Parsing CSV file with type

Question

I am trying to parse a CSV and construct a DataTable out of it. Now the tricky part is i would like to assign data types before constructing the data table. For eg consider the following CSV file

Name,Age,Salary
A,30,1000
B,35,1500
C,40,2000

I would like to have Name stored as string, Age as Int and Salary as decimal in the data table I am constructing. Any suggestions on the best way to do this?

How could the program distinguish between `30` and `1000` and consider the former as `int` and the latter as `decimal`? This cannot be done until some other information is provided. e.g., the mapping between column names and data types. — Mohammad Dehghan, Feb 03 '12 at 05:41
if you can use oledb. you could use schema.ini to defined column types. check this http://stackoverflow.com/questions/8683180/data-error-when-reading-csv-file-in-c-sharp-winforms/8683365#8683365 — Shoaib Shaikh, Feb 03 '12 at 05:44
Thanks Shoaib. It works the way I wanted it to. Schema.ini was the answer. — Nishant, Feb 03 '12 at 08:30

Gustavo Mori · Answer 1 · 2012-02-03T06:38:22.657

Here's a naive implementation that ignores most error checking, and some good coding practices:

namespace StackOverflowConsole
{
    using System;
    using System.IO;
    using System.Data;

    class Program
    {
        static void Main(string[] args)
        {
            var path = @"C:\temp\test.csv";

            CreateTestFile(path);

            var dataTable = new DataTable();
            dataTable.Columns.Add("Name", typeof(string));
            dataTable.Columns.Add("Age", typeof(int));
            dataTable.Columns.Add("Salary", typeof(decimal));

            // TODO: add checks, exception handling
            using (var reader = new StreamReader(path))
            {
                // reads all lines into a single string
                var lines = reader.ReadToEnd().Split(new char[] { '\n' });

                if (lines.Length > 0)
                {
                    // you may wanna skip the first line, if you're using a file header
                    foreach (string line in lines)
                    {
                        if (string.IsNullOrWhiteSpace(line))
                        {
                            continue;
                        }

                        // split the current line using the separator
                        var tokens = line.Trim().Split(new char[] { ',' });

                        // check your assumptions on the CSV contents
                        // ex: only process lines with the correct number of fields
                        if (tokens.Length == 3)
                        {
                            var person = new Person();

                            person.Name = tokens[0];
                            // a better implementation would use TryParse()
                            person.Age = Int32.Parse(tokens[1]);
                            person.Salary = Decimal.Parse(tokens[2]);

                            dataTable.Rows.Add(person.Name, person.Age, person.Salary);
                        }
                    }
                }
            }
        }

        private static void CreateTestFile(string path)
        {
            if (File.Exists(path))
            {
                File.Delete(path);
            }

            using (var writer = new StreamWriter(path))
            {
                writer.WriteLine("A,30,1000");
                writer.WriteLine("B,35,1500");
                writer.WriteLine("C,40,2000");
            }
        }
    }

    public class Person
    {
        public string Name;
        public int Age;
        public decimal Salary;
    }
}

Vinod · Answer 2 · 2012-02-03T06:36:24.827

Try this:

Keep CSV file in code directory

string path = Server.MapPath("emp.csv");
            string header = "Yes";
            string sql = string.Empty;
            DataTable dt = null;
            string fullpath = Path.GetDirectoryName(path);
            string fileName = Path.GetFileName(path);
            OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fullpath + ";Extended Properties=\"Text;HDR=" + header + "\"");
            OleDbDataAdapter da = new OleDbDataAdapter("select * from [" + fileName + "]", connection);
            dt = new DataTable();
            dt.Columns.Add("Name", typeof(string));
            dt.Columns.Add("Age", typeof(int));
            dt.Columns.Add("Salary", typeof(decimal));
            da.Fill(dt);
            GridView1.DataSource = dt;
            GridView1.DataBind();

Parsing CSV file with type

2 Answers2