Importing csv from API into a worksheet.

Several techniques for dealing with CSV files that use double quotes arounf fields can be found on this page:

• Read CSV File

I have used the sample code from the first link to do this parsing in several other EE questions, but I think I made a small modification to the code.  An example of one of these questions is:

• Finding and Renaming Files.. Solutions | Experts Exchange

This is VBscript code, but it could easily be adapted to VBA.

There are also some other approaches mention on the first link above if you want to explore them.

---

»bp

Why not simply open the csv in Excel, copy the cells and paste them into your workbook?

CSV is simply plain text, you can break it into line and break lines into cells.  Thus you can iterate over the cells and append them. but without some real data, it very hard to guide you.

As I read the question, I believe the complexity of the data involved, while it is in a CSV format, it has data in the columns that contains the field delimiter.  When that occurs the data provider follows the "standard" and encloses the column data in double quotes.  Notice the sample data mock up in the original question:

"Test1","Test2","Test3,Test4"

Select allOpen in new window

As a result simple approaches like using Split() on commas will not work, since commas can be contained in data.  This is what the routine I mentioned above gets around, by intelligently handling quoted or unquoted columns.

---

»bp

You have the data:

strResult = objHTTP.responseText

Select allOpen in new window

But what does it contain? One line only? Like your sample:

"Test1", "Test2", "Test3,Test4"

Select allOpen in new window

Probably more. But how are they formatted? Without a representative sample, it is difficult to be specific about the solution.

Anyway, you most likely will have to read the data line by line, parsing it using some method, copy the retrieved data to a Range of one row, and then pick the next line.

OK. What is the expected outcome, please?

You can use this method to beef up the items of one line to be splittable:

Public Sub SplitStringDemo()

    Dim Sample  As String
    Dim Index   As Integer
   
    Dim Items() As String
   
    ' Uncomment one sample:
    Sample = """test1,""test"",""test3,test4"""
    'Sample = "1,2,3"
   
    Items = Split(Replace(Replace(Replace(Sample, """,", ""","""), ",""", ""","""), ",", ""","""), """,""")
    Debug.Print Sample
    For Index = LBound(Items) To UBound(Items)
        Debug.Print Index, Replace(Items(Index), """", "")
    Next
   
End Sub

Select allOpen in new window

Then, you could either pass the results to one array which you then write to the range, or you could split line by line and write one row of data by the time.

Sample output:

"test1,"test","test3,test4"
 0            test1
 1            test
 2            test3
 3            test4

Select allOpen in new window

Here's a similar example (leveraging Gustav's example) using the CSVParse() function I mentioned in earlier post.  Notice that it makes the gymnastics a lot easier on the incoming data.  It also correctly picks up the 3 data values (one with embedded comma) as we want.

Test Code:

Option Explicit

Public Sub SplitStringDemo()

    Dim Sample  As String
    Dim Index   As Integer
   
    Dim Items   As Variant
   
    Sample = "test1,""test2"",""test3,test4"""
    Debug.Print Sample
   
    Items = CSVParse(Sample)
    
    For Index = LBound(Items) To UBound(Items)
        Debug.Print Index, Items(Index)
    Next
   
End Sub


' Function to parse comma delimited line and return array of field values.
Function CSVParse(ByVal strLine) As Variant
    Dim arrFields
    Dim blnIgnore
    Dim intFieldCount
    Dim intCursor
    Dim intStart
    Dim strChar
    Dim strValue

    Const QUOTE = """"
    Const QUOTE2 = """"""

    ' Check for empty string and return empty array.
    If (Len(Trim(strLine)) = 0) Then
        CSVParse = Array()
        Exit Function
    End If

    ' Initialize.
    blnIgnore = False
    intFieldCount = 0
    intStart = 1
    arrFields = Array()

    ' Add "," to delimit the last field.
    strLine = strLine & ","

    ' Walk the string.
    For intCursor = 1 To Len(strLine)
        ' Get a character.
        strChar = Mid(strLine, intCursor, 1)
        Select Case strChar
            Case QUOTE
                ' Toggle the ignore flag.
                blnIgnore = Not blnIgnore
            Case ","
                If Not blnIgnore Then
                    ' Add element to the array.
                    ReDim Preserve arrFields(intFieldCount)
                    ' Makes sure the "field" has a non-zero length.
                    If (intCursor - intStart > 0) Then
                        ' Extract the field value.
                        strValue = Mid(strLine, intStart, _
                            intCursor - intStart)
                        ' If it's a quoted string, use Mid to
                        ' remove outer quotes and replace inner
                        ' doubled quotes with single.
                        If (Left(strValue, 1) = QUOTE) Then
                            arrFields(intFieldCount) = _
                                Replace(Mid(strValue, 2, _
                                Len(strValue) - 2), QUOTE2, QUOTE)
                        Else
                            arrFields(intFieldCount) = strValue
                        End If
                    Else
                        ' An empty field is an empty array element.
                        arrFields(intFieldCount) = Empty
                    End If
                    ' increment for next field.
                    intFieldCount = intFieldCount + 1
                    intStart = intCursor + 1
                End If
        End Select
    Next
    ' Return the array.
    CSVParse = arrFields
End Function

Select allOpen in new window

Sample Output:

test1,"test2","test3,test4"
 0            test1
 1            test2
 2            test3,test4

Select allOpen in new window

---

»bp

You keep changing the sample data, now on third edition. That's not easy, neither fair.

Try this in an Excel module.  Adapt to your needs as appropriate, it demonstrates the concept.

Option Explicit

Public Sub SplitStringDemo()

    Dim Sample  As String
    Dim Index   As Integer
    Dim Items   As Variant
    Dim ws As Worksheet
   
    Sample = "test1,""test2"",""test3,test4"""
   
    Items = CSVParse(Sample)
    
    With ActiveSheet
        For Index = LBound(Items) To UBound(Items)
            .Cells(1, Index + 1) = Index + 1
            .Cells(2, Index + 1) = Items(Index)
            Debug.Print Index, Items(Index)
        Next
    End With
   
End Sub


' Function to parse comma delimited line and return array of field values.
Function CSVParse(ByVal strLine) As Variant
    Dim arrFields
    Dim blnIgnore
    Dim intFieldCount
    Dim intCursor
    Dim intStart
    Dim strChar
    Dim strValue

    Const QUOTE = """"
    Const QUOTE2 = """"""

    ' Check for empty string and return empty array.
    If (Len(Trim(strLine)) = 0) Then
        CSVParse = Array()
        Exit Function
    End If

    ' Initialize.
    blnIgnore = False
    intFieldCount = 0
    intStart = 1
    arrFields = Array()

    ' Add "," to delimit the last field.
    strLine = strLine & ","

    ' Walk the string.
    For intCursor = 1 To Len(strLine)
        ' Get a character.
        strChar = Mid(strLine, intCursor, 1)
        Select Case strChar
            Case QUOTE
                ' Toggle the ignore flag.
                blnIgnore = Not blnIgnore
            Case ","
                If Not blnIgnore Then
                    ' Add element to the array.
                    ReDim Preserve arrFields(intFieldCount)
                    ' Makes sure the "field" has a non-zero length.
                    If (intCursor - intStart > 0) Then
                        ' Extract the field value.
                        strValue = Mid(strLine, intStart, _
                            intCursor - intStart)
                        ' If it's a quoted string, use Mid to
                        ' remove outer quotes and replace inner
                        ' doubled quotes with single.
                        If (Left(strValue, 1) = QUOTE) Then
                            arrFields(intFieldCount) = _
                                Replace(Mid(strValue, 2, _
                                Len(strValue) - 2), QUOTE2, QUOTE)
                        Else
                            arrFields(intFieldCount) = strValue
                        End If
                    Else
                        ' An empty field is an empty array element.
                        arrFields(intFieldCount) = Empty
                    End If
                    ' increment for next field.
                    intFieldCount = intFieldCount + 1
                    intStart = intCursor + 1
                End If
        End Select
    Next
    ' Return the array.
    CSVParse = arrFields
End Function

Select allOpen in new window

---

»bp

Ok sorry all, I don't think I explained myself adequately from the start. This is an api call so I have written the code to better demonstrate the issue. You will notice there is a 4th column splitting Titles that have a comma in.

Private Function GetCollection(URL As String) As Collection

    Dim strResult As String
    Dim objHTTP As Object
    Dim NumRows As Long
    Dim NumCols As Long
    
    Set objHTTP = CreateObject("Microsoft.XMLHTTP")
    
    objHTTP.Open "GET", URL, False
    objHTTP.send
    strResult = objHTTP.responseText
    arr = SplitTo2DArray(strResult, vbLf, ",")
    RowCount = UBound(arr, 1)
    columnCount = UBound(arr, 2)
    
    Dim var As Collection
    Set var = New Collection
    
    var.Add arr
    var.Add RowCount
    var.Add columnCount
    
    Set GetCollection = var
    

End Function

Private Function SplitTo2DArray(ByRef the_sValue As String, ByRef the_sRowSep As String, ByRef the_sColSep As String) As String()

    Dim vasValue                    As Variant
    Dim nUBoundValue                As Long
    Dim avasCells()                 As Variant
    Dim nRowIndex                   As Long
    Dim nMaxUBoundCells             As Long
    Dim nUBoundCells                As Long
    Dim asCells()                   As String
    Dim nColumnIndex                As Long

    ' Split up the table value by rows, get the number of rows, and dim a new array of Variants.
    vasValue = Split(the_sValue, the_sRowSep)
    nUBoundValue = UBound(vasValue)
    ReDim avasCells(0 To nUBoundValue)

    ' Iterate through each row, and split it into columns. Find the maximum number of columns.
    nMaxUBoundCells = 0
    For nRowIndex = 0 To nUBoundValue
        avasCells(nRowIndex) = Split(vasValue(nRowIndex), the_sColSep)
        nUBoundCells = UBound(avasCells(nRowIndex))
        If nUBoundCells > nMaxUBoundCells Then
            nMaxUBoundCells = nUBoundCells
        End If
    Next nRowIndex

    ' Create a 2D string array to contain the data in <avasCells>.
    ReDim asCells(0 To nUBoundValue, 0 To nMaxUBoundCells)

    ' Copy all the data from avasCells() to asCells().
    For nRowIndex = 0 To nUBoundValue
        For nColumnIndex = 0 To UBound(avasCells(nRowIndex))
            asCells(nRowIndex, nColumnIndex) = avasCells(nRowIndex)(nColumnIndex)
        Next nColumnIndex
    Next nRowIndex

    SplitTo2DArray = asCells()

End Function

Sub Click()

Dim ws As Worksheet
Set ws = ActiveWorkbook.Worksheets("Sheet1")
ws.Cells.ClearContents

Set csvCol = GetCollection("https://people.sc.fsu.edu/~jburkardt/data/csv/deniro.csv")
ws.Range("A1").Resize(csvCol.Item(2), csvCol.Item(3) + 1).Value = csvCol.Item(1)

End Sub

Select allOpen in new window

Hi Bill,

Yes this works, but surely there is an easier way to get a csv file off the web and into excel than this without saving the file locally and importing it?

This also works in less lines

Sub Macro1()

    ActiveWorkbook.Queries.Add Name:="deniro", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(Web.Contents(""https://people.sc.fsu.edu/~jburkardt/data/csv/deniro.csv""),3,"""",null,65001)," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Year,"", Int64.Type}, {""Score,"", Int64.Type}, {""Title"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " " & _
        "   #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=deniro;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [deniro]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "deniro"
        .Refresh BackgroundQuery:=False
    End With

End Sub

Well, given that the CSVParse routine was already developed, this was pretty "easy" to then apply to your need.  I guess that's the idea of reusable modular code.

Could it be done in a more specific way for your specific data pattern, probably.  But I suspect it would be more fragile too, and need more tuning if things changed slightly.

The tricky part is handling the various cases and coding for each.  If there were an "intelligent" Split function in VBA or Excel that would be easier, where it could deal with certain fields being double quoted because they might contain the delimiter.  But there isn't such a tool.

You could write the data returned to a CSV file, and then open it with Excel telling it that the double quote was in play, and comma was the delimiter, and it would parse it correctly, but I personally think writing a temporary file and creating a new workbook or worksheet with the data is "harder" than this approach.

---

»bp

Try using:

arr = SplitTo2DArray(strResult, vbLf, """,""")

Select allOpen in new window

and then strip the remaining double-quotes.

Gustav, that puts all values in one column

Right, two problems with that thought.  There are spaces around the comma delimiter sometimes, and there aren't always double quotes around each column.

---

»bp

There are spaces around the comma delimiter sometimes, ..

So the sample data has changed again? I give up.

Gustav the sample data hasn’t changed

Well, without representative data, we can't help you further.
You have the bits and pieces, so put together what fits, or you are left with manual pre-editing.

Gustav the data is in the code.
https://people.sc.fsu.edu/~jburkardt/data/csv/deniro.csv

What we have works but I'm interested to see if anyone knows how to do this with less code possibly using a textparser library or something else.

I have modified the initial question with working data.

Private Sub cmdKirimGET_Click()
    Dim strResult As String
    Dim objHTTP As Object
    Dim URL As String
    Set objHTTP = CreateObject("Microsoft.XMLHTTP")
    URL = "https://people.sc.fsu.edu/~jburkardt/data/csv/deniro.csv"
    objHTTP.Open "GET", URL, False
    objHTTP.send
    strResult = objHTTP.responseText
    Worksheets("Sheet1").Range("A1") = strResult
End Sub

Select allOpen in new window

This method will create your array which you can copy to the range:

Public Sub SplitStringDemo()

    Const MaxItems  As Integer = 3
   
    Dim Sample      As String
    Dim Index       As Integer
   
    Dim Items() As String
   
    Sample = "1974,  97, ""The Godfather, Part II"""
   
    Items = Split(Sample, ",", MaxItems)
    Debug.Print Sample
    For Index = LBound(Items) To UBound(Items)
        Debug.Print Index, Replace(LTrim(Items(Index)), """", "")
    Next
   
End Sub

Select allOpen in new window