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;
}
{
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:
- Commas may not be your default delimiter for your UI culture
- Only txt, csv, asc, and tab are acceptable file extensions
- 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