2

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!

Community
  • 1
  • 1
Peter L.
  • 7,276
  • 5
  • 34
  • 53
  • The `ResponseText` is interpreted in the encoding the server declares in the HTTP Content-Type header – Esailija Jan 10 '13 at 07:38
  • @Esailija Below is the valuable part of `HTTPReq.getAllResponseHeaders`: `Content-Length: 101967 Content-Type: text/csv; header=present Status: 200 Content-Transfer-Encoding: binary X-Runtime: 2163 Content-Disposition: attachment; filename="timelog.csv"` I'm not sure for the CSV in my case encoding is expected. – Peter L. Jan 10 '13 at 08:08
  • Since there is no `charset` specified in the `Content-Type` header, the default charset is `ISO-8859-1` per [RFC 2616 Section 3.7.1](http://tools.ietf.org/html/rfc2616#section-3.7.1): "When no explicit charset parameter is provided by the sender, media subtypes of the "text" type are defined to have a default charset value of "ISO-8859-1" when received via HTTP". Unless WinHTTP is choosing to analyze the data itself, such as by lookin for a UTF-8 BOM. – Remy Lebeau Jan 11 '13 at 02:52

2 Answers2

3

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.

Peter L.
  • 7,276
  • 5
  • 34
  • 53
0

This line

OutputRange = WorksheetFunction.Transpose(CSV_Strings)

should be like this

OutputRange.Formula = WorksheetFunction.Transpose(CSV_Strings)
Evgeniy Mishustin
  • 3,343
  • 3
  • 42
  • 81
user6523673
  • 1