Reading a CSV File with an OleDbDataAdapter and Jet

Reading a comma delimited is quite simple with an OleDbDataAdapter and Jet 4.0:

 
private static DataTable ReadCsvFileViaJetOleDb(string file)
{
string connection =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"" +
Path.GetDirectoryName(file) +
"\\\";Extended Properties=\"text;HDR=Yes;FMT=Delimited\"";
 
string query = "SELECT * FROM " + file;
 
DataTable dt = new DataTable();
 
using (OleDbDataAdapter da = new OleDbDataAdapter(query, connection))
da.Fill(dt);
 
return dt;
}

However, you have to remember a few caveats:

  1. Commas may not be your default delimiter for your UI culture
  2. Only txt, csv, asc, and tab are acceptable file extensions
  3. You can’t have more than one period in a file name, e.g. myfile.test.csv won’t work

There are a few options you can tweak via the registry here:

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text
This entry was posted in C#. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *