Reading CSV files in .NET

I have typically used a “StreamReader” to read through text files when I had to parse out comma separated variables. This normally meant that I had to do a lot of the lifting of reading values, parsing them and handling exceptions. And most often the case was that I was reading the data into a DataSet which could then be easily displayed in a UI

Today I was shown a much easier way to do the above using the Excel engine by Joe Harker. It uses an ODBC driver to read a CSV file just like you would read in data from any database. The trick is to use the correct connection string. Here is a sample connection string:
”Provider=\"Microsoft.Jet.OLEDB.4.0\";Data Source=\"{0}\";Extended Properties=\"Excel 8.0; HDR=Yes; IMEX=0\"";

[Important Update: 02-12-2010]

There is a limitation with the JET engine – it can be used to read in only 65,535 rows of data and 255 columns of data. (not sure if this limit is different on 64 bit machines).

For more information:

Text File Driver Programming Considerations

Using OleDb to Import Text Files

Customizing how data is read using a schema.ini file

OLE DB Programmers Guide – Jet Provider

OLE DB Tutorial – C#

JET SQL