Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
747 views
in Technique[技术] by (71.8m points)

vba - How to parse line by line WinHTTP response: UTF-8 encoded 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

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

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

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!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

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

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...