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.