Linq - Index was outside the bounds of the array

Question

I currently have a program that uses Linq to read a CSV file. Everything was working great until a recent change was done where the CSV file had records being added to it where the last 2 columns where empty.

So, the error I am getting now is Index was outside the bounds of the array. The question I have is how do I handle a column where an empty or null value is set in the CSV file?

Here's my abbreviated code below. Thanks for any help in advance.

Function readLINQ(ByVal strCustomerFile As String) As DataTable

    Dim readlines = File.ReadAllLines(strCustomerFile)
    File.WriteAllLines(strCustomerFile, readlines.Skip(4).ToArray())

    Dim gTable As New DataTable("CashFile")
    With gTable
        .Columns.Add("Num").DataType = GetType(System.String)
        .Columns.Add("EnvNum").DataType = GetType(System.String)
        .Columns.Add("TransNum").DataType = GetType(System.String)
        .Columns.Add("Envelope").DataType = GetType(System.String)
        .Columns.Add("RemitterName").DataType = GetType(System.String)
        .Columns.Add("InvoiceNumber").DataType = GetType(System.String)

    End With
    Dim lines As String() = System.IO.File.ReadAllLines(strCustomerFile)
    Dim pattern As String = ",(?=(?:[^""]*""[^""]*"")*(?![^""]*""))"



    Dim r As System.Text.RegularExpressions.Regex = New System.Text.RegularExpressions.Regex(pattern)
    Dim custs = From line In lines Where line <> header AndAlso Not String.IsNullOrEmpty(line)
                Let data = r.Split(line)
                Select New With {.Num = data(0), .EnvNum = data(1), .TransNum = data(2), .Envelope = data(3), .RemitterName = data(4), .InvoiceNumber = data(5)}

    Dim xRow As DataRow
    For Each row In custs
        xRow = gTable.NewRow()
        xRow.ItemArray = {row.Num, row.EnvNum, row.TransNum, row.Envelope, _
                          row.RemitterName, row.InvoiceNumber}

        gTable.Rows.Add(xRow)
    Next

    readlines = Nothing
    lines = Nothing

    Return gTable
End Function

Gert Arnold · Accepted Answer

You could simply use

Let data = r.Split(line + ",,")

by which you add two empty entries add the end of each line. They are ignored for lines with 6 entries and for lines with 4 entries they add two empty entries, so data(4) and data(5) are not out of bounds.

This causes RemitterName and InvoiceNumber to be empty strings. I assume that is what should happen when the last 2 columns are absent.

Linq - Index was outside the bounds of the array

Answers (2)

Related Questions