读取CSV文件的OleDbCommand / OleDbDataAdapter的 [英] Reading CSV files with OleDbCommand / OleDbDataAdapter

问题描述

我想不通为什么，但是当我使用的OleDbDataAdapter的或OleDbCommand的读​​取一个CSV文件，在这两种情况下，生成数据的结构以及（它识别从文件标题中的列），但该行的数据全部清空字符串。

我已经做了CSV处理很多次才成功，所以我发现这个文件唯一不同的是，所有的文件中的字段用引号括起来。
我已经使用schema.ini文件试过，但无济于事。这感觉就像一个问题，在Microsoft Jet 4.0组件，但我想不出是什么问题。

下面是该文件的节选：

<$c$c>"UNIQUEID","OWNERID","PHONE1","PHONE2","EMERGENCYCONTACT","ADDRESS1","ADDRESS2","ADDRESS3","ADDRESSCITY","ADDRESSSTATE","ADDRESSZIP","UNION$c$c","CUSTOM1","CUSTOM2","CUSTOM3","CUSTOM4","CUSTOM5","CUSTOM6"
5，33，1235551212，，，1914 SANDFLAT道，，，THOMASVILLE，AL，367849215，，合同， 7.75，1，N，，A的
6，34，1235551212，，，1407 OLD HWY 5南，，，THOMASVILLE，AL，36784，，合同，7.75，1，N，，B的
7，35，1235551212，，，邮政信箱204，，，THOMASVILLE，AL，36784，，替代， 7.75，0，Y，，M
8，36，1235551212，，，383本叔叔RD，，，THOMASVILLE，AL，36784，，替代， 0.00，0，，， 

的

下面是我目前的code：

  OleDbConnection的康恩=新的OleDbConnection（
    供应商= Microsoft.Jet.OLEDB.4.0;数据源= C：\\工资; +
    扩展属性= \文本; HDR =是; FMT = CSVDelimited \）;
OleDbDataAdapter的适配器=新OleDbDataAdapter的（选择file.txt的*，康涅狄格州）;
数据表DT =新的DataTable（）;
adapter.Fill（DT）;  

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

  [file.txt的]
格式= CSVDelimited
ColNameHeader = TRUE
COL1 =UNIQUEID文本
COL2 =OWNERID文本
COL3 =PHONE1文本
COL4 =PHONE2文本
COL5 =EMERGENCYCONTACT文本
COL6 =ADDRESS1文本
COL7 =地址2的文本
Col8 =地址3的文本
Col9 =ADDRESSCITY文本
Col10 =ADDRESSSTATE文本
Col11 =ADDRESSZIP文本
COL12 =UNION code的文本
Col13 =CUSTOM1文本
Col14 =CUSTOM2文本
Col15 =特制3文本
Col16 =CUSTOM4文本
Col17 =CUSTOM5文本
Col18 =CUSTOM6文本
字符集= ANSI  

解决方案

我发现了什么问题了。在schema.ini文件的字符集设置为ANSI。该文件的编码是单向code。该字符集设置为统一code解决了问题。

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

解决方案

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.

这篇关于读取CSV文件的OleDbCommand / OleDbDataAdapter的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！