Modify a CSV file

[pyrusane]

I am hoping someone can help me with this. I am trying to figure out how to update data in a csv file with changes I make in a userform. I use one form to create the csv file, the code looks like this:

        Dim out As System.IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter("Agents.csv", True)
        out.WriteLine(txtAgID.Text & "," & txtFName.Text & "," & txtLName.Text & "," & txtHDate.Text & "," & txtTDate.Text & "," & txtSup.Text & "," & txtShift.Text & "," & txtBRank.Text)
        out.Close()
 

I have an identical userform that retrieves the data, the code looks like this:

Dim myLine As String
        myLine = txtAgID.Text
        Dim sr As System.IO.StreamReader
        sr = System.IO.File.OpenText("Agents.csv")
        sr.ReadLine()
 
        Dim values As String()
        While sr.Peek() >= 0
            values = sr.ReadLine().Split(",")
            If values(0) = myLine Then
 
                txtFName.Text = values(1)
                txtLName.Text = values(2)
                txtHDate.Text = values(3)
                txtTDate.Text = values(4)
                txtSup.Text = values(5)
                txtShift.Text = values(6)
                txtBRank.Text = values(7)
 
                Exit While
            Else
                lblStatus.Text = "Agent is not found in the database"
            End If
        End While
        lblStatus.Text = "Record Located"
        sr.Close()

I want to be able to change a record in one of those text boxes and have it save the change to the csv file. For example, if an agent's BidRank changes I want to update the existing record with the new BidRank. I tried this:

Dim myLine As String
        myLine = txtAgID.Text
        Dim sr As System.IO.StreamReader
        sr = System.IO.File.OpenText("Agents.csv")
 
        sr.ReadLine()
 
        Dim values As String()
        While sr.Peek() >= 0
            values = sr.ReadLine().Split(",")
            If values(0) = myLine Then
                values(1) = txtFName.Text
                values(2) = txtLName.Text
                values(3) = txtHDate.Text
                values(4) = txtTDate.Text
                values(5) = txtSup.Text
                values(6) = txtShift.Text
                values(7) = txtBRank.Text
 
                Exit While
            Else
                lblStatus.Text = "Agent is not found in the database"
            End If
        End While
        lblStatus.Text = "Record updated"
        sr.Close()

and it looks like it works (i.e. it pops no errors) but when I check the csv file it still has the old data. Any suggestions? Also, I am using Visual Studio.net 2008, forgot to mention this originally.

[Rabbit]

At no point in your code do you write anything.

[pyrusane]

I make the changes in the text box (for ex: txtBRank) and when I click the Update button it runs the code in the third box there. My goal is to set the lines in the array to the values in the text box but...I'm not sure how to do that. Tried using Streamwriter and Streamreader together and it did NOT like that. I should probably mention that I have a lot of experience doing stuff like this in VBA for Excel but very little in VB.net. I am mostly a novice with VB.net when it comes to anything more complex than a basic form or calculator.

[Rabbit]

The general methodology in VBA would be the same in VB.net. In VBA, you would not attempt to write a file as you have done here. If you post the code for how you would do this in VBA, you will see where you went wrong in your logic.

[pyrusane]

Ok, I see what you're saying. I'm not actually changing the value in the csv, I'm just changing the variable so that it references the textbox instead of the csv. I could just use the same code I used to write the data to begin with, but then I would have a duplicate record with slightly different data. I don't know how to overwrite the data rather than appending a duplicate record.

[pyrusane]

Eh, nvm, I got it to work by doing this:

    Dim i As Integer
        lblStatus.Text = ""
        Dim oExcel As Object = CreateObject("Excel.Application")
        Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\owner\Desktop\EWFM\EWFM\EWFM\bin\Debug\Agents.csv")
        Dim oSheet As Object = oBook.Worksheets(1)
        On Error Resume Next
        oExcel.DisplayAlerts = False
        For i = 2 To 50000
            If oSheet.Range("A" & i).value = txtAgID.Text Then
                oSheet.Range("B" & i).value = txtFName.Text
                oSheet.Range("C" & i).value = txtLName.Text
                oSheet.Range("D" & i).value = txtHDate.Text
                oSheet.Range("E" & i).value = txtTDate.Text
                oSheet.Range("F" & i).value = txtSup.Text
                oSheet.Range("G" & i).value = txtShift.Text
                oSheet.Range("H" & i).value = txtBRank.Text
                Exit For
            Else
            End If
        Next
        oBook.Close(SaveChanges:=True)
        oExcel.Quit()
        ReleaseComObject(oExcel)

[Mikkeee]

Pyrusane, you might want to reevaluate your csv technique. I once started down the path that you're on but there are many holes in your logic. A valid csv may contain embedded quotes and commas which are not intended to delimit the fields as long as they're embedded between quotes or properly escaped. I found a very nice csv reader which is super easy to use and extremely flexible. You should read the article at http://www.codeproject.com/Articles/...ast-CSV-Reader and decide for yourself. I'm not affiliated with the code in any way but I found it extremely fast and useful.

[!NoItAll]

Mikkeee is right - the apparent simplicity of a CSV file is misleading. Fields in CSV files can, and often will, include quotes and commas. If you do not account for that then you will eventually miss-parse a file and likely corrupt it.
so the following is perfectly valid

Field one,Field two,"field,three","field,"four"",field five
The results of a proper parser will be

Field one
Field two
field,three
field, "four"
field five

The results of your parser will be
Field one
Field two
"field
three"
"field
"four""
field five

...that's a mess

While it's tempting to say, "well I can control and prevent users from entering quotes and commas in fields" that creates a code base that someone may inherit someday that will make him/her stick a pin in your voodoo doll.

Des