c# - 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.

Answer 1

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