OLEDB将CSV导入到VB.NET数据类读取“ - ”为0 [英] OLEDB Import of CSV to VB.NET datatable reading '-' as 0

问题描述

问候有用的令人愉快的人，

我有一个问题，读取CSV文件和转换为VB.Net的数据表。
如果CSV文件包含一个充满 - 的列，则在导入到数据表时，它们将显示为0，整个列将以数字格式进行格式化。

我写的代码是：

 公共函数LoadCsvFile（filePath As String）As DataTable 
 Try 
 
 DTReadCSV =新数据表
 
 Dim连接As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection（Provider = Microsoft.ACE.OLEDB.12.0 ; Data Source =& Microsoft.VisualBasic.Left（filePath，InStrRev（filePath，\））&; Extended Properties =text; HDR = Yes; FMT = Delimited）
 
 Dim adapter As New OleDb.OleDbDataAdapter（SELECT * FROM [+ Microsoft.VisualBasic.Mid（filePath，InStrRev（filePath，\）+ 1）+]，connection）
'Dim table As New DataTable（）
 adapter.Fill（DTReadCSV）
 
'现在在数据表中很好的
 IntAmountRows = DTReadCSV.Rows.Count 
 IntAmountColumns = DTReadCSV.Columns.Count 
 
 
'System.Diagnostics.Debug.Print（DTReadCSV.Rows.Item（1）（1）.ToString）
返回DTReadCSV 
 
退出函数
 Catch ex As Exception 
 MsgBox（ex.ToString（））
 
 MsgBox（Err.Number& & Chr（13）& Err.Description）
结束尝试
结束函数
  

除了通过将 - 作为空格修改CSV文件外，如何解决这个问题，这在目前看来是导入这些CSV文件的唯一长时间的方式。

非常感谢

解决方案

使用Schema.INI可以向OleDB描述CSV看起来像 / em>，包括结果列名称和数据类型。例如，根据此数据：

国家，首都城市，人口，假 $ b法国，巴黎，2.25，-----

加拿大，多伦多，2.5，-----

意大利，罗马，2.8，-----

创建架构。 ini文件在同一个文件夹;它可以有多个部分在该文件夹中定义各种CSV。如果在与CSV 相同的文件夹中有一个Schema.INI，并且它有一个CSV文件的条目，OleDB将自动使用它（没有什么特别的做）。

上述数据的Schema.INI条目可能如下所示：

[Capitals.CSV]

ColNameHeader = True

CharacterSet = 1252

Format = CSVDelimited

TextDelimiter =

Col1 =Country文字宽度254

Col2 =Capital City文字宽度254

Col3 =人口单一

Col4 =Fake文字宽度254

当读取该文件时，OleDb将使用这些定义，导致输出中Fake列的一串短划线：

使用Schema.INI的一个额外的好处（许多）是你可以命名那里的列，而不是使用或别名F1，F2，F3等。输出的SQL只是SELECT * FROM Capitals.CSV

提示（对于其他人来说）：要指定UTF8作为字符集，

有关详细信息，请参阅

- Schema.ini文本文件驱动程序

- 代码页标识符

Greetings helpful delightful people,

I have a problem with reading CSV files and converting to datatables in VB.Net. If the CSV file contains a column full of '-' then on import into the datatable they appear as '0' and the entire column is formatted in a numeric format.

The code I have written is:

Public Function LoadCsvFile(filePath As String) As DataTable
    Try

        DTReadCSV = New DataTable

        Dim connection As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Microsoft.VisualBasic.Left(filePath, InStrRev(filePath, "\")) & ";Extended Properties=""text;HDR=Yes;FMT=Delimited""")

        Dim adapter As New OleDb.OleDbDataAdapter("SELECT * FROM [" + Microsoft.VisualBasic.Mid(filePath, InStrRev(filePath, "\") + 1) + "]", connection)
        'Dim table As New DataTable()
        adapter.Fill(DTReadCSV)

        'now thats its nicely in a datatable 
        IntAmountRows = DTReadCSV.Rows.Count
        IntAmountColumns = DTReadCSV.Columns.Count


        'System.Diagnostics.Debug.Print(DTReadCSV.Rows.Item(1)(1).ToString)
        Return DTReadCSV

        Exit Function
    Catch ex As Exception
        MsgBox(ex.ToString())

        MsgBox(Err.Number & " " & Chr(13) & Err.Description)
    End Try
End Function

Please can someone smarter figure out how to combat this issue besides modifying the CSV file by taking out the '-' as blanks, which at the moment seems the only long winded way of importing these CSV files.

Many thanks

解决方案

Using a Schema.INI you can describe to OleDB what the CSV looks like in detail including resulting column names and data types. For instance, given this data:

"Country","Capital City","Population", "Fake"
"France","Paris","2.25","-----"
"Canada","Toronto","2.5","-----"
"Italy","Rome","2.8","-----"

Create a Schema.ini file in the same folder; it can have multiple sections to define various CSVs in that folder. If there is a Schema.INI in the same folder as the CSV and it has an entry for your CSV, OleDB will automatically use it (nothing special to do).

The Schema.INI entry for the above data may look like this:

[Capitals.CSV]
ColNameHeader=True
CharacterSet=1252
Format=CSVDelimited
TextDelimiter="
Col1="Country" Text Width 254
Col2="Capital City" Text Width 254
Col3="Population" Single
Col4="Fake" Text Width 254

OleDb will use these definitions when reading that file, resulting in a string of dashes for the "Fake" column in the output:

One additional benefit (among many) of using a Schema.INI is that you can name the columns there rather than using or aliasing F1, F2, F3 etc. The SQL for that output was just "SELECT * FROM Capitals.CSV"

Tip (for others to come): to specify UTF8 as the character set, use CharacterSet=65001 in the schema.

For more information see
- Schema.ini Text File Driver
- Code Page Identifiers

这篇关于OLEDB将CSV导入到VB.NET数据类读取“ - ”为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！