It doesn’t seem to be well known that you can easily access data in CSV files (Comma-Separated Values) using ADO.NET components. For example, you can use the OleDbConnection, OleDbCommand, and OleDbDataAdapter objects to fill a DataSet from a CSV file. You can also update the data. Also notice that it is possible to use Visual Studio’s Server Explorer to visualize or edit CSV files.

All you need is to use the Jet engine thanks to a connection string like the following one:

“Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TxtFilesFolder\;Extended Properties=’text;HDR=Yes;FMT=Delimited'”

Note: “HDR=Yes” indicates that the first row contains columnnames, not data (source: connectionstrings.com).

Once you have a connection to a directory, you can refer to files as tables as follows:

“SELECT * FROM data.txt”

Hint: Think about JOINs if you need them…

So, why not use ADO.NET’s power to access CSV files instead of coding this all by yourself?

Update: this post seems very popular, so I guess it’s useful that I add two links about the Schema.ini file for more information:

Update: interesting comment by David:

Recap: Reading columns with mixed data types (that is, with data that could appear to a stupid OleDB driver to be of two types), you must use registry modifications AND extended properties on the connection string.

For Excel, the connection string is:

string.Format(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};” + @” Extended Properties=””{1}”””, pathName, “Excel 8.0;HDR=YES;IMEX=1″);

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\ImportMixedTypes = Text

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Excel\TypeGuessRows = 0

For CSV, the connection string is:

string.Format(@”Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};” + @” Extended Properties=””{1}”””, path, “Text;HDR=YES;FMT=Delimited;IMEX=1”);

In the registry, set:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\ImportMixedTypes = “Text”

and

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows = “0”

ที่มา: http://weblogs.asp.net/fmarguerie/archive/2003/10/01/29964.aspx