如何逐行解析WinHTTP响应：UTF-8编码的CSV？ [英] How to parse line by line WinHTTP response: UTF-8 encoded CSV?

问题描述

作为我快乐解决问题的下一步（不理解为什么WinHTTP不认证某些HTTPS资源）我需要很好地解析获得的CSV。目前我使用以下解决方案：

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:

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

也就是说，我将CSV保存到磁盘，然后将其作为数据源链接Excel。但是，我想让我的Excel书自给自足，没有必要创建额外的文件（因为一些很明显的原因）。

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).

解决方案很简单在我的情况下：store WinHTTP.responseText 逐行在单独的Excel表（而不是当前存储的链接CSV），然后使用文本到数据 Excel转换。但是，我面临以下直接的麻烦：

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

1. CSV是UTF-8编码的，而原始WinHTTP响应文本似乎不是。是否有任何方法使用所需的编码解释它？


2. 如何将CSV拆分成行？使用拆分函数或其他什么？ CSV似乎使用一些标准的NewLine字符，数据99％应该没有这些。

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 Split function or whatever else? CSV seems to use some of standard NewLine chars, and the data 99% should have NOT any of these.

有很多类似的麻烦解决，但我发现没有什么清楚和可接受的VBA到目前为止，所以任何帮助将赞赏。提前感谢！

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!

推荐答案

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

Finally I found both solutions on my own:

1. 借助ADODB.Stream将CSV转换为UTF-8转换（详见： http://www.motobit.com/tips/detpg_binarytostring/ ）


2. 使用文本拆分CSV和进一步解析字符串数组到数据 Excel例程

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 Data Excel routine

以下是代码的相关部分：

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

因此，我的Excel文件现在完全自给自足。希望这将帮助别人以及。非常感谢所有留下评论的人 - 他们缩小了我的搜索范围。

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.

这篇关于如何逐行解析WinHTTP响应：UTF-8编码的CSV？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！