Kevin
asked on
An escape for the comma in a CSV file using VB.NET
Good Morning,
I am working with VB.NET and have the below code which writes to a CSV file.
My problem arises when the user uses a “,” as their value for the textboxes on my windows form.
So instead of writing the below values to the CSV:
It’s writing like the below instead:
I understand why this problem is happening and it’s because the “,” is being used to split the column. My problem however is I don’t know enough to find a fix for it as I am still very new to programming.
Is someone able to provide me with a fix for my scenario? My code is below.
Kindly advise,
Regards,
N
I am working with VB.NET and have the below code which writes to a CSV file.
My problem arises when the user uses a “,” as their value for the textboxes on my windows form.
So instead of writing the below values to the CSV:
It’s writing like the below instead:
I understand why this problem is happening and it’s because the “,” is being used to split the column. My problem however is I don’t know enough to find a fix for it as I am still very new to programming.
Is someone able to provide me with a fix for my scenario? My code is below.
Private Sub btnUPrint_Click(sender As Object, e As EventArgs) Handles btnUPrint.Click
'ctrlPrintDoc.Print()
Dim HistCSV As String
HistCSV = My.Computer.Registry.GetValue(gRegKeyName, "HistoryFileLocation", "")
Dim out As System.IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(HistCSV, True)
out.WriteLine(txtUChkNum.Text & "," & DateTimePicker1.Text & "," & txtUAmt.Text & "," & txtUPayTo.Text & "," & txtURef.Text)
out.Close()
End Sub
Kindly advise,
Regards,
N
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Hi nobushi;
If you can maybe using a different delimiter in place of the comma something like a vbTab or the pipe symbol.
Or in the below code to use the pipe symbol change all occurrences of vbTab to "|".
If you can maybe using a different delimiter in place of the comma something like a vbTab or the pipe symbol.
Or in the below code to use the pipe symbol change all occurrences of vbTab to "|".
out.WriteLine(txtUChkNum.Text & vbTab & DateTimePicker1.Text & vbTab & txtUAmt.Text & vbTab & ControlChars.Quote & txtUPayTo.Text & ControlChars.Quote & vbTab & ControlChars.Quote & txtURef.Text & ControlChars.Quote)
*No Points*
Furthering what Éric Moreau states, this is known as "text qualifying" your fields. It's a standard practice.
Furthering what Éric Moreau states, this is known as "text qualifying" your fields. It's a standard practice.
There are a few options that programmers will use in order to accomplish your goal:
1. Sanitize the user input
2. Use a different delimiter.
3. Use XML.
Each have their own pro's and con's:
My preferred method is sanitizing the user input:
Quite simply, this is accomplished by either:
1. Encoding the user data
-OR-
2. Replacing the delimiter in the user input with another delimiter.
The method that is most often used and is safer is to encode the data for future decoding.
Consider the following (bearing in mind that this is just one of many examples):
I say safer because if you introduce another delimiter you will have to check for the existence of that delimiter and so forth and so on.
The other methods
-saige-
1. Sanitize the user input
2. Use a different delimiter.
3. Use XML.
Each have their own pro's and con's:
My preferred method is sanitizing the user input:
Quite simply, this is accomplished by either:
1. Encoding the user data
-OR-
2. Replacing the delimiter in the user input with another delimiter.
The method that is most often used and is safer is to encode the data for future decoding.
Consider the following (bearing in mind that this is just one of many examples):
Imports System.Text
Module Module1
Public sentence As List(Of String) = New List(Of String) From _
{ _
"The brown cow", _
"walked along", _
"the winding, dangerous", _
"and shortest path", _
"to reach her", _
"final destination." _
}
Sub Main()
Dim storedString As String = String.Empty
' First lets encode and store the strings
Console.WriteLine("First lets encode and store the strings:")
Dim firstItem As Boolean = True
For Each item In sentence
Dim encoded As Byte() = Encoding.UTF8.GetBytes(item)
Console.WriteLine("{0} has been encoded as {1}", item, Convert.ToBase64String(encoded))
If firstItem Then
storedString = String.Format("{0}", Convert.ToBase64String(encoded))
firstItem = False
Else
storedString += String.Format(",{0}", Convert.ToBase64String(encoded))
End If
Next
' Here is what our stored value looks like
Console.WriteLine()
Console.WriteLine("Here is what our stored value looks like:")
Console.WriteLine(storedString)
Console.WriteLine()
'Now lets split and decode the stored string
Console.WriteLine("Now lets split and decode the stored string:")
For Each item In storedString.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
Dim decoded = Convert.FromBase64String(item)
Console.WriteLine(Encoding.UTF8.GetString(decoded))
Next
Console.ReadLine()
End Sub
End Module
Produces the following output -I say safer because if you introduce another delimiter you will have to check for the existence of that delimiter and so forth and so on.
The other methods
-saige-
And when applied to your specific data:
Imports System.Text
Module Module1
Public sentence As List(Of String) = New List(Of String) From _
{ _
"465262", _
"3/4/2015", _
"4,542.22", _
"John Doe", _
"Server Maintenance, Installation and configuration" _
}
Sub Main()
Dim storedString As String = String.Empty
' First lets encode and store the strings
Console.WriteLine("First lets encode and store the strings:")
Dim firstItem As Boolean = True
For Each item In sentence
Dim encoded As Byte() = Encoding.UTF8.GetBytes(item)
Console.WriteLine("{0} has been encoded as {1}", item, Convert.ToBase64String(encoded))
If firstItem Then
storedString = String.Format("{0}", Convert.ToBase64String(encoded))
firstItem = False
Else
storedString += String.Format(",{0}", Convert.ToBase64String(encoded))
End If
Next
' Here is what our stored value looks like
Console.WriteLine()
Console.WriteLine("Here is what our stored value looks like:")
Console.WriteLine(storedString)
Console.WriteLine()
'Now lets split and decode the stored string
Console.WriteLine("Now lets split and decode the stored string:")
For Each item In storedString.Split(New String() {","}, StringSplitOptions.RemoveEmptyEntries)
Dim decoded = Convert.FromBase64String(item)
Console.WriteLine(Encoding.UTF8.GetString(decoded))
Next
Console.ReadLine()
End Sub
End Module
Produces the following output --saige-
ASKER
Thank you all for your replies however I cannot get them to work.
Eric, when I try your solution I am getting the below results:
When it should look like this in the CSV:
Fernando, when i try your approach (using both the pipe and the vbtab in different instances) I am getting this in the CSV:
When it should look like this in the CSV:
Peter, I am not sure how to insert your example in to my code so I was not able to test your solution.
Essentially what I am after is what the user inputs in the textboxes on the windows form will appear in the CSV file, this CSV file is a standard CSV file which I will read from on another windows form for the user to see the contents "neatly" without having the user to open the CSV file via explorer to view it manually.
Also this csv file will eventually be imported in to another in house system, so it needs to remain as a standard comma delimited (CSV) file.
Apologies for my lack of experience with this.
Kindly advise.
Regards,
N
Eric, when I try your solution I am getting the below results:
When it should look like this in the CSV:
Fernando, when i try your approach (using both the pipe and the vbtab in different instances) I am getting this in the CSV:
When it should look like this in the CSV:
Peter, I am not sure how to insert your example in to my code so I was not able to test your solution.
Essentially what I am after is what the user inputs in the textboxes on the windows form will appear in the CSV file, this CSV file is a standard CSV file which I will read from on another windows form for the user to see the contents "neatly" without having the user to open the CSV file via explorer to view it manually.
Also this csv file will eventually be imported in to another in house system, so it needs to remain as a standard comma delimited (CSV) file.
Apologies for my lack of experience with this.
Kindly advise.
Regards,
N
ASKER
it_saige, I did not see your posting upon writing my response. I will review and advise.
Thank you.
Thank you.
it looks like you have an unmatched double quote (you opened one that you haven't closed).
Can you show me your code?
Can you show me your code?
@nobushi - Understand that my example solution is not going to be perfect for every situation (especially when you need to be able to open the file, directly, and read the contents in human readable format).
If you do not need to open the file directly and can decode it instead then it should work as proposed (although, my preference would be to use a well-defined type; e.g. class, and serialize the object instance).
-saige-
If you do not need to open the file directly and can decode it instead then it should work as proposed (although, my preference would be to use a well-defined type; e.g. class, and serialize the object instance).
-saige-
have you seen my last comment? Ensure that you really have 4 ControlChars.Quote. Can you show your latest code?
*No Points*
@Eric - I was just working on an example that utilizes your proposed solution to present to nobushi. It would probably be more inline with his/her ultimate goal.
I'll post it shortly.
-saige-
@Eric - I was just working on an example that utilizes your proposed solution to present to nobushi. It would probably be more inline with his/her ultimate goal.
I'll post it shortly.
-saige-
ASKER
@Eric Thank you, I see what I did wrong, corrected it and now it's working as it should.
@it_saige Thank you also for your idea, I had no idea something like this could be accomplished, but then again I am still learning. I may look in to your code for another application I have in mind, as it is a very interesting idea of encoding and decoding the values, especially when the values are sensitive to the eyes.
Kind Regards,
N
@it_saige Thank you also for your idea, I had no idea something like this could be accomplished, but then again I am still learning. I may look in to your code for another application I have in mind, as it is a very interesting idea of encoding and decoding the values, especially when the values are sensitive to the eyes.
Kind Regards,
N
@nobushi - Here is a quick and dirty example using Eric's recommendation and your dataset.
-saige-
Imports System.Text
Imports System.IO
Module Module1
Public DataSets As List(Of List(Of String)) = New List(Of List(Of String)) From _
{ _
New List(Of String) From {"555666", "3/11/2015", "122.32", "Peter Smith", "Server Maintenance"}, _
New List(Of String) From {"555667", "3/4/2015", "1,123.22", "Simon Smith", "Desktop Configuration"}, _
New List(Of String) From {"555668", "10/13/2015", "45,225.55", "Tools for Hire, Company Ltd", "Blades Installation, configuration and setup"}, _
New List(Of String) From {"555669", "3/4/2015", "45,121.22", "John Doe", "Perry Como and Steve Jobs", "Electrician work, installation and setup"}, _
New List(Of String) From {"555670", "3/13/2015", "54,545.02", "Bill Gates", "Microsoft software, tablets and tools"}, _
New List(Of String) From {"555671", "3/15/2015", "11,233.25", "Carlos Teller", "Peter Parker", "History Lessons, work and tools"} _
}
Sub Main()
For Each DataSet In DataSets
EricsMethod(DataSet)
Next
Console.WriteLine("Finished")
Console.ReadLine()
End Sub
Sub EricsMethod(ByVal data As IEnumerable(Of String))
Dim firstItem As Boolean = True
Dim HistCSV As String = "Test.csv"
Dim stored As String = String.Empty
Dim out As StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(HistCSV, True)
For Each item In data
If item.Contains(",") Then item = String.Format("{0}{1}{0}", ControlChars.Quote, item)
If firstItem Then
stored = String.Format("{0}", item)
firstItem = False
Else
stored += String.Format(",{0}", item)
End If
Next
out.WriteLine(stored)
out.Close()
End Sub
End Module
Which gives me this CSV ouput (in Excel) -And this CSV text file --saige-
ASKER
@saige Thank you for your example. Tell me something, using your method of decoding/encoding the values, is there a decrease in speed (writing to the file), when compared to doing it like I am now using Eric's method? Or is it about the same?
It depends on the level of complexity of the data and the encoding methods used. Understand that each method call does take time to produce a result.
Ultimately, with the dataset we are dealing with, here and now, the complete process just takes a matter of milliseconds. But as your dataset becomes more and more complex encoding will take longer than just writing clear text.
Consider the following -
Ultimately, with the dataset we are dealing with, here and now, the complete process just takes a matter of milliseconds. But as your dataset becomes more and more complex encoding will take longer than just writing clear text.
Consider the following -
Imports System.Text
Imports System.IO
Module Module1
Public DataSets As List(Of List(Of String)) = New List(Of List(Of String)) From _
{ _
New List(Of String) From {"555666", "3/11/2015", "122.32", "Peter Smith", "Server Maintenance"}, _
New List(Of String) From {"555667", "3/4/2015", "1,123.22", "Simon Smith", "Desktop Configuration"}, _
New List(Of String) From {"555668", "10/13/2015", "45,225.55", "Tools for Hire, Company Ltd", "Blades Installation, configuration and setup"}, _
New List(Of String) From {"555669", "3/4/2015", "45,121.22", "John Doe", "Perry Como and Steve Jobs", "Electrician work, installation and setup"}, _
New List(Of String) From {"555670", "3/13/2015", "54,545.02", "Bill Gates", "Microsoft software, tablets and tools"}, _
New List(Of String) From {"555671", "3/15/2015", "11,233.25", "Carlos Teller", "Peter Parker", "History Lessons, work and tools"} _
}
Sub Main()
Dim watch As New Stopwatch()
Console.WriteLine("Starting Stopwatch for Generating Encoded File")
watch.Start()
For Each DataSet In DataSets
GenerateEncodedFile(DataSet)
Next
watch.Stop()
Console.WriteLine("Finished; Generating Encoded File took - {0} ms", watch.ElapsedMilliseconds)
watch.Reset()
Console.WriteLine()
Console.WriteLine("Starting Stopwatch for Generating Unencoded File")
watch.Start()
For Each DataSet In DataSets
GenerateUnencodedFile(DataSet)
Next
watch.Stop()
Console.WriteLine("Finished; Generating Unencoded File took - {0} ms", watch.ElapsedMilliseconds)
Console.ReadLine()
End Sub
Sub GenerateEncodedFile(ByVal data As IEnumerable(Of String))
Dim firstItem As Boolean = True
Dim HistCSV As String = "EncodedTest.csv"
Dim stored As String = String.Empty
Dim out As StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(HistCSV, True)
For Each item In data
If firstItem Then
stored = String.Format("{0}", Convert.ToBase64String(Encoding.UTF8.GetBytes(item)))
firstItem = False
Else
stored += String.Format(",{0}", Convert.ToBase64String(Encoding.UTF8.GetBytes(item)))
End If
Next
out.WriteLine(stored)
out.Close()
End Sub
Sub GenerateUnencodedFile(ByVal data As IEnumerable(Of String))
Dim firstItem As Boolean = True
Dim HistCSV As String = "UnEncodedTest.csv"
Dim stored As String = String.Empty
Dim out As StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(HistCSV, True)
For Each item In data
If item.Contains(",") Then item = String.Format("{0}{1}{0}", ControlChars.Quote, item)
If firstItem Then
stored = String.Format("{0}", item)
firstItem = False
Else
stored += String.Format(",{0}", item)
End If
Next
out.WriteLine(stored)
out.Close()
End Sub
End Module
Produces the following results --saige-
ASKER
Understood. Thank you.
txtURef.Text = txtURef.Text.Replace(",", " ")