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

Question

我有一个存储在Web服务器上的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

所有的字符（包括越南语字符）都被正确地显示。

当我尝试使用相同的宏，但是不是给出文件的本地地址（"C:/myFile.csv"），而是传递文件的 URL ("http://myserver.com/myFile.csv")，CSV 文件能够正确导入到我的 Excel 工作表中，但越南语字符不再显示正确。

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

With ActiveSheet.QueryTables.Add(Connection:= _
                "TEXT;C:/myFile.csv" _
                , Destination:=Range("$A$1"))
                .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

з¤ғдң‹ж•°жҚ®пәљГ‚вЂһ; Г‚; Г‚Лњ; Г‚1/4; ‰; Г‚в„ұ,Г‚в„ұ

Excel й”™иҮҮиҮ»еЏ–дёғпәљÂГұ€Ең; Â; ˜; Â1/4; ÂГұ€°; ÂГұвЂһВұ,ÂГұвЂһВұ;

- user2741700

---

谢谢，你知道我怎么改变那些HTTP头吗？ - user2741700

取决于服务器。您可以使用Web浏览器轻松检查：在Firefox中，您可以激活Web控制台，输入URL到地址栏并查看响应头“Content-type”。 - z̫͋

不应该这样。也许Excel会忽略它。 - z̫͋

我的测试表明，当文件中没有Unicode字节顺序标记时，Excel会感到困惑。当有一个时，它可以从URL正确打开，并且在这种情况下，服务器是否在头部提供charset=UTF-8并不重要。 - GSerg

@GSerg... 我刚刚验证了我打开的所有文件都是UTF-8编码，手动打开那些文件并录制宏时设置了Origin:=65001。当我完全删除Origin参数后，这些文件就能正常打开了... 我认为这可能是Excel的一个错误（可能是宏记录器或者简单地说UTF-8与Excel的65001之间没有关系... 不确定，但我很高兴在删除Origin参数后它可以正常工作了。 - user2140173

Answer 1

我一直在研究导入utf-8编码的csv文件到工作表中遇到的类似问题。虽然我没有从web服务器上获取数据，但以下解决方案可能会对您有所帮助。

我的解决方案是将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")

- nathansclone4

Answer 2

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

下载

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

加载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

请注意：这里的“Local”参数是关键，它让VBA使用您Excel的本地配置（越南语），默认设置为False。

将所有内容整合在一起。

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

- Uri Goren

---

嗨Uri。感谢您的回复。在我的情况下，Local:=True没有改变任何事情（这是因为我的本地设置是英国吗？更改我的本地设置不是一个真正的选项，因为我将处理许多语言的文件...）。例如，csv文件中有一个™，当我使用Workbooks.Open时，该字符仍然被加载为„¢。我注意到，当我手动执行此操作（而不是通过宏）时，csv会正确加载。我已经从手动步骤中记录了一个宏，但宏的行为不同。我已经阅读过csv文件可能缺少UTF-8标题的内容 - 您是否熟悉标题？ - user2140173

当读取UTF-8时，不应使用“Local”或对其产生影响。@Meehow是的，将UTF-8 BOM放入文件中可以修复它。 - GSerg

Answer 3

在使用记录的宏代码打开UTF-8/UTF-8-BOM文件时，Excel中似乎存在一个错误或冲突，特别是当将Origin参数设置为65001时，这个参数应该是代表UTF-8编码。

我找到了两种解决方法：

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

   MSDN说：

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

   我认为，一旦你将文件与Excel链接起来，它应该尝试读取文件头并自动选择正确的国家代码（假设文件头不缺失）。

2. 我尝试了不同的国家代码，发现在我的特定场景中，将Origin:=1252（1252 - windows-1252 - ANSI Latin 1; Western European (Windows)）设置能够正常加载Excel文件。

- user2140173