Creating a DataTable from CSV File

Question

I'm working on a project and I need to read a CSV file and then fill a DataSet with its data. I've been searching and I have found some interesting things in OleDB.

I have a class CSVReader:

class CSVReader
{
    public DataTable GetDataTable(string filePath)
    {
        OleDbConnection conn = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + Path.GetDirectoryName(filePath) + "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");
        conn.Open();
        string strQuery = "SELECT * FROM [" + Path.GetFileName(filePath) + "]";
        OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
        DataSet ds = new System.Data.DataSet("CSV File");
        adapter.Fill(ds);
        return ds.Tables[0];
    }
}

And I call it from here:

CSVReader datareader = new CSVReader();
DataTable dt = datareader.GetDataTable(filepath);

The problem is that it parse the first line (the header line) like JUST ONE identifier for the column, I mean: This is the header of the CSV file:

Name, Product Name, Server, Vendor, Start Time, End Time, Host Name, User Name, Project Name, Usage time (hours)

And after it, there is all the data separated by commas.

When I read the file, fill the dataset and print dt.Columns.Count it shows that it only have 1 column.

Any help?

Thanks in advance.

possible duplicate of [How to read a csv file into a .net datatable](http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable) — Mikael Svenson, Jul 22 '10 at 06:37
My solution is the same as Jim Scott (http://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable/1050278#1050278), but it doesn't work :S — Pablo Reyes, Jul 22 '10 at 06:43

score 7 · Answer 1 · answered Feb 12 '13 at 18:18

The best option I have found, and it resolves issues where you may have different versions of Office installed, and also 32/64-bit issues, is FileHelpers.

It can be added to your project references using NuGet and it provides a one-liner solution:

CommonEngine.CsvToDataTable(path, "ImportRecord", ',', true);

score 7 · Accepted Answer · answered Jul 22 '10 at 06:49

I always use this CSV library for reading CSV files in through C# its always worked good for me.

http://www.codeproject.com/KB/database/CsvReader.aspx

Heres an example of reading a CSF file using the library

using System.IO;
using LumenWorks.Framework.IO.Csv;

void ReadCsv()
{
    // open the file "data.csv" which is a CSV file with headers
    using (CsvReader csv =
           new CsvReader(new StreamReader("data.csv"), true))
    {
        int fieldCount = csv.FieldCount;
        string[] headers = csv.GetFieldHeaders();

        while (csv.ReadNextRecord())
        {
            for (int i = 0; i < fieldCount; i++)
                Console.Write(string.Format("{0} = {1};",
                              headers[i], csv[i]));

            Console.WriteLine();
        }
    }
}

score 3 · Answer 3 · edited Sep 28 '11 at 00:51

3

KBCsv has built-in support for reading into a DataSet:

using (var reader = new CsvReader(@"C:\data.csv")) {
    reader.ReadHeaderRecord();
    var dataSet = new DataSet();
    reader.Fill(dataSet, "csv-data");
}

edited Sep 28 '11 at 00:51

answered Jul 22 '10 at 07:02

Kent Boogaart

175,602
35
392
393

score 1 · Answer 4 · answered Jul 22 '10 at 06:47

1

if nothing special i use this kind of code

TextReader tr1 = new StreamReader(@"c:\pathtofile\filename",true);

var Data = tr1.ReadToEnd().Split('\n')
.Where(l=>l.Length>0)  //nonempty strings
.Skip(1)               // skip header 
.Select(s=>s.Trim())   // delete whitespace
.Select(l=>l.Split(',')) // get arrays of values
.Select(l=>new {Field1=l[0],Field2=l[1],Field3=l[2]});

answered Jul 22 '10 at 06:47

Alexander Taran

6,655
2
39
60

1

-1. Splitting by newline is not how you should approach CSV parsing. If it was that simple, there won't be any libraries out there. – Victor Zakharov Jan 16 '14 at 21:29

score 0 · Answer 5 · answered Jul 22 '10 at 06:50

0

Try including IMEX in the extended properties, which will tell the driver that you have mixed mode data

Text;HDR=YES;FMT=Delimited;IMEX=1

answered Jul 22 '10 at 06:50

Fadrian Sudaman

6,405
21
29

Creating a DataTable from CSV File

5 Answers5

Linked