Account For Comma In Export To CSV

H-SC
H-SC used Ask the Experts™
on
I have the following code that saves a dataset to a csv file.  The problem that I am having, is...
When there is a comma within the text in my data, it throws everything off within the ending result of the csv file.  How can I account for the comma within the data to tell the export to ignore?

Dim iRow As Integer
        Dim intCount As Integer = 0
        Dim iCol As Integer
        Dim oRite As System.IO.StreamWriter
        Dim strfilename As String
        Dim defaultExportLocation As String = "C:\"
        SaveFileDialog1.Title = "Save File As "
        SaveFileDialog1.InitialDirectory = defaultExportLocation
        Me.Cursor = Cursors.WaitCursor
        Try
            With SaveFileDialog1
                .InitialDirectory = Environ("UserProfile")
                .Filter = "csv files|*.csv"
                .FileName = ((("My_File_" + Now.ToString("MM_dd_yyyy_hhmmss"))))
                If .ShowDialog = Windows.Forms.DialogResult.OK Then
                    strFilename = .FileName
                    intCount = 0
                    Application.DoEvents()
                    Dim ds As DataSet
                    Dim strToWrite As String
                    ds = Final
                    oRite = IO.File.CreateText(.FileName)
                    For Each column In ds.Tables("my_file").Columns
                        strToWrite = strToWrite & column.ColumnName & ","
                    Next
                    oRite.WriteLine(strToWrite.Substring(0, strToWrite.Length - 1))
                    For iRow = 0 To ds.Tables("my_file").Rows.Count - 1
                        For iCol = 0 To ds.Tables("my_file").Columns.Count - 1
                            If iCol > 0 Then oRite.Write(",")
                            oRite.Write(ds.Tables("my_file")(iRow).Item(iCol))
                        Next
                        oRite.WriteLine()
                    Next
                    oRite.Close()
                    Me.Cursor = Cursors.Default
                End If
            End With
        Catch ex As Exception
        End Try
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014
Commented:
Change the following loop
For iCol = 0 To ds.Tables("my_file").Columns.Count - 1
                            If iCol > 0 Then oRite.Write(",")
                            oRite.Write(ds.Tables("my_file")(iRow).Item(iCol))
                        Next

to this
For iCol = 0 To ds.Tables("my_file").Columns.Count - 1
                            If iCol > 0 Then oRite.Write(",")
                            oRite.Write(chr(34) & ds.Tables("my_file")(iRow).Item(iCol) & chr(34))
                        Next
Most Valuable Expert 2012
Top Expert 2014

Commented:
This will enclose your values in " ". Otherwise change the , separater to something else such as a ;
Fernando SotoRetired
Distinguished Expert 2017

Commented:
Hi H-SC,

Well the first thing that comes to mind is to use a different separator value such as a Tab character. Another option is to place value in double quote characters that contain a comma in them.

What type of program will be reading in this CSV file?

Fernando
If there is going to be commas in the text of the fields of the CSV file, you will need to put double quotes (") around the text.  For example:
Los Angeles, CA, 90000 - is three different fields since the comma is a delimiter
"Los Angeles, CA",900000 - is two different fields.
 

Author

Commented:
CodeCruiser,
You are good.  That is perfect!  

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial