How to read a CSV file into a .NET Datatable

How can I load a CSV file into a `System.Data.DataTable`, creating the datatable based on the CSV file?

Does the regular ADO.net functionality allow this?

Here's a solution that uses ADO.Net's ODBC text driver:

Dim csvFileFolder As String = "C:\YourFileFolder"
Dim csvFileName As String = "YourFile.csv"

'Note that the folder is specified in the connection string,
'not the file. That's specified in the SELECT query, later.
Dim connString As String = "Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" _
& csvFileFolder & ";Extended Properties=""Text;HDR=No;FMT=Delimited"""
Dim conn As New Odbc.OdbcConnection(connString)

'Open a data adapter, specifying the file name to load
Dim da As New Odbc.OdbcDataAdapter("SELECT * FROM [" & csvFileName & "]", conn)
'Then fill a data table, which can be bound to a grid
Dim dt As New DataTableda.Fill(dt)

grdCSVData.DataSource = dt

Once filled, you can value properties of the datatable, like ColumnName, to make utilize all the powers of the ADO.Net data objects.

In VS2008 you can use Linq to achieve the same effect.

NOTE: This may be a duplicate of [this][1] SO question.



[1]:

[To see links please register here]

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

public class Csv
{
public static DataTable DataSetGet(string filename, string separatorChar, out List<string> errors)
{
errors = new List<string>();
var table = new DataTable("StringLocalization");
using (var sr = new StreamReader(filename, Encoding.Default))
{
string line;
var i = 0;
while (sr.Peek() >= 0)
{
try
{
line = sr.ReadLine();
if (string.IsNullOrEmpty(line)) continue;
var values = line.Split(new[] {separatorChar}, StringSplitOptions.None);
var row = table.NewRow();
for (var colNum = 0; colNum < values.Length; colNum++)
{
var value = values[colNum];
if (i == 0)
{
table.Columns.Add(value, typeof (String));
}
else
{
row[table.Columns[colNum]] = value;
}
}
if (i != 0) table.Rows.Add(row);
}
catch(Exception ex)
{
errors.Add(ex.Message);
}
i++;
}
}
return table;
}
}

I came across this piece of code that uses Linq and regex to parse a CSV file. The refering article is now over a year and a half old, but have not come across a neater way to parse a CSV using Linq (and regex) than this. The caveat is the regex applied here is for comma delimited files (will detect commas inside quotes!) and that it may not take well to headers, but there is a way to overcome these). Take a peak:

Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"
Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)
Dim custs = From line In lines _
Let data = r.Split(line) _
Select New With {.custnmbr = data(0), _
.custname = data(1)}
For Each cust In custs
strCUSTNMBR = Replace(cust.custnmbr, Chr(34), "")
strCUSTNAME = Replace(cust.custname, Chr(34), "")
Next

this is the code i use it but your apps must run with net version 3.5

private void txtRead_Click(object sender, EventArgs e)
{
// var filename = @"d:\shiptest.txt";

openFileDialog1.InitialDirectory = "d:\\";
openFileDialog1.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*";
DialogResult result = openFileDialog1.ShowDialog();
if (result == DialogResult.OK)
{
if (openFileDialog1.FileName != "")
{
var reader = ReadAsLines(openFileDialog1.FileName);

var data = new DataTable();

//this assume the first record is filled with the column names
var headers = reader.First().Split(',');
foreach (var header in headers)
{
data.Columns.Add(header);
}

var records = reader.Skip(1);
foreach (var record in records)
{
data.Rows.Add(record.Split(','));
}

dgList.DataSource = data;
}
}
}

static IEnumerable<string> ReadAsLines(string filename)
{
using (StreamReader reader = new StreamReader(filename))
while (!reader.EndOfStream)
yield return reader.ReadLine();
}