Help with traversing through data

[manofsteel00]

I'm having trouble with determining the best way to loop though a CSV file and break it into relevant segments.

For example:
The first line should read like the following:
Column A,Column B,Column C, Column D, Column E, Column F
Weight, Zone 2, Zone 3, Zone 9-10, Zone 11-12, Zone 13-16

Each segment represents a column of data. So my problem is what is the cleanest and best way to break the preceding line and be able to read the revelent column of data? In other words information for 9 and 10 is in the D column, and information for 11 and 12 is in the E column, etc. Any help would be GREATLY appreciated.

My initial attempt is posted below.

line = sr.readline
        headerArray = Split(line, ",")
        ReDim howMany(UBound(headerArray))
        For i = (LBound(headerArray) + 1) To UBound(headerArray) Step 1
            Dim tempArray
            tempArray = Split(Trim(headerArray(i)), " ")


            Dim j As Integer
            For j = LBound(tempArray) To UBound(tempArray) Step 1
                Dim dashArray()

                If (Mid(tempArray(j), 2, 1) = "-") Then
                    dashArray = Split(tempArray(j), "-")
                    ReDim Preserve headerArray(UBound(headerArray) + 1)
                    headerArray(j) = dashArray(0)
                    headerArray(j + 1) = dashArray(1)
                End If

                If (IsNumeric(tempArray(j))) Then
                    headerArray(i) = Format(Val(Trim(tempArray(j))), "000")
                End If
            Next
        Next

[manofsteel00]

Has anyone had a similiar problem like this one before? If so please let me know. Thank you.

[jmcilhinney]

You can use ADO.NET to read a CSV file straight into a DataTable. See www.connectionstrings.com for the details of the connection string and how you have to adjust your SQL code.