Link to home
Start Free TrialLog in
Avatar of Kevin
KevinFlag for United States of America

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:

User generated image
It’s writing like the below instead:

User generated image
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
ASKER CERTIFIED SOLUTION
Avatar of Éric Moreau
Éric Moreau
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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(",", " ")
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

*No Points*

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):
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 -User generated image
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

Open in new window

Produces the following output -User generated image-saige-
Avatar of Kevin

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:

User generated image
When it should look like this in the CSV:

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

User generated image
When it should look like this in the CSV:

User generated image
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
Avatar of Kevin

ASKER

it_saige, I did not see your posting upon writing my response. I will review and advise.

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?
@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-
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-
Avatar of Kevin

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
@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) -User generated imageAnd this CSV text file -User generated image
-saige-
Avatar of Kevin

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 -
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 -User generated image-saige-
Avatar of Kevin

ASKER

Understood. Thank you.