[RESOLVED] Help with CSV Data Manipulation

[Sp0oK]

Hi All,

I'm having issues trying to manipulate the data I read from one of my logged csv files.

I have a csv file with the following information:

Date, Product, QTY, ExtPrice, TotalPrice - (This line is not in the output it's just to show what each column is for)
25/04/2013,Soup - 12oz,1,3.30,3.30
25/04/2013,Soup - 12oz,2,3.30,3.30
30/04/2013,Cappuccino,1,2.40,2.40
30/04/2013,Cappuccino,1,2.40,2.40
30/04/2013,Cappuccino,2,2.40,4.80
30/04/2013,Cappuccino,1,2.40,2.40

the above is just a quick snippet of whats in the log file

what i want to do is display this result in the following way so that you can quickly see the qty sold and total price.

Date, Product, QTY, ExtPrice, TotalPrice - (This line is not in the output it's just to show what each column is for)
25/04/2013,Soup - 12oz,3,3.30,9.90
30/04/2013,Cappuccino,5,2.40,12

I read the csv into an arraylist and have tried various ways to try to accomplish this but without success. What I'm not sure about is if this is the best way to try to do what I want or if it's even possible having the data in that format

If anyone could please point me in the right direction it would be greatly appreciated

Thanks

Paul.

[dday9]

I've come up with a function that converts textfiles that have a delimiter to datatables, and that sounds exactly what you need. Take a look under 'Contributions' in my signature, I should have a link that says 'text to data' or something along those lines

[Sp0oK]

thanks dday9 will give it a go and let you know how i get on. I've Been going over this all day my brain is fried haha

[dday9]

I've Been going over this all day my brain is fried haha

Welcome to the beautiful world of programming my friend!

[IanRyder]

Hi,

I have not had a look at dday9's example in his/her signature but one way to easily do this is to use the TextFieldParser Class. Here is a quick example:-

Imports Microsoft.VisualBasic.FileIO
 
Public Class Form1
  Dim DT As New DataTable
 
  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    With DT.Columns
      .Add("Date", GetType(Date))
      .Add("Product", GetType(String))
      .Add("QTY", GetType(Integer))
      .Add("ExtPrice", GetType(Decimal))
      .Add("TotalPrice", GetType(Decimal))
    End With
 
    Using myReader As New TextFieldParser(Application.StartupPath & "\TextFile1.txt")
      myReader.SetDelimiters(",")
      While Not myReader.EndOfData
        Dim myFields() As String = myReader.ReadFields
        DT.Rows.Add(myFields)
      End While
    End Using
 
    DataGridView1.DataSource = DT
  End Sub
End Class

Hope that helps.

Cheers,

Ian

[Sp0oK]

thanks to the both of you. Both example create the datatables now just got to try and work out how to group the occurrences for each particular date / product and get the relevant data to recalculate the totals for qty / totalprice.

I'm assuming I need to look at a query and run that for each item in the datatable and return relevant results accordingly?

not really worked much with datatables

[IanRyder]

Hi,

now just got to try and work out how to group the occurrences for each particular date / product and get the relevant data to recalculate the totals for qty / totalprice.

I'm assuming I need to look at a query and run that for each item in the datatable and return relevant results accordingly?

Ah, I missed that bit, Yes!, You can do this using LINQ to Group your information and summarize the fields you need. Here is an example:-

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
  Dim mySummary = (From DR As DataRow In DT.Rows.Cast(Of DataRow)() _
                   Select curDate = DR(0).ToString, curProduct = DR(1).ToString, QTY = CInt(DR(2)), Price = CDec(DR(4)) _
                   Group By curDate, curProduct Into G = Group, _
                   TotalQTY = Sum(QTY), TotalPrice = Sum(Price)).ToList
  DataGridView2.DataSource = mySummary
End Sub

Hope that helps.

Cheers,

Ian

[Sp0oK]

Hi Ian,

thanks for all your assistance on this really appreciated. its now displaying all the results as required


again thanks to all for your help !

look forward to contributing myself where I can.

Paul