By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,515 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,515 IT Pros & Developers. It's quick & easy.

Modify a CSV file

P: 4
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:

Expand|Select|Wrap|Line Numbers
  1.         Dim out As System.IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter("Agents.csv", True)
  2.         out.WriteLine(txtAgID.Text & "," & txtFName.Text & "," & txtLName.Text & "," & txtHDate.Text & "," & txtTDate.Text & "," & txtSup.Text & "," & txtShift.Text & "," & txtBRank.Text)
  3.         out.Close()
  4.  
I have an identical userform that retrieves the data, the code looks like this:

Expand|Select|Wrap|Line Numbers
  1. Dim myLine As String
  2.         myLine = txtAgID.Text
  3.         Dim sr As System.IO.StreamReader
  4.         sr = System.IO.File.OpenText("Agents.csv")
  5.         sr.ReadLine()
  6.  
  7.         Dim values As String()
  8.         While sr.Peek() >= 0
  9.             values = sr.ReadLine().Split(",")
  10.             If values(0) = myLine Then
  11.  
  12.                 txtFName.Text = values(1)
  13.                 txtLName.Text = values(2)
  14.                 txtHDate.Text = values(3)
  15.                 txtTDate.Text = values(4)
  16.                 txtSup.Text = values(5)
  17.                 txtShift.Text = values(6)
  18.                 txtBRank.Text = values(7)
  19.  
  20.                 Exit While
  21.             Else
  22.                 lblStatus.Text = "Agent is not found in the database"
  23.             End If
  24.         End While
  25.         lblStatus.Text = "Record Located"
  26.         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:
Expand|Select|Wrap|Line Numbers
  1. Dim myLine As String
  2.         myLine = txtAgID.Text
  3.         Dim sr As System.IO.StreamReader
  4.         sr = System.IO.File.OpenText("Agents.csv")
  5.  
  6.         sr.ReadLine()
  7.  
  8.         Dim values As String()
  9.         While sr.Peek() >= 0
  10.             values = sr.ReadLine().Split(",")
  11.             If values(0) = myLine Then
  12.                 values(1) = txtFName.Text
  13.                 values(2) = txtLName.Text
  14.                 values(3) = txtHDate.Text
  15.                 values(4) = txtTDate.Text
  16.                 values(5) = txtSup.Text
  17.                 values(6) = txtShift.Text
  18.                 values(7) = txtBRank.Text
  19.  
  20.                 Exit While
  21.             Else
  22.                 lblStatus.Text = "Agent is not found in the database"
  23.             End If
  24.         End While
  25.         lblStatus.Text = "Record updated"
  26.         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.
Feb 3 '13 #1
Share this Question
Share on Google+
7 Replies


Rabbit
Expert Mod 10K+
P: 12,382
At no point in your code do you write anything.
Feb 4 '13 #2

P: 4
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.
Feb 4 '13 #3

Rabbit
Expert Mod 10K+
P: 12,382
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.
Feb 4 '13 #4

P: 4
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.
Feb 4 '13 #5

P: 4
Eh, nvm, I got it to work by doing this:
Expand|Select|Wrap|Line Numbers
  1.     Dim i As Integer
  2.         lblStatus.Text = ""
  3.         Dim oExcel As Object = CreateObject("Excel.Application")
  4.         Dim oBook As Object = oExcel.Workbooks.Open("C:\Users\owner\Desktop\EWFM\EWFM\EWFM\bin\Debug\Agents.csv")
  5.         Dim oSheet As Object = oBook.Worksheets(1)
  6.         On Error Resume Next
  7.         oExcel.DisplayAlerts = False
  8.         For i = 2 To 50000
  9.             If oSheet.Range("A" & i).value = txtAgID.Text Then
  10.                 oSheet.Range("B" & i).value = txtFName.Text
  11.                 oSheet.Range("C" & i).value = txtLName.Text
  12.                 oSheet.Range("D" & i).value = txtHDate.Text
  13.                 oSheet.Range("E" & i).value = txtTDate.Text
  14.                 oSheet.Range("F" & i).value = txtSup.Text
  15.                 oSheet.Range("G" & i).value = txtShift.Text
  16.                 oSheet.Range("H" & i).value = txtBRank.Text
  17.                 Exit For
  18.             Else
  19.             End If
  20.         Next
  21.         oBook.Close(SaveChanges:=True)
  22.         oExcel.Quit()
  23.         ReleaseComObject(oExcel)
Feb 4 '13 #6

Mikkeee
P: 94
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.
Feb 4 '13 #7

!NoItAll
100+
P: 296
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
Feb 9 '13 #8

Post your reply

Sign in to post your reply or Sign up for a free account.