convert ADODB binary stream to string vba

Question

I have the following problem: I have a CSV file which is stored on a server but it has 3 characters as delimiters: "[|]". I would like to load the data from the URL and fill the data in the columns of my Excel page using the [|] as delimiter. Until now I found code to load the file from a website using an ADODB recordset but I cannot get any further:

 myURL = "http://www.example.com/file.csv"
Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
WinHttpReq.Open "GET", myURL, False, "username", "password"
WinHttpReq.send

myURL = WinHttpReq.responseBody
If WinHttpReq.Status = 200 Then
Set oStream = CreateObject("ADODB.Stream")
oStream.Open
oStream.Type = 1 'binary type
oStream.Write WinHttpReq.responseBody
oStream.SaveToFile "E:\file.csv", 2 ' 1 = no overwrite, 2 = overwrite   
oStream.Close
End If

This works fine to save a file directly. But I do not want to save it to a file, I want to enter the data in the proper cells. Is there any way to do this? I would prefer not tu use Internet Explorer objects

Answer 1

Tested OK with a regular csv file:

Sub Tester()
    Dim myURL As String, txt As String, arrLines, arrVals
    Dim l As Long, v As Long, WinHttpReq As Object
    Dim rngStart As Range

    myURL = "http://www.mywebsite.com/file.csv"

    Set WinHttpReq = CreateObject("Microsoft.XMLHTTP")
    WinHttpReq.Open "GET", myURL, False, "username", "password"
    WinHttpReq.send

    txt = WinHttpReq.responseText

    'might need to adjust vbLf >> vbCrLf or vbCr
    '  depending on the file origin (Win/Unix/Mac)
    arrLines = Split(txt, vbLf)

    Set rngStart = ActiveSheet.Range("A1")

    For l = 0 To UBound(arrLines)
        arrVals = Split(arrLines(l), "[|]")
        For v = 0 To UBound(arrVals)
            rngStart.Offset(l, v).Value = arrVals(v)
        Next v
    Next l

End Sub

Answer 2

you can use the ADO.Stream also with local files with the LoadFromFile method and store the value into a local variable. I have here an example where this is used to read a file that uses UTF-8 code page.

Dim adoStream As ADODB.Stream
Dim strText As String

Set adoStream = New ADODB.Stream
adoStream.Charset = "UTF-8"
adoStream.Open
adoStream.LoadFromFile "C:\Temp\Datei.txt"
strText = adoStream.ReadText

adoStream.Close
Set adoStream = Nothing

If the file isn't a UTF-8 one then simply delete the row with the Charset. After that you ahve the entire file content in the variable strText. You can then use the split() function to cut by using the delimiter.

here is how I get page content:

Dim oRequest As Object
Set oRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
oRequest.Open "GET", "http://www.cboden.de"
oRequest.Send
MsgBox oRequest.ResponseText

this should also work for CSV