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.
