[2008] Editing CSV File

[Orbit__]

I have a CSV File and i want to edit it and make a new column in it

my current text in CSV file (Some)
Username,Your referral since,Last click,Clicks
R1181312,2008-06-13 16:49:00,2008-07-18 09:27:00,40
R1477140,2008-06-13 16:49:00,2008-07-17 10:18:00,73
R30994,2008-06-13 16:49:00,2008-07-18 09:47:00,87
R1310392,2008-06-13 16:49:00,2008-07-18 11:13:00,33
R290032,2008-06-13 16:49:00,2008-07-17 14:17:00,80
R328542,2008-06-13 16:49:00,2008-07-18 12:57:00,84

Now i want to do is add another column named "Average Clicks"
It should do is calculate the days from Your referral since till taday and then divide clicks by days

Is it possible?

[Orbit__]

I need some help here

[jmcilhinney]

Of course it's possible. A CSV file is just plain text. If you want more data then you simply open the file and write to it.

I believe that you already know how to use ADO.NET to read that file into a DataTable. You should start by doing that. You could then add a new DataColumn to the DataTable and set its Expression property to perfrom the calculation automatically for all rows. Given the nature of the calculation though, it might be easier to loop through the rows of the DataTable and calculate the value yourself, writing out the line to the file as you go.

Using writer As New StreamWriter("file path here")
    For Each row As DataRow In table.Rows
        'Perform the calculation and write out the row.
    Next
End Using

[Orbit__]

the code i am using to open the file is

 Dim Filelocation As String
        Dim Textfilestext As String


        If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.OK Then

            Filelocation = OpenFileDialog1.FileName
            TextBox1.Text = Filelocation
            Textfilestext = IO.File.ReadAllText(OpenFileDialog1.FileName)

[jmcilhinney]

Weren't you using ADO.NET to read this CSV file in some other thread? Anyway, ReadAllText is the wrong way to read this file. It will just give you the whole file contents as a single string, which is useless. You need each individual field value so you'd have to break that string up into lines anyway, then each of those lines into fields. There's no point doing that when there's ways to read the file that will do that for you. As I said, ADO.NET can populate a DataTable for you. Alternatively you can use a TextFieldParser to read the file line by line, field by field.

[Orbit__]

Weren't you using ADO.NET to read this CSV file in some other thread? Anyway, ReadAllText is the wrong way to read this file. It will just give you the whole file contents as a single string, which is useless. You need each individual field value so you'd have to break that string up into lines anyway, then each of those lines into fields. There's no point doing that when there's ways to read the file that will do that for you. As I said, ADO.NET can populate a DataTable for you. Alternatively you can use a TextFieldParser to read the file line by line, field by field.

Ok if i use this code ADO.Net

    Private Function CsvToDataTable(ByVal filePath As String, ByVal hasHeaderLine As Boolean) As DataTable
        Dim dt As DataTable = Nothing
        Dim sourcePath As String = String.Empty
        Dim csvFile As String = String.Empty
        Dim conString As String = String.Empty
        Dim conn As OleDb.OleDbConnection = Nothing
        Dim adapter As OleDb.OleDbDataAdapter = Nothing
        Dim selString As String = String.Empty
        Dim header As String = "No"
        Try
            sourcePath = System.IO.Path.GetDirectoryName(filePath)
            csvFile = System.IO.Path.GetFileName(filePath)
            If hasHeaderLine Then header = "Yes"
            conString = String.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""text;HDR={1};FMT=Delimited"";", sourcePath, header)
            conn = New OleDb.OleDbConnection(conString)
            selString = "Select * From " & csvFile
            adapter = New OleDb.OleDbDataAdapter(selString, conn)
            dt = New DataTable(System.IO.Path.GetFileNameWithoutExtension(filePath))
            conn.Open()
            adapter.Fill(dt)
            conn.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        Finally
            adapter.Dispose()
            conn.Dispose()
        End Try
        Return dt
    End Function

how do i add a new column the code you gave me gives an error on streamwriter because i didnt dim in anywhere?

can u provide the exact code as i am new to this

[jmcilhinney]

You don't "Dim" something. You "declare" it. "Dim" is one way to declare a variable. The Using statement is another, which I used in my code. The point of a Using block is to dispose the object you create at the end of the block. In this case that closes the file and releases the resource back to the OS.

If you create a DataTable, as you are with that ADO.NET code, then you can create a new DataColumn object and add it to the table's Columns collection. That said, there's no point doing so. You may as well just loop through the rows of the table, calculate the new value and then write out the line to the file. If you're not actually going to use the data in the DataTable for anything else then adding the new data to it doesn't server a purpose.

[Orbit__]

If you create a DataTable, as you are with that ADO.NET code, then you can create a new DataColumn object and add it to the table's Columns collection. That said, there's no point doing so. You may as well just loop through the rows of the table, calculate the new value and then write out the line to the file. If you're not actually going to use the data in the DataTable for anything else then adding the new data to it doesn't server a purpose.

I want to use it for many purpses but the first thing i want to do is add a new column and do calculations in it

You may as well just loop through the rows of the table, calculate the new value and then write out the line to the file.

If i knew how to do that i wouldnt be asking

[jmcilhinney]

If i knew how to do that i wouldnt be asking

If I didn't know how to do it I would be reading the documentation for the DataColumn class and the DataTable.Columns property first. If I still couldn't work out how to do it, then I'd ask. I'd post what I had so far and ask if anyone could help me to change it to get it to work. Maybe it's just me but I always try first, then ask for help if I fail. Before you say it, I did the same when I was a beginner.