c# - How to read a CSV file into a .NET Datatable

ID : 20443

viewed : 8

Tags : c#.netcsvdatatablec#

Top 5 Answer for c# - How to read a CSV file into a .NET Datatable

vote vote

92

I have been using OleDb provider. However, it has problems if you are reading in rows that have numeric values but you want them treated as text. However, you can get around that issue by creating a schema.ini file. Here is my method I used:

// 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;     } } 
vote vote

80

Here's an excellent class that will copy CSV data into a datatable using the structure of the data to create the DataTable:

A portable and efficient generic parser for flat files

It's easy to configure and easy to use. I urge you to take a look.

vote vote

75

Hey its working 100%

  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 enter image description here

Data table Imported enter image description here

vote vote

66

I have decided to use Sebastien Lorion's Csv Reader.

Jay Riggs suggestion is a great solution also, but I just didn't need all of the features that that Andrew Rissing's Generic Parser provides.

UPDATE 10/25/2010

After using Sebastien Lorion's Csv Reader in my project for nearly a year and a half, I have found that it throws exceptions when parsing some csv files that I believe to be well formed.

So, I did switch to Andrew Rissing's Generic Parser and it seems to be doing much better.

UPDATE 9/22/2014

These days, I mostly use this extension method to read delimited text:

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

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

UPDATE 2/20/2015

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()); } 
vote vote

51

We always used to use the Jet.OLEDB driver, until we started going to 64 bit applications. Microsoft has not and will not release a 64 bit Jet driver. Here's a simple solution we came up with that uses File.ReadAllLines and String.Split to read and parse the CSV file and manually load a DataTable. As noted above, it DOES NOT handle the situation where one of the column values contains a comma. We use this mostly for reading custom configuration files - the nice part about using CSV files is that we can edit them 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); } 

Top 3 video Explaining c# - How to read a CSV file into a .NET Datatable

Related QUESTION?