27 Apr 2009

Parse CSV files with the MS ODBC Driver

Easy way to read CSV files in .NET just like connecting to an SQL table - just use the standard Microsoft Text Driver.

public DataTable GetData(string FullPath)
{
string strPathWithoutFileName = Path.GetDirectoryName(FullPath);
string Name = Path.GetFileName(FullPath);

DataSet dsData = new DataSet();
System.Data.Odbc.OdbcConnection conCsv = new System.Data.Odbc.OdbcConnection();
conCsv.ConnectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=" + strPathWithoutFileName + @";Extensions=csv;";
conCsv.Open();

System.Data.Odbc.OdbcDataAdapter oda = new System.Data.Odbc.OdbcDataAdapter();
oda.SelectCommand = new System.Data.Odbc.OdbcCommand("SELECT * FROM [" + Name + "]", conCsv);

try
{
oda.Fill(dsData);
}
catch (Exception ex)
{
throw new InvalidOperationException("Csv File Read Error: " + FullPath, ex);
}
finally
{
oda.Dispose();
conCsv.Close();
conCsv.Dispose();
}

dsData.Tables[0].TableName = Name;

return dsData.Tables[0];
}


A note on configuring the Microsoft Text Driver for your CSV file:

If you place a Schema.ini file in the same location as the CSV file, you can declaratively specify the expected columns and datatypes. See this article for info: http://msdn.microsoft.com/en-us/library/ms709353.aspx

A note about System Regional Settings vs Application Culture when using the Microsoft Text Driver:

I noticed something weird when I specified a column as DateTime in my Schema.ini file. The Text Driver was reading the dates in US format (mm/dd/yy). My testing indicates that even though my ASP.NET web app was explicitly set for British culture ("en-GB" in the web.config's globalization section), the Text Driver uses the Windows Regional And Language Options setting. Annoying eh!

Note also that if the Text Driver cannot resolve a DateTime value according the Regional Settings format, it doesn't throw an exception. It just treats the value as Null. Yuck.

No comments:

Post a Comment

Comments are very welcome unless you're a spammer, in which case you should probably kill yourself.

If I helped you out today, you can buy me a beer below. Cheers!