How to load a CSV file into a Datatable in.NET

Problem

What is the best way to import a CSV file into a system? Is it possible to use Data.DataTable to create a datatable from a CSV file?

Is this possible with standard ADO.net functionality?

Asked by Ronnie Overby

Solution #1

I’ve been utilising the OleDb provider. However, if you’re reading in rows with numeric values but want them to be handled as text, that can cause issues. You can work around this problem by generating a schema.ini file. Here’s how I went about it:

// using System.Data;
// using System.Data.OleDb;
// using System.Globalization;
// using System.IO;

static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader)
{
    string header = isFirstRowHeader ? "Yes" : "No";

    string pathOnly = Path.GetDirectoryName(path);
    string fileName = Path.GetFileName(path);

    string sql = @"SELECT * FROM [" + fileName + "]";

    using(OleDbConnection connection = new OleDbConnection(
              @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + 
              ";Extended Properties=\"Text;HDR=" + header + "\""))
    using(OleDbCommand command = new OleDbCommand(sql, connection))
    using(OleDbDataAdapter adapter = new OleDbDataAdapter(command))
    {
        DataTable dataTable = new DataTable();
        dataTable.Locale = CultureInfo.CurrentCulture;
        adapter.Fill(dataTable);
        return dataTable;
    }
}

Answered by Jim Scott

Solution #2

Here’s a great class that will replicate CSV data into a datatable and generate the DataTable based on the data structure:

A generic parser for flat files that is both portable and efficient.

It’s straightforward to set up and operate. Please take a look at it.

Answered by Jay Riggs

Solution #3

Hey, it’s fully functional.

  public static DataTable ConvertCSVtoDataTable(string strFilePath)
  {
    DataTable dt = new DataTable();
    using (StreamReader sr = new StreamReader(strFilePath))
    {
        string[] headers = sr.ReadLine().Split(',');
        foreach (string header in headers)
        {
            dt.Columns.Add(header);
        }
        while (!sr.EndOfStream)
        {
            string[] rows = sr.ReadLine().Split(',');
            DataRow dr = dt.NewRow();
            for (int i = 0; i < headers.Length; i++)
            {
                dr[i] = rows[i];
            }
            dt.Rows.Add(dr);
        }

    }


    return dt;
   }

CSV Image

Imported Data Table

Answered by Shivam Srivastava

Solution #4

I’ve decided to utilize Csv Reader by Sebastien Lorion.

Jay Riggs’ proposal is also a good one, but I didn’t require all of the features provided by Andrew Rissing’s Generic Parser.

After about a year and a half of using Sebastien Lorion’s Csv Reader in my project, I’ve discovered that it throws exceptions when processing some well-formed csv files.

As a result, I switched to Andrew Rissing’s Generic Parser, which appears to be doing considerably better.

I generally use this extension approach to read delimited text these days:

https://github.com/Core-Techs/Common/blob/master/CoreTechs.Common/Text/DelimitedTextExtensions.cs#L22

https://www.nuget.org/packages/CoreTechs.Common/

Example:

var csv = @"Name, Age
Ronnie, 30
Mark, 40
Ace, 50";

TextReader reader = new StringReader(csv);
var table = new DataTable();
using(var it = reader.ReadCsvWithHeader().GetEnumerator())
{

    if (!it.MoveNext()) return;

    foreach (var k in it.Current.Keys)
        table.Columns.Add(k);

    do
    {
        var row = table.NewRow();
        foreach (var k in it.Current.Keys)
            row[k] = it.Current[k];

        table.Rows.Add(row);

    } while (it.MoveNext());
}

Answered by Ronnie Overby

Solution #5

Until we switched to 64-bit apps, we always used the Jet.OLEDB driver. A 64-bit Jet driver has not been released and will not be released by Microsoft. Here’s a quick fix we came up with that makes use of File. ReadAllLines and String are two functions that can be used together. Split the CSV file to read and parse it, then manually load a DataTable. It does not handle the circumstance where one of the column values has a comma, as previously stated. We mostly use this to read custom configuration files; the benefit of utilizing CSV files is that they can be edited in Excel.

string CSVFilePathName = @"C:\test.csv";
string[] Lines = File.ReadAllLines(CSVFilePathName);
string[] Fields;
Fields = Lines[0].Split(new char[] { ',' });
int Cols = Fields.GetLength(0);
DataTable dt = new DataTable();
//1st row must be column names; force lower case to ensure matching later on.
for (int i = 0; i < Cols; i++)
    dt.Columns.Add(Fields[i].ToLower(), typeof(string));
DataRow Row;
for (int i = 1; i < Lines.GetLength(0); i++)
{
    Fields = Lines[i].Split(new char[] { ',' });
    Row = dt.NewRow();
    for (int f = 0; f < Cols; f++)
        Row[f] = Fields[f];
    dt.Rows.Add(Row);
}

Answered by Chuck Bevitt

Post is based on https://stackoverflow.com/questions/1050112/how-to-read-a-csv-file-into-a-net-datatable