[RESOLVED] Reading comma delimited file

[inferr]

Hi,I'm new to visual basic programming, I'm trying to read a comma delimited file and assign the data to arrays for making a sales summary, can anyone give me some sugguestion about where to start? Thanks

the data looks like the following, where the first number is the sales ID, the rest are sales amount and the -1.0 indicates the end of the line.
I need to put the IDs in ID() and sum the total sales into TotalSales(), but I have problem reading in and summing up the data.

282, 8848.19, 8160.08, 2404.85, 6791.34, -1.0
721, 776.82, 6542.79, 2659.21, -1.0
267, 10893.29, 8967.67, 7773.35, 5773.49, 7856.12, -1.0
595, 1949.30, 985.77, 445.34, 9565.31, 6249.56, 7256.93, -1.0
.
.
.

[circuits2]

The information you put in the code window is your csv file. Can you show us your code so we can help you correct it?

[stanav]

What you're showing us is a bunch of numbers... How can we possibly know what each line stands for, and what each number in each column stands for? If you just want to read this data into an array, and since the number of fields in each line differs, you can use a 2-d jagged array. However, a regular 2-d array will work too. Use tream reader to open the text file, the read each line and parse it into and array. Search for reading csv file in this forum and you will be able to get something to help you started.

[circuits2]

Stan,

The original post tells you exactly what the lines of numbers are, which is why I only asked to see the code. The first number is the ID, the remaining numbers are sales figures and the EOL.

[colonel720]

you have a csv file? why dont you make a data connection to the excel document?

Or, if you want to do it by reading the file directly:

Private Sub ReadFile()
        Dim myfile As String = "yourCSVfilename.csv"
        Dim x() As arraylist = buildarrays(myfile)
        Dim ID() = x(0).ToArray
        Dim totalsales() = x(1).ToArray
 
        'Now you have your arrays filled with data, do with them as you wish.
    End Sub
 
    Private Function BuildArrays(ByVal sourcefile As String)
        Dim a As Integer
        Dim ID As New arraylist
        Dim totalsales As New arraylist
        Dim reader As New IO.Streamreader(sourcefile)
        Do While reader.endofstream = False
            Dim line As String = reader.readline()
            Dim contents() As String = split(line, ", ")
            ID.Add(contents(0))
            Dim b As Integer
            Dim totalsalesforline As Single = 0
            For b = 1 To ubound(contents) - 1
                totalsalesforline = totalsalesforline + CSng(contents(b))
            Next
            totalsales.Add(totalsalesforline)
        Loop
        reader.close()
        Dim x(1) As arraylist
        x(0) = ID
        x(1) = totalsales
        Return x
    End Function

This should work.

[inferr]

Here's what I have tried, but errors are found on the line "ID(x) = CInt(Sales(0))"

Dim ID() As Integer
    Dim totalsales() As Double    
Sub Input()
        Dim sr As IO.StreamReader = IO.File.OpenText("sales.txt")
        Dim Sales() As String
        Dim i As Integer
        Dim x As Integer = -1
 
        Do Until (sr.Peek = -1)
            Dim line As String = sr.ReadLine
            Sales = line.Split(","c)
            x += 1
            ID(x) = CInt(Sales(0))
            For i = 1 To Sales.GetUpperBound(0) - 1
                totalsales(x) += CDbl(Sales(i))
            Next
 
        Loop
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Input()
   End Sub

[inferr]

Thanks colonel720, I'll try that later when I get home.
Meanwhile, could anybody fix my codes? Thanks a lot!

[stanav]

This should do it:

'Class level variables
    Private ID(0) As Integer
    Private totalsales(0) As Double
 
    Private Sub ReadData()
        Dim sr As IO.StreamReader = IO.File.OpenText("C:\test.txt") 'Change the file path as needed
        Dim fields() As String
        Dim i As Integer = 0
        Dim x As Double = 0D
        Dim total As Double = 0D
        Do Until (sr.Peek = -1)
            fields = sr.ReadLine.Split(","c)
            If fields.Length > 1 Then
                ReDim Preserve ID(i)
                ReDim Preserve totalsales(i)
                ID(i) = fields(0)
                For j As Integer = 1 To fields.GetUpperBound(0) - 1
                    total += Double.Parse(fields(j))
                Next
                totalsales(i) = total
                i += 1
            End If
        Loop
        sr.Close()
    End Sub

To run the code, you just put under a button click or something this code

ReadData()
        Dim str As String
        For i As Integer = 0 To ID.GetUpperBound(0)
            str &= "Total sales for Salesperson " & ID(i).ToString & " is: " & totalsales(i).ToString() & vbCrLf
        Next
        MsgBox(str)

[inferr]

that worked
thank you very much