Reading CSV files with OleDbCommand / OleDbDataAdapter

Question

I cannot figure out why, but when I use an OleDbDataAdapter or OleDbCommand to read a CSV file, in both cases the resulting data is structured well (it recognizes the columns from the file header), but the row data is all empty strings.

I've done CSV processing before many times with success, so the only difference I found with this file is that all the fields in the file are surrounded by quotes.
I've tried using a schema.ini file, but to no avail. This feels like an issue with the Microsoft Jet 4.0 component, but I cannot figure out what the problem is.

Here is an excerpt from the file:

"UNIQUEID","OWNERID","PHONE1","PHONE2","EMERGENCYCONTACT","ADDRESS1","ADDRESS2","ADDRESS3","ADDRESSCITY","ADDRESSSTATE","ADDRESSZIP","UNIONCODE","CUSTOM1","CUSTOM2","CUSTOM3","CUSTOM4","CUSTOM5","CUSTOM6"
"5","33","1235551212","          ","","1914 SANDFLAT ROAD","","","THOMASVILLE","AL","367849215","","contract","7.75","1","N","","A"
"6","34","1235551212","          ","","1407 OLD HWY. 5 SOUTH","","","THOMASVILLE","AL","36784","","contract","7.75","1","N","","B"
"7","35","1235551212","          ","","P.O. BOX 204","","","THOMASVILLE","AL","36784","","substitute","7.75","0","Y","","M"
"8","36","1235551212","          ","","383 UNCLE BEN RD","","","THOMASVILLE","AL","36784","","substitute","0.00","0","","",""

Here is my current code:

OleDbConnection conn = new OleDbConnection(
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\payroll;" + 
    "Extended Properties=\"text;HDR=Yes;FMT=CSVDelimited\"");
OleDbDataAdapter adapter = new OleDbDataAdapter("select * from file.txt", conn);
DataTable dt = new DataTable();
adapter.Fill(dt);

Here are the contents of the schema.ini file. I couldn't read the file without it:

[file.txt]
Format=CSVDelimited
ColNameHeader=True
Col1="UNIQUEID" Text
Col2="OWNERID" Text
Col3="PHONE1" Text
Col4="PHONE2" Text
Col5="EMERGENCYCONTACT" Text
Col6="ADDRESS1" Text
Col7="ADDRESS2" Text
Col8="ADDRESS3" Text
Col9="ADDRESSCITY" Text
Col10="ADDRESSSTATE" Text
Col11="ADDRESSZIP" Text
Col12="UNIONCODE" Text
Col13="CUSTOM1" Text
Col14="CUSTOM2" Text
Col15="CUSTOM3" Text
Col16="CUSTOM4" Text
Col17="CUSTOM5" Text
Col18="CUSTOM6" Text
CharacterSet=ANSI

Solution 1

I found out what the issue was. The CharacterSet in the schema.ini file was set to ANSI. The file encoding is Unicode. Setting the CharacterSet to Unicode resolved the issue.

Solution 2

What is another brilliant alternative is to use the FileHelpers library @ www.filehelpers.com

Its much more abstract and flexible than a jet engine, allowing you to do things like on the importing of the file to build a collection directly of your type on the fly with customizations.

i.e

FileHelperEngine engine = new FileHelperEngine(); List customers = engine.ReadFile("File.csv");

This is just the start, and then you can get to much more cleaner by using linq operators on your collection.

Sk8tz