使用 OleDbCommand / OleDbDataAdapter 读取 CSV 文件

发布于 2024-10-01 14:29:29 字数 1877 浏览 0 评论 0原文

我不明白为什么，但是当我使用 OleDbDataAdapter 或 OleDbCommand 读取 CSV 文件时，在这两种情况下，生成的数据结构良好（它识别文件头中的列），但行数据都是空字符串。

我之前已经成功进行过多次 CSV 处理，因此我发现此文件的唯一区别是文件中的所有字段都用引号引起来。
我尝试过使用 schema.ini 文件，但无济于事。这感觉像是 Microsoft Jet 4.0 组件的问题，但我无法弄清楚问题是什么。

以下是该文件的摘录：

"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","","",""

这是我当前的代码：

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);

以下是 schema.ini 文件的内容。没有它我无法读取该文件：

[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

原文

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

分享到QQ

分享到微博