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:

screenshot1
It’s writing like the below instead:

screenshot2
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

Open in new window


Kindly advise,

Regards,
N
LVL 1
KevinAsked:
Who is Participating?
 
Éric MoreauSenior .Net ConsultantCommented:
add double quotes around your value

out.WriteLine(txtUChkNum.Text & "," & DateTimePicker1.Text & "," & txtUAmt.Text & "," & ControlChars.Quote & txtUPayTo.Text & ControlChars.Quote & "," &  ControlChars.Quote & txtURef.Text & ControlChars.Quote)

Open in new window

0
 
Peter HutchisonSenior Network Systems SpecialistCommented:
You could apply some field format control and replace any commas found with another character such as a space before writing them out to a file.

txtURef.Text = txtURef.Text.Replace(",", " ")
0
 
Fernando SotoRetiredCommented:
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 "|".

out.WriteLine(txtUChkNum.Text & vbTab & DateTimePicker1.Text & vbTab & txtUAmt.Text & vbTab & ControlChars.Quote & txtUPayTo.Text & ControlChars.Quote & vbTab &  ControlChars.Quote & txtURef.Text & ControlChars.Quote)

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
käµfm³d 👽Commented:
*No Points*

Furthering what Éric Moreau states, this is known as "text qualifying" your fields. It's a standard practice.
0
 
it_saigeDeveloperCommented:
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):
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

Open in new window

Produces the following output -Capture.JPG
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-
0
 
it_saigeDeveloperCommented:
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

Open in new window

Produces the following output -Capture.JPG-saige-
0
 
KevinAuthor Commented:
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:

eric1
When it should look like this in the CSV:

eric2
Fernando, when i try your approach (using both the pipe and the vbtab in different instances) I am getting this in the CSV:

fernando1
When it should look like this in the CSV:

fernando2
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
0
 
KevinAuthor Commented:
it_saige, I did not see your posting upon writing my response. I will review and advise.

Thank you.
0
 
Éric MoreauSenior .Net ConsultantCommented:
it looks like you have an unmatched double quote (you opened one that you haven't closed).

Can you show me your code?
0
 
it_saigeDeveloperCommented:
@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-
0
 
Éric MoreauSenior .Net ConsultantCommented:
have you seen my last comment? Ensure that you really have 4 ControlChars.Quote. Can you show your latest code?
0
 
it_saigeDeveloperCommented:
*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-
0
 
KevinAuthor Commented:
@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
0
 
it_saigeDeveloperCommented:
@nobushi - Here is a quick and dirty example using Eric's recommendation and your dataset.
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

Open in new window

Which gives me this CSV ouput (in Excel) -Capture.JPGAnd this CSV text file -Capture.JPG
-saige-
0
 
KevinAuthor Commented:
@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?
0
 
it_saigeDeveloperCommented:
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 -
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

Open in new window

Produces the following results -Capture.JPG-saige-
0
 
KevinAuthor Commented:
Understood. Thank you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.