如何在Excel 2010中使用VBA查询UTF-8编码的CSV文件? [英] How to query UTF-8 encoded CSV-files with VBA in Excel 2010?

问题描述

我想在具有以下数据库连接的Excel 2010中使用VBA查询UTF-8编码的CSV文件:

I would like to query an UTF-8 encoded CSV file using VBA in Excel 2010 with the following database connection:

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"，即，在问号之前添加了问号.我尝试了不同的CharacterSet，也尝试使用Microsoft.ACE.OLEDB.12.0，但到目前为止一切都没有成功.

All CSV files start with the BOM \xEF\xBB\xBF and the header line. Somehow the BOM isn't recognized correctly and the first column header gets read as "?header_name", i.e. a question mark gets prepended. I have tried different CharacterSets and I have also tried to use Microsoft.ACE.OLEDB.12.0, but everything was without success so far.

这是一个已知的错误，还是可以在不更改源文件编码的情况下获得正确的第一列标题名称的方法?

Is this a known bug or is there any way to get the right first column header name without changing the encoding of the source files?

推荐答案

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

The following procedure extracts the entire CSVfile into a new Sheet, clearing the BOM from the Header. It has the Path, Filename and BOM string as variables to provide flexibility.

使用此过程调用查询过程

Use this procedure to call the Query procedure

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

这篇关于如何在Excel 2010中使用VBA查询UTF-8编码的CSV文件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！