vba 如何逐行解析 WinHTTP 响应：UTF-8 编码的 CSV？

How to parse line by line WinHTTP response: UTF-8 encoded CSV?

提问by Peter L.

As the next step for my happily solved problem (Not understanding why WinHTTP does NOT authenticate certain HTTPS resource) I need to prettily parse obtained CSV. At the moment I use the following solution:

作为我愉快解决的问题的下一步（不明白为什么 WinHTTP 不验证某些 HTTPS 资源），我需要漂亮地解析获得的 CSV。目前我使用以下解决方案：

If HTTPReq.Status = 200 Then
    If FSO.FileExists(CSV_Path) = True Then FSO.DeleteFile (CSV_Path)
    Set FileStream = CreateObject("ADODB.Stream")
    FileStream.Open
    FileStream.Type = 1
    FileStream.Write HTTPReq.responseBody
    FileStream.SaveToFile (CSV_Path)
    FileStream.Close
    ActiveWorkbook.Connections("Redmine Timelog").Refresh
    ActiveSheet.PivotTables("PivotTable_RM").PivotCache.Refresh
End If

That is, I save CSV to disk and then link it as data source to Excel. However, I'd like to have my Excel book self-sufficient, with no need to create additional files (for some quite obvious reasons).

也就是说，我将 CSV 保存到磁盘，然后将其作为数据源链接到 Excel。但是，我想让我的 Excel 书自给自足，而无需创建其他文件（出于一些非常明显的原因）。

The solution is pretty simple in my case: store WinHTTP.responseTextline by line on the separate Excel sheet (instead of currently stored linked CSV) and then use Text to DataExcel conversion. However, I face the following immediate troubles:

在我的情况下，解决方案非常简单：WinHTTP.responseText在单独的 Excel 工作表上逐行存储（而不是当前存储的链接 CSV），然后使用Text to DataExcel 转换。但是，我面临以下直接问题：

1. CSV is UTF-8 encoded, while raw WinHTTP response text seems to be NOT. Is there any way to interpret it using desired encoding?
2. How to split CSV into lines? Use Splitfunction or whatever else? CSV seems to use some of standard NewLine chars, and the data 99% should have NOT any of these.

1. CSV 是 UTF-8 编码的，而原始的 WinHTTP 响应文本似乎不是。有没有办法使用所需的编码来解释它？
2. 如何将CSV拆分成行？使用Split函数还是其他什么？CSV 似乎使用了一些标准的 NewLine 字符，而 99% 的数据应该没有这些。

There are many similar troubles solved, but I found nothing clear and acceptable for VBA so far, so any help will be appreciated. Thanks in advance!

解决了许多类似的问题，但到目前为止我发现 VBA 没有任何明确和可接受的问题，因此我们将不胜感激。提前致谢！

回答by Peter L.

Finally I found both solutions on my own:

最后我自己找到了两种解决方案：

1. CSV to UTF-8 conversion with the help of ADODB.Stream (see for more: http://www.motobit.com/tips/detpg_binarytostring/)
2. Splitting CSV and further parsing of strings array using Text to DataExcel routine

1. 借助 ADODB.Stream 将 CSV 转换为 UTF-8（有关更多信息，请参见：http: //www.motobit.com/tips/detpg_binarytostring/）
2. 使用Text to DataExcel 例程拆分 CSV 并进一步解析字符串数组

Below is the related part of code:

下面是代码的相关部分：

'CSV to UTF-8
Set FileStream = CreateObject("ADODB.Stream")
FileStream.Open
FileStream.Type = 1 'Binary
FileStream.Write HTTPReq.responseBody
FileStream.Position = 0
FileStream.Type = 2 'Text
FileStream.Charset = "UTF-8"
CSV_Text = FileStream.ReadText
FileStream.Close
'CSV Splitting
CSV_Strings = Split(Trim(CSV_Text), vbLf)
ThisWorkbook.Worksheets("RM_Log").Cells.ClearContents
Set OutputRange = ThisWorkbook.Sheets("RM_Log").Range("A1:A" & UBound(CSV_Strings) + 1)
OutputRange = WorksheetFunction.Transpose(CSV_Strings)
OutputRange.TextToColumns Destination:=ThisWorkbook.Sheets("RM_Log").Range("A1"), _
    DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, _
    Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _
    :=Array(Array(1, 3), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
    Array(7, 1), Array(8, 1), Array(9, 1)), DecimalSeparator:=".", _
    TrailingMinusNumbers:=True

As a result, my Excel file is now totally self-sufficient. Hope this will help someone else as well. Many thanks to everyone who left comments - they narrowed my search.

结果，我的 Excel 文件现在完全自给自足。希望这也能帮助其他人。非常感谢留下评论的每个人 - 他们缩小了我的搜索范围。

回答by user6523673

This line

这条线

OutputRange = WorksheetFunction.Transpose(CSV_Strings)

should be like this

应该是这样的

OutputRange.Formula = WorksheetFunction.Transpose(CSV_Strings)