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

Solution 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

Solution 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

Comments

[g00golplex]

Hi Cboden, The code you show here is what I use but if you get data from a website you cannot use the ADODB stream as a text, it has to be binary. So I can load the file from the website and store it as a local file in text format and read this out but that is not what I want. I want to load the file from the web and convert the data using the "[|]" delimiter in the proper columns of the excel sheet. thankx

[cboden]

a I see ... the problem isn't the stream ... it's the HTTP request. Why not using WinHttp.WinHttpRequest.5.1 instead of XMLHTTP? Have added a code sample to the answer.

[g00golplex]

Hi Tim! This code really did it!!!! I got my data from the URL with the code as is!!!!! Sadly I do not have enough reputation to post this question as being answered! But this solution works spotless!!!! THANKS