Comma-separated string to data table error: input array is longer

I am trying to put data received from an API call that is comma-delimited into a datatable.

The data comes in something like this:

Name,ID,Date,Supervisior CRLF
Joe,123,1/1/2020,George CRLF
Mike,456,2/1/2020,George CRLF
Dan,789,4/1/2021,George

If there is only one row of data then my code works the data displays on screen just fine.
If there is more than one row I get an error "Input array is longer than the number of columns in this table."

I tried doing a split on comma and environment new line (also vbCRLF); none of those resolved the issue.

Any ideas on how I can resolve this?

Here is my code:

Dim vartable As DataTable = New DataTable()
vartable.Columns.Add("Name", GetType(String))
vartable.Columns.Add("ID", GetType(String))
vartable.Columns.Add("Date", GetType(String))
vartable.Columns.Add("Supervisior", GetType(String))

Dim inputstring As String
inputstring = (apiresponse)  'redacted API code as it works fine If I just display 'raw data to text field

Dim rowData As String() = inputstring.Split(New Char() {",",Environment.NewLine}) 

vartable.Rows.Add(rowData) 'this is where I get the input array error if 'more than one row of 'data

GridView1.DataSource = vartable
GridView1.DataBind()

Solution

It looks like you're expecting the Split() function to act on each of the delimiters separately, so you get an array of arrays, with each element in the outer array holding one line/row. This is not how it works.

You need to separate the lines first, and then in a loop for each line separate the contents by comma. The test way to do this is NOT by calling Split(). Instead, you can use a StringReader (which is different from StreamReader):

Using rdr As New StringReader(apiresponse)
    Dim line As String = rdr.ReadLine()
    While line IsNot Nothing
        Dim rowData As String() = line.Split(","c)
        vartable.Rows.Add(rowData)
        line = rdr.ReadLine()
    End While
End Using

But I would be surprised to learn the code to access the API doesn't also need to deal with streams at some point, even if you don't see it directly, meaning there exists a possible version of this that is even more efficient from using StreamReader connected to the api response directly.

For fun, since it's been a while since I've had to do this in VB, I made this extension module:

Public Module TextExt
    <Extension()> 
    Public Iterator Function AsLines(data As TextReader) As IEnumerable(Of String)
        Dim line As String = data.ReadLine()
        While line IsNot Nothing
            Yield line
            line = data.ReadLine()
        End While
    End Function

    <Extension()> 
    Public Iterator Function AsLines(data As String) As IEnumerable(Of String)
        Using rdr As New StringReader(data)
            For Each line As String In AsLines(rdr)
                Yield line
            Next
        End Using
    End Function
End Module

Which would let me write it like this:

For Each row As String() In apiresponse.AsLines().Select(Function(ln) ln.Split(","c))
    vartable.Rows.Add(row)
Next

Finally, I need to add my customary warning about how it's a really bad idea to use .Split() as a CSV parser.