VBA 从 Web 服务器导入 UTF-8 CSV 文件

VBA importing UTF-8 CSV file from a web server

提问by user2741700

I have a UTF-8 CSV file stored on a web server. When I download the file put it on my hard drive and I then import it into an Excel sheet with this macro (from the macro recorder) :

我有一个 UTF-8 CSV 文件存储在网络服务器上。当我下载文件时，把它放在我的硬盘上，然后我用这个宏（从宏记录器）将它导入到 Excel 工作表中：

Sub Macro2()
Workbooks.OpenText Filename:= _
    "C:/myFile.csv", Origin _
    :=65001, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
    xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
    , Comma:=True, Space:=False, Other:=False
End Sub

All of the characters (vietnamese characters) are displayed correctly.

所有字符（越南字符）都正确显示。

When I try the same macro but instead of giving the local address of the file ("C:/myFile.csv") I pass the URL of the file ("http://myserver.com/myFile.csv") the CSV is correctly imported into my Excel sheet but the vietnamese characters are not displayed correctly anymore.

当我尝试使用相同的宏但没有提供文件的本地地址（“C:/myFile.csv”）时，我传递了文件的 URL（“ http://myserver.com/myFile.csv”）CSV已正确导入到我的 Excel 工作表中，但越南语字符不再正确显示。

I have also tried using the Data tab but the encoding seems be ignored by Excel:

我也尝试过使用“数据”选项卡，但 Excel 似乎忽略了编码：

With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;C:/myFile.csv" _
                , Destination:=Range("$A"))
                .Name = "myFile.csv"
                .FieldNames = True
                .RowNumbers = False
                .FillAdjacentFormulas = False
                .PreserveFormatting = True
                .RefreshOnFileOpen = False
                .RefreshStyle = xlInsertDeleteCells
                .SavePassword = False
                .SaveData = True
                .AdjustColumnWidth = True
                .RefreshPeriod = 0
                .TextFilePromptOnRefresh = False
                .TextFilePlatform = 65001
                .TextFileStartRow = 1
                .TextFileParseType = xlDelimited
                .TextFileTextQualifier = xlTextQualifierDoubleQuote
                .TextFileConsecutiveDelimiter = False
                .TextFileTabDelimiter = True
                .TextFileSemicolonDelimiter = False
                .TextFileCommaDelimiter = False
                .TextFileSpaceDelimiter = False
                .TextFileOtherDelimiter = "~"
                .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                .TextFileTrailingMinusNumbers = True
                .Refresh BackgroundQuery:=False
       End With

Sample data: ??; ?; ??; ?1/4; ?‰; ??,??

样本数据： ??; ?; ??; ?1/4; ?‰; ??,??

which Excel reads wrongly as: ??a?; ??; ????; ??1/4; ??a°; ??a?￠,??a?￠;

Excel 错误地读取为： ??a?; ??; ????; ??1/4; ??a°; ??a?￠,??a?￠;

回答by Uri Goren

If the characters are displayed correctly when you download the csvfile yourself, I'd divide the process to 2 stages:

如果您csv自己下载文件时字符显示正确，我会将过程分为两个阶段：

Downloading

下载

Sub DownloadFile(ByVal url As String, ByVal local As String)

Dim WinHttpReq As Object
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", url, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
    Set oStream = CreateObject("ADODB.Stream")
    oStream.Open
    oStream.Type = 1
    oStream.Write WinHttpReq.responseBody
    oStream.SaveToFile local, 2 
    oStream.Close
End If

End Sub

Loading CSV

加载 CSV

Sub OpenCsv(ByVal csvfile As String)
Workbooks.OpenText Filename:= _ 
csvfile,Local:=True,StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
, Comma:=True, Space:=False, Other:=False
End Sub

Note That:The Localparameter is the key here,it makes VBAuse your excel's local configuration (vietnamese), which is by default set to False.

需要注意的是：该Local参数是这里的关键，它使得VBA使用Excel的本地配置（越南），这是默认设置为False。

Putting it all together

把这一切放在一起

Sub DownloadAndLoad
  DownloadFile "http://myserver.com/myFile.csv","C:\myFile.csv"
  OpenCsv "C:\myFile.csv"
End Sub

回答by nathansclone4

I have been looking at a similar problem where we import utf-8 encoded csv files in to a worksheet. I am not pulling the data from a web server but this might help.

我一直在研究一个类似的问题，我们将 utf-8 编码的 csv 文件导入到工作表中。我不是从网络服务器中提取数据，但这可能会有所帮助。

My solution is to read the utf-8 file to a local variable then insert it into a sheet. I tried saving the data to a temp file with ansi encoding but doing this caused all the characters to lose their accents.

我的解决方案是将 utf-8 文件读取到局部变量，然后将其插入到工作表中。我尝试将数据保存到 ansi 编码的临时文件中，但这样做会导致所有字符丢失重音。

Function ReadUTF8CSVToSheet(file As String)
    Dim ws As Worksheet
    Dim strText As String

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    Set ws = Sheets.Add()
    intRow = 1
    For Each strLine In Split(strText, chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

    ReadUTF8CSVToSheet = ws.Name

End Function

' to run
strSheetName = ReadUTF8CSVToSheet("C:\temp\utf8file.csv")

回答by nathansclone4

IMO, there seems to be a bug/conflict in Excel when opening UTF-8/UTF-8-BOM files using the recorded macro code, specifically when the Originparameter is set to 65001which is supposedbe UTF-8.

IMO，似乎是在Excel中的错误/冲突时，使用录制的宏代码，特别是当开放UTF-8 / UTF-8-BOM文件Origin参数设置为65001它应该是UTF-8。

I have found two workarounds to this issue:

我找到了两个解决此问题的方法：

1. Remove the Originparameter from the function call and see if the file loads properly Workbooks.OpenText Filename:="C:\file.csv".

   MSDN says:

   If this argument is omitted, the method uses the current setting of the File Origin option in the Text Import Wizard.

   I would think that as soon as you link the file with Excel, it should try to read the header of the file and select the correct Country Codeautomatically (well, assuming the header is not missing).

2. I have tried different Country Codesand found that in my specific scenario setting Origin:=1252(1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) loads the file in Excel just fine.

1. Origin从函数调用中删除参数并查看文件是否正确加载Workbooks.OpenText Filename:="C:\file.csv"。

   MSDN 说：

   如果省略此参数，则该方法使用文本导入向导中文件源选项的当前设置。

   我认为一旦您将文件与 Excel 链接，它应该尝试读取文件的标题并自动选择正确的国家/地区代码（好吧，假设标题没有丢失）。

2. 我尝试了不同的国家/地区代码，发现在我的特定场景设置Origin:=1252( 1252 - windows-1252 - ANSI Latin 1; Western European (Windows)) 中将文件加载到 Excel 中就好了。