无法使用 OLEDB 从 CSV 中获取所有列的值 [英] Unable get value of all columns from CSV using OLEDB

问题描述

我正在使用 OLEDB 将 CSV 文件解析为 DataTable.它工作正常，但当 CSV 中的某些值包含双引号 (") 时会产生问题.OLEDB 跳过该行中下一个剩余列的值.

I am using OLEDB to parse CSV file into DataTable. It is working fine but it creates an issue when some value in CSV contains double quote("). OLEDB skips the value of the of the next remaining columns in that row.

例如，我在 CSV 文件中有以下值.这里第二行 Col2 的值包含双引号 (").

For example, I have following values in CSV file. Here value of Col2 in second row contain the double quote(").

当我将 CSV 解析为 DataTable 时，DataTable 包含以下值.这里col3和col4的值在第二行空白处.

When I parse the CSV to DataTable then DataTable contains the following values. Here the value of col3 and col4 in blank in the second row.

我正在使用以下连接字符串

I am using following connection string

"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='text;HDR=Yes;IMEX=1;ColNameHeader=True;CharacterSet=65001;FMT=Delimited(,)'"

查询是

"select * from [" + fileName + "]"

这是完整的代码

string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='text;HDR=Yes;IMEX=1;ColNameHeader=True;CharacterSet=65001;FMT=Delimited(,)'";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
    conn.Open();
    table = new DataTable();
    var dataAdapter = new OleDbDataAdapter("select * from [" + fileName + "]", conn);
    dataAdapter.Fill(table);
}

如何忽略值中的双引号(")?

How can I ignore double quote(") from value?

注意:我是从第三方下载的文件，直接使用这个CSV进行解析.

Note: I am downloading the file from the third party directly use that this CSV for parsing.

推荐答案

您将需要创建 schema.ini 文件并将其放置在与您从中提取数据的文件相同的目录中.在其中，您必须将 TextDelimiter 和 Format 提供给 csv 文件.将 TextDelimiter 设置为 none

You going to need to create schema.ini file and place it in the same directory as the files you're pulling the data from. In it you will have to give TextDelimiter and Format to the csv file. Set the TextDelimiter to none

[YOURCSVFILENAME.csv]
ColNameHeader=True
Format=CSVDelimited
TextDelimiter=none

希望这会有所帮助.

更新:动态创建 schema.ini 文件...

UPDATE: Creating schema.ini file dynamically...

  string csvFilePath = /* CSV file directory */
  string csvFileName = /* CSV file name */
  using (FileStream sr = new FileStream(csvFilePath + "\schema.ini", 
      FileMode.Create, FileAccess.Write)) 
  { 
      using (StreamWriter writer=new StreamWriter(sr)) 
      { 
          writer.WriteLine("[" + csvFileName + "]"); 
          writer.WriteLine("ColNameHeader=True"); 
          writer.WriteLine("Format=CSVDelimited"); 
          writer.WriteLine("TextDelimiter=none"); 
          writer.Close(); 
          writer.Dispose(); 
      } 
  } 

这篇关于无法使用 OLEDB 从 CSV 中获取所有列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！