CSV string from GET request incomplete when storing in variable

Question

In VBA, I currently have code that returns a CSV string from the internet. However, it seems like the entire file is not being captured when I try to store it in a string variable using .responseText

I've heard string sizes are limited, which makes this problem understandable (I'm trying to store 1000 rows of data into a variable - which is probably not possible). I was wondering if there is a way to download the CSV file straight from the source given that I have the URL to get the response? Here is the relevant code that I have:

              Dim JiraReq As New MSXML2.XMLHTTP60    
              With JiraReq
                  ' Create Session
                 downloadUrl = https://myurlishere.atlassian.net 
                 ' This is not the actual form of the URL, but it does 
                  ' successfully grab the CSV string (but the string variable 
                  ' will not store all of it - probably because of the size)
                 .Open "GET", downloadUrl, False
                 .setRequestHeader "Authorization", "Basic " + EncodeBase64(username + ":" + password)
                 .setRequestHeader "Cache-Control", "no-cache"
                 .setRequestHeader "Pragma", "no-cache"
                 .send
               End With

                CSVData = JiraReq.responseText
                If JiraReq.Status = 200 Then
                    Set oStream = CreateObject("ADODB.Stream")
                    oStream.Open
                    oStream.Type = 1
                    oStream.Write JiraReq.responseText
                    Filename = "C:\file" & i & ".csv"
                    oStream.SaveToFile Filename, 2
                    oStream.Close
                End If

For reference, I have looked at the following links to try to solve this issue:

Arguments out of acceptable range

Downloading file from web with VBA

HTTP response text returning incomplete Data

I've heard string sizes are limited - In VBA the limit is ~2GB so that's not likely going to be the issue, similarly there is no specified limit on .responseText — Alex K.
– Alex K., Commented Jul 26, 2018 at 13:19
Would it be possible that the website I am using would only return part of the response @Alex K. ? — Jack Moody
– Jack Moody, Commented Jul 26, 2018 at 13:21
I've seen websites download more of the page as you scroll down.. maybe this is (not) happening here. — CLR
– CLR, Commented Jul 26, 2018 at 13:24
@Jeeped would something like the post from donato in this post work like you are saying with urldownloadtofile? — Jack Moody
– Jack Moody, Commented Jul 26, 2018 at 13:31

Jack Moody · Accepted Answer · 2018-07-30 15:52:11Z

My code ended up looking like this:

    For i = 1 To pageCount Step 1
        ' MsgBox "Entering For Loop"
        Dim startNumber As Integer
        startNumber = (i - 1) * 1000

        Dim downloadUrl As String
        downloadUrl = "https://myurl.atlassian.net/sr/jira.issueviews:searchrequest-csv-current-fields/" & CStr(filterID) & "/SearchRequest-" & CStr(filterID) & ".csv?tempMax=1000&pager/start=" & CStr(startNumber)

        With JiraReq
          ' Create Session
         Debug.Print "Getting page " & CStr(i) & " of " & CStr(pageCount)
         .Open "GET", downloadUrl, False
         .setRequestHeader "Authorization", "Basic " + EncodeBase64(username + ":" + password)
         .setRequestHeader "Cache-Control", "no-cache"
         .setRequestHeader "Pragma", "no-cache"
         .send
       End With

       file_name = "C:\VBA\file" & i & ".csv"
       If JiraReq.Status = 200 Then
            Set oStream = CreateObject("ADODB.Stream")
            oStream.Open
            oStream.Type = 1
            oStream.Write JiraReq.responseBody
            oStream.SaveToFile file_name, 2 ' 1 = no overwrite, 2 = overwrite
            oStream.Close
       End If

I had to change from .responseText to .responseBody.

Collectives™ on Stack Overflow

CSV string from GET request incomplete when storing in variable

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related