Pablo Reyes Pablo Reyes - 1 year ago 78
C# Question

Creating a DataTable from CSV File

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\"");
string strQuery = "SELECT * FROM [" + Path.GetFileName(filePath) + "]";
OleDbDataAdapter adapter = new OleDbDataAdapter(strQuery, conn);
DataSet ds = new System.Data.DataSet("CSV File");
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.

Answer Source

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

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]));