如何在Excel 2010中使用VBA查询UTF-8编码的CSV文件？

Question

我想使用VBA在Excel 2010中查询一个UTF-8编码的CSV文件，使用以下数据库连接：

provider=Microsoft.Jet.OLEDB.4.0;;data source='xyz';Extended Properties="text;HDR=Yes;FMT=Delimited(,);CharacterSet=65001"

所有的CSV文件都以BOM \xEF\xBB\xBF和标题行开始。但某些情况下，BOM不能被正确识别，并且第一个列标题会被读取为“?header_name”，即在列名前面出现了问号。我尝试过不同的字符集，也尝试使用Microsoft.ACE.OLEDB.12.0，但是目前为止一切都没有成功。

这是一个已知的漏洞吗？还是有什么方法可以在不更改源文件编码的情况下获取正确的第一个列标题名称？

- p.vitzliputzli

---

你介意分享你的UTF-8编码的CSV文件吗？ - EEM

@EEM 每个简单的csv文件，例如 a，b，c\n 0.1，0.2，0.3\n 在开头带有 \xEF\xBB\xBF 的文件都有同样的问题。 - p.vitzliputzli

1. 我很好奇为什么只有 Microsoft.Jet.OLEDB.4.0 可以使用。
2. Connection:="TEXT;Path & Filename" 完全不适用吗？

- EEM

Answer 1

以下过程将整个 CSV 文件提取到新的 Sheet 中，并清除标题中的 BOM。它具有路径、文件名和 BOM 字符串作为变量，以提供灵活性。

使用此过程调用查询过程。

Sub Qry_Csv_Utf8()
Const kFile As String = "UTF8 .csv"
Const kPath As String = "D:\StackOverFlow\Temp\"
Const kBOM As String = "\xEF\xBB\xBF"
    Call Ado_Qry_Csv(kPath, kFile, kBOM)
End Sub

这是查询流程。

Sub Ado_Qry_Csv(sPath As String, sFile As String, sBOM As String)
Dim Wsh As Worksheet
Dim AdoConnect As ADODB.Connection
Dim AdoRcrdSet As ADODB.Recordset
Dim i As Integer

    Rem Add New Sheet - Select option required
    'With ThisWorkbook           'Use this if procedure is resident in workbook receiving csv data
    'With Workbooks(WbkName)     'Use this if procedure is not in workbook receiving csv data
    With ActiveWorkbook         'I used this for testing purposes
        Set Wsh = .Sheets.Add(After:=.Sheets(.Sheets.Count))
        'Wsh.Name = NewSheetName        'rename new Sheet
    End With

    Set AdoConnect = New ADODB.Connection
    AdoConnect.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & sPath & ";" & _
        "Extended Properties='text;HDR=Yes;FMT=Delimited(,);CharacterSet=65001'"

    Set AdoRcrdSet = New ADODB.Recordset
    AdoRcrdSet.Open Source:="SELECT * FROM [" & sFile & "]", _
        ActiveConnection:=AdoConnect, _
        CursorType:=adOpenDynamic, _
        LockType:=adLockReadOnly, _
        Options:=adCmdText

    Rem Enter Csv Records in Worksheet
    For i = 0 To -1 + AdoRcrdSet.Fields.Count
        Wsh.Cells(1, 1 + i).Value = _
            WorksheetFunction.Substitute(AdoRcrdSet.Fields(i).Name, sBOM, "")
    Next
    Wsh.Cells(2, 1).CopyFromRecordset AdoRcrdSet

End Sub

- EEM

---

+1 CopyFromRecordset 是一个不错的选择！它可以很好地处理逗号分隔的 csv 文件。我无法让它处理分号分隔的文件，因为 Delimited(;) 似乎只接受逗号而不接受其他字符。但是由于 OP 使用逗号，所以没问题。 - Daniel Dušek

Answer 2

我找到的唯一解决此问题的方法是使用 Schema.ini 文件。

我的测试 CSV 文件

Col_A;Col_B;Col_C
Some text example;123456789;3,14

我的测试csv文件的Schema.ini。

[UTF-8_Csv_With_BOM.csv] 
Format=Delimited(;)
Col1=Col_A Text
Col2=Col_B Long
Col3=Col_C Double

这个 Schema.ini 文件包含源 csv 文件的名称并描述了我的列。每一列都由它的名称和类型指定，但你可以指定更多信息。此文件必须位于与你的 csv 文件相同的文件夹中。更多信息 在这里。

最后是读取 csv 文件的 VBA 代码。请注意，HDR=No。这是因为列标题在 Schema.ini 中定义。

' Add reference to Microsoft ActiveX Data Objects 6.1 Library
Sub ReadCsv()

    Const filePath As String = "c:\Temp\StackOverflow\"
    Const fileName As String = "UTF-8_Csv_With_BOM.csv"
    Dim conn As ADODB.Connection
    Dim rs As New ADODB.Recordset

    Set conn = New ADODB.Connection
    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & filePath & _
        "';Extended Properties='text;HDR=No;FMT=Delimited()';"

    With rs
        .ActiveConnection = conn
        .Open "SELECT * FROM [" & fileName & "]"
        If Not .BOF And Not .EOF Then
            While (Not .EOF)
                Debug.Print rs.Fields("Col_A") & " " & _
                            rs.Fields("Col_B") & " " & _
                            rs.Fields("Col_C")
                .MoveNext
            Wend
        End If
        .Close
    End With

    conn.Close
    Set conn = Nothing

End Sub

Output

Some text example 123456789 3,14

- Daniel Dušek

---

谢谢，我会看一下的。不过，我认为这意味着我需要为每个不同的csv文件编程生成schema.ini。 - p.vitzliputzli