从CSV - VBA读取双精度值时出现问题

Question

我想从vba-excel中读取csv文件，但我遇到了双精度值的问题，例如，csv: 125.5中的值是不带点读取的。所以我得到1255。我的代码：

Dim rs As New ADODB.Recordset
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & myDir & ";" & "Extended Properties=""text;HDR=Yes;FMT=Delimited()"";"
strSQL = "SELECT * FROM " & myFileName
rs.Open strSQL, strCon, 3, 3
IBH = rs("IBH")

我该怎么解决呢？

更新:我尝试了@Siddharth路由解决方案，但仍然有同样的问题。现在是我的代码：

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim myDate, myTime, IBH, IBL
Dim myDir As String, myFileName As String
Dim strSQL As String

myDir = Trim(shParams.Range("fp_path"))
myFileName = Trim(shParams.Range("fp_filename"))

With conn
 .Provider = "Microsoft.ACE.OLEDB.12.0"
 .ConnectionString = "Data Source=" & myDir & ";Extended Properties='text'"
 .Open
End With

strSQL = "SELECT * FROM " & myFileName
rs.Open strSQL, conn, 3, 3
rs.MoveLast

myDate = rs("Date")
myTime = rs("Time")
IBH = rs("IBH")
IBL = rs("IBL")

Debug.Print myDate, myTime, IBH, IBL

rs.Close
Set rs = Nothing

这就是结果：

​

这是我的csv：

​

​

Answer 1

此问题源于ACE引擎如何确定ADODB字段的类型。驱动程序将扫描一定数量的行，以确定整个列的类型。

更改连接字符串的

您可以尝试的一种快速方法是将连接字符串中的MaxScanRows更改为0。将其设置为0将扫描所有行以确定类型，请记住，这可能会对性能产生影响，具体取决于您的数据集的大小。

";Extended Properties='text;MaxScanRows=0;IMEX=0'"

这并不总是能给你想要的结果。假设我们有一个这样的数据集：

+--------------------------+
|       DoubleField        |
+--------------------------+
| 1                        |
| 2                        |
| 3                        |
| ...(996 more records...) |
| 1000.01                  |
+--------------------------+

驱动程序将看到999条看起来像整数的记录，以及1条看起来像双精度的记录。它将根据MajorityType来决定这个字段，它是一个整数，而不是一个双精度。老实说，我不完全确定这种类型确定是如何准确完成的，但它是这样的。我也看到过这样的例子，简单地将top记录更改为您想要的类型就可以了。例如。

+--------------------------+
|       DoubleField        |
+--------------------------+
| 1.00                     |
| 2                        |
| 3                        |
| ...(996 more records...) |
| 1000.01                  |
+--------------------------+

因此，另一种方法可以是格式化源文件，使其预先包含小数位。如果您控制源文件，这应该很容易做到，但情况并不总是如此。

使用架构INI文件

Comintern指出，如果升级MaxScanRows不起作用，那么获得您期望的每列类型的方法就是使用Schema.ini文件。

下面是一个link，介绍了这个问题。

要点是，创建一个文件，显式地定义每一列的每种类型。对于上面的人为设计的表格，这变成了：

[MyFileNameGoesHere.csv]
ColNameHeader = True
Format = CSVDelimited
Col1=DoubleField Double

然后将此文件另存为Schema.Ini，并将其放置在与要导入的文件相同的目录中。这种方法的好处是它只是创建一个文本文件，您甚至可以在VBA中毫不费力地完成此操作。这种方法的一个缺点是，如果您有很多文件要导入，可能很难管理所有的Schema.ini文件。

一种纯粹的方法

您可以在ADODB中创建一个内存表，并使用csv文件中的数据填充该表。这里有一个小的代码示例，可以帮助您入门。

Option Explicit

Private Function getTypedRS() As ADODB.Recordset
    Set getTypedRS = New ADODB.Recordset

    With getTypedRS
        'Add your other fields here
        .Fields.Append "DoubleField", adDouble
    End With
End Function

Public Sub CSVToADODB()
    Dim myTimer         As Double
    Dim FileNumber      As Long
    Dim FilePath        As String
    Dim FileData        As String
    Dim CSVArray        As Variant
    Dim i               As Long
    Dim rs              As ADODB.Recordset

    myTimer = Timer
    Set rs = getTypedRS()
    FilePath = "C:\Users\Ryan\Desktop\Example.csv"

    'Get the CSV
    FileNumber = FreeFile()
    Open FilePath For Binary Access Read As FileNumber
    FileData = Space$(LOF(FileNumber)) 'Create a buffer first, then assign
    Get FileNumber, , FileData
    Close FileNumber

    'My CSV is just a list of Doubles, should be relatively easy to swap out to process with ','
    CSVArray = Split(FileData, vbCrLf)

    'Add data
    rs.Open
    For i = LBound(CSVArray) + 1 To UBound(CSVArray) '+1 to skip header
        rs.AddNew
        rs.Fields("DoubleField").Value = CSVArray(i)
    Next
    rs.UpdateBatch
    rs.MoveLast

    Debug.Print rs.Fields("DoubleField").Value, "Processed 1000 records in: " & Timer - myTimer
End Sub

这种方法的好处是，它仍然相当快。我能够在大约0.03秒内加载1000个doubles，因为这里完成的大多数操作都是在内存中完成的。这也避免了对Schema.ini文件的需要，但是需要维护更多的代码，因此这是一种权衡。

Recommendation

我会尝试更改MaxScanRows，如果不起作用，创建一个Schema.ini文件。

Answer 2

尝尝这个

Sub Sample()
    Dim conn As New ADODB.Connection
    Dim RS As New ADODB.Recordset

    Dim FilePath As String, SheetName As String

    '~~> Replace this with relevant values
    FilePath = "C:\Users\routs\Desktop"
    Filename = "Sample.Csv"

    With conn
       .Provider = "Microsoft.ACE.OLEDB.12.0"
       .ConnectionString = "Data Source=" & FilePath & ";Extended Properties='text'"
       .Open
    End With

    strSQL = "select * from " & Filename

    RS.Open strSQL, conn

    '~~> Replace this with relevant field
    Debug.Print RS("Sale")
End Sub

​

​