>256 fields...parse comma delimited text file into array and average columns???

I am writing an application that will have to access comma delimited text files of numbers (both types: short and double). This text file has 318 columns (every time), 1-3 rows of data and no header line. I need to do some averaging of about 15 fields. I was using datatables, but that method is not working now that I cleared 256 columns (schema.ini column names are not taking...not sure if "F318" and similar would work). I am now looking into arrays for performing the computations.

Assume the text file looks something like this:

1,-3.45,5,7,6.676
2,-3.12,6,8,9.787

Does anyone have some suggestions on how to read this in a rectangular array and average a column I can split the first line into a single dimension array (with the code below), but I get confused on how to split the first line into fields by commas in the first row of the array, then split on vbcrlf to start the second and third rows, etc.

Dim sr As StreamReader = New StreamReader(fileName)
Dim inputLine As String = sr.ReadLine
sr.Close()
If Not (inputLine Is Nothing) Then
Dim values() As String = Split(inputLine, ",")
End If

Answer this question

>256 fields...parse comma delimited text file into array and average columns???

flash.tato
I'd read the whole file in, then split on vbCrLf to get each line, then split (as you were doing) on commas
shanejh
Thanks for the reply. I have created a single dimension array and split on vbcrlf then I create a rectangular array to store the actual table values (splitting each row on commas). I am still having a hard time understanding how to move on to the next row of the rectangular arrays...my code is below:

Dim fileName As String = "c:\!rtcm\adcp\ascii\1.txt"

Dim sr As StreamReader = New StreamReader(fileName)

Dim inputLine As String = sr.ReadToEnd

sr.Close()

Dim values() As String = Split(inputLine, vbCrLf) 'create array, each element is line in text file

Dim numRows As Integer = values.Length - 1 'subtract 2 to get actual number of lines/rows (there is an extra vbcrlf at end of file)

Dim irows As Integer 'hold number of rows (lines) in text file

Dim icolumns As Integer 'number of columns constant at 318

Dim rectarray(,) As String 'new array to hold all values in text file

For irows = 0 To values.GetUpperBound(0)

For icolumns = 0 To 317 'there will always be 318 columns in the text file

rectarray(irows, icolumns) = {{Split(values(irows), ",")}} 'here is the "expression expected" error

'am I doing this right, using nested for/next loops I always get confused moving on to the second and third lines

Next

Next
Shan McArthur
OK, I now have it working. Since the array is a string, the average array function will not be available. I figured out how to cleanly do it with the following code:

Dim fileName As String = "c:\!rtcm\adcp\ascii\1.txt"

Dim sr As StreamReader = New StreamReader(fileName)

Dim inputLine As String = sr.ReadToEnd

sr.Close()

Dim values() As String = Split(inputLine, vbCrLf) 'create array, each element is line in text file

Dim irows As Integer 'hold number of rows (lines) in text file

Dim icolumns As Integer 'number of columns constant

Dim rectarray(values.Length - 2, 77) As String

Dim dblAverage, dblSum As Double

For irows = 0 To values.GetUpperBound(0) - 1

Dim valueSplit() As String = Split(values(irows), ",")

For icolumns = 0 To 77

rectarray(irows, icolumns) = valueSplit(icolumns)

Next

dblSum += rectarray(irows, 22) 'here is where the column is summed

Next

dblAverage = dblSum / values.GetUpperBound(0) 'here is where the division takes place (averaging)
thukralz
more like this....

Dim fileName As String = "c:\!rtcm\adcp\ascii\1.txt"
Dim sr As StreamReader = New StreamReader(fileName)
Dim inputLine As String = sr.ReadToEnd
sr.Close()

Dim Lines() As String = Split(inputLine, vbCrLf) '~ I've renamed your values to Lines

For irow As Integer = 0 To Lines.GetUpperBound(0) - 1 '~ This loops for each line in the file

Dim Columns() As String = Split(Lines(irow), ",") '~ This line splits each line into a 317 array of numbers

For icolumns As Integer = 0 To 317 'there will always be 318 columns in the text file

'~ Do your processing/average of the numbers here

Next '~ Column

Next '~ Line

So first it reads the file in, splits each line into the Lines() array, then loops for each line in that array, and splits each value into a array called columns, is this what you need
Dhondtie
That is close, but I needed a rectangular array similar to a table. I needed to size it before I started filling it. My number of columns changed again (78 now). Speaking of, should I stick with datatables with 78 columns, or are arrays quicker

I got it to work with the following code, but I am still trying to figure out how to compute column averages...is there a way other than for/next loops (for all array values in a column, sum up and divide by the number of rows)

Dim fileName As String = "c:\!rtcm\adcp\ascii\1.txt"

Dim sr As StreamReader = New StreamReader(fileName)

Dim inputLine As String = sr.ReadToEnd

sr.Close()

Dim values() As String = Split(inputLine, vbCrLf) 'create array, each element is line in text file

Dim irows As Integer 'hold number of rows (lines) in text file

Dim icolumns As Integer 'number of columns constant at 318

Dim rectarray(values.Length - 2, 77) As String

MsgBox("values.length: " & values.Length.ToString & ", values.getupperbound: " & values.GetUpperBound(0).ToString)

For irows = 0 To values.GetUpperBound(0) - 1

Dim valueSplit() As String = Split(values(irows), ",")

For icolumns = 0 To 77

rectarray(irows, icolumns) = valueSplit(icolumns)

Next

Next
>256 fields...parse comma delimited text file into array and average columns???

Answer this question