[RESOLVED] Reading comma delimited file
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.
Code:
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
.
.
.
Re: Reading comma delimited file
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?
Re: Reading comma delimited file
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.
Re: Reading comma delimited file
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.
Re: Reading comma delimited file
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:
VB Code:
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.
Re: Reading comma delimited file
Here's what I have tried, but errors are found on the line "ID(x) = CInt(Sales(0))"
VB Code:
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
Re: Reading comma delimited file
Thanks colonel720, I'll try that later when I get home.
Meanwhile, could anybody fix my codes? Thanks a lot!
Re: Reading comma delimited file
This should do it:
VB Code:
'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
VB 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)
Re: [RESOLVED] Reading comma delimited file
that worked
thank you very much