CSV file split method help?

[donavan]

I'm Sort of new, sort of old to VB I learned the old versions years back (before .net), so bare with me here. I am trying to write a script that will read the data from a CSV file and chop it up into a number of variable arrays.

CSV file looks something like this:

Uniquedata,date,data,data,data,,,,,,(and so on)
Uniquedata,date,data,data,data,,,,,,(and so on)
Uniquedata,date,data,data,data,,,,,,(and so on)

The number of lines is never the same but the number of datapoints in each line is always the same [56 datapoints]. The code below works up until I try to get the data to go from the aryTextFile() to the separate arrays[ie...SampleID()] for each data point[marked in red]. I'm sure I'm doing something stupidly simple here but I can't figure it out and I don't know what to search for to find the answer. All the variables and arrays are delcared in other areas of the code, so I don't think that is the issue.


If System.IO.File.Exists(FILE_NAME) = True Then
Dim objReader As New System.IO.StreamReader(FILE_NAME)
Do While objReader.Peek() <> -1
Linetext = Linetext & (objReader.ReadLine() & vbNewLine)
'split data string data from linetext into array
MsgBox(Linetext)
i = 0
Dim aryTextFile() As String = Linetext.Split(",")
For i = 0 To UBound(aryTextFile) : Next i
' put arytextfile array data into type arrays
SampleID(x) = aryTextFile(i)

x = x + 1
'used to clear Linetext after each line form the csv is read
Linetext = ""
Loop


Basically, I am trying to get line1[data1], line2[data1].... into var1() , then line1[data2], line2[data2].... into var2() and so on down the file. If my code is junk and you know of an easier way to do this please let me know. but any help is appreciated.


Thanks

 

[rcombs4]

It looks like you are trying to reverse the logic of a CSV file. Normally you read the file into an array where each element in the array holds information regarding a specific line of text. Looks like you are trying to read the file into an array where each element in the array holds all the data for each column.

Say we have a File with 10 records and 5 columns.

Normally we would have an array with 10 elements. You want an array with 5 elements.

That sound correct?

This code should put you on the right path

 'Read the file
        Dim lines() As String = IO.File.ReadAllLines("PATH")

        'Create array of List
        Dim varList(56) As List(Of String)
        'Iterate over each record
        For Each strRecord As String In lines
            'Split data
            Dim strLine() As String = strRecord.Split(",")
            'Iterate over each field
           For i As Integer=0 to strLine.Length-1
                varList(i).Add(strLine(i))
            Next
        Next

Im not sure if this is some odd syntax i've never seen before but I think your problem may be with your For...Next loop.

For i = 0 To UBound(aryTextFile) : Next i

I'm not sure what the : is doing there but it seems like that loop does nothing. What gets executed in the loops is what is between the 'UBound(arytextfile)' and 'Next i', which is only :

 

[Tom]

If Im understanding this correctly, you are attempting to seperate all the fields and group them together in there proper columns of data. Why not use a dataset/datatable and actually use columns instead of seperate arrays for each column of data?

        [COLOR="SeaGreen"]'I would define and use a TYPED dataset here instead [/COLOR]       
        [COLOR="Blue"]Dim [/COLOR]myDataset [COLOR="blue"]As New [/COLOR]DataSet

        [COLOR="blue"]Using [/COLOR]tfpFile [COLOR="blue"]As New [/COLOR]TextFieldParser(txtImportFile.Text)
            tfpFile.TextFieldType = FieldType.Delimited
            tfpFile.SetDelimiters(",")

            [COLOR="blue"]While Not[/COLOR] tfpFile.EndOfData
                [COLOR="blue"]Dim [/COLOR]astrCurrentRow() [COLOR="blue"]As String [/COLOR]= tfpFile.ReadFields
                [COLOR="blue"]Dim [/COLOR]row [COLOR="blue"]As [/COLOR]DataRow = myDataset.Tables([COLOR="darkred"]"TableName"[/COLOR]).NewRow

                row([COLOR="DarkRed"]"Column1"[/COLOR]) = astrCurrentRow(0)
                row([COLOR="darkred"]"Column2"[/COLOR]) = astrCurrentRow(1)
                row([COLOR="darkred"]"Column3"[/COLOR]) = astrCurrentRow(2)
                row([COLOR="darkred"]"Column4"[/COLOR]) = astrCurrentRow(3)
                row([COLOR="darkred"]"Column5"[/COLOR]) = astrCurrentRow(4)
                myDataset.Tables([COLOR="darkred"]"TableName"[/COLOR]).Rows.Add(row)
            [COLOR="blue"]End While
        End Using[/COLOR]