CSV and Text Delimiter Befuddlement

[lordbaddkitty]

I'm working with VB.Net.
I have imported some data into the Data Set.
I have four columns of data.

[Column 1]This is a test[/Column 1]
[Column 2]This is a comma, Test.[/Column 2]
[Column 3]This is a double quote " test.[/Column 3]
[Column 4]This is a comma, quote " test.[/Column 4]

* Ignore the Pseudo-Tags, they're just to show column start/end:

I want to export this Data Set Table to a CSV.
I want to maintain cell/column integrity.
I am using Double Quotes around my "cell contents" to delineate text.
This works just fine.
When I open the file with notepad, it looks like this:

"This is a test","This is a comma, Test.","This is a double quote " test.","This is a comma, quote " test."

No problem there. Looks good.

HOWEVER:
When I open the file with Excel, I get this:

[Column 1]This is a test[/Column 1]
[Column 2]This is a comma, Test.[/Column 2]
[Column 3]This is a double quote  test."[/Column 3]
[Column 4]This is a comma, quote  test."[/Column 4]

Note that the Double quotes in Column three and four have disappeared, and the ending double quotes have been left visible, rather than being ignored as part of a delineater string of [","]...

So, how would you handle this?

btw: I can't export to Excel, as the user may not have excel installed. It must be to a CSV.

[lordbaddkitty]

Here's the solution that worked for me:

Imports:

Imports System.IO

Declarations:

Dim DoubleQuotes As String = _
    Char.ConvertFromUtf32(34)

Dim TwoDoubleQuotes As String = _
    Char.ConvertFromUtf32(34) & Char.ConvertFromUtf32(34)

Dim AComma As String = _
    Char.ConvertFromUtf32(44)

Dim ACommaInQuotes As String = _
    Char.ConvertFromUtf32(34) & Char.ConvertFromUtf32(44) & Char.ConvertFromUtf32(34)

Format text for use in CSV:

Public Function EncapsulateCommasAndDoubleQuotes(ByVal sInputString As String) As String

        If sInputString.IndexOf(Char.ConvertFromUtf32(34), 0) > -1 Or sInputString.IndexOf(",", 0) Then

          Dim NewString As String = ""

            ' If the String has Double Quotes:
            If sInputString.Contains(DoubleQuotes) Then

                ' First, fix the double quotes by replacing ["] with [""]
            Dim TweakedDoubleQuotes As String = Replace(sInputString, DoubleQuotes, TwoDoubleQuotes)

                NewString = DoubleQuotes + TweakedDoubleQuotes + DoubleQuotes

            Else 'If the String does NOT have any double quotes:

                If sInputString.Contains(AComma) Then
                    NewString = DoubleQuotes + sInputString + DoubleQuotes
                Else
                    NewString = sInputString
                End If

            End If

            ' Trim any lameness out of it:
            Trim(NewString)

            'And Return the cleaned String
            Return NewString
        Else
            ' Or, return the original string
            Return sInputString
        End If

    End Function

Use formatted text to build CSV:

Public Function WriteDataSetToCSV(ByVal SourceDataSet As DataSet, _
                                      ByVal SourceTable As System.Data.DataTable, _
                                      ByVal FileName As String, _
                                      Optional ByVal UseUniqueFilename As Boolean = False) _
                                      As Boolean

        'Declarations
        Dim outFile As IO.StreamWriter
        Dim hdStr As String = ""

        'If UseUniqueFilename is True then Add the date and time to the filename:
        If UseUniqueFilename = True Then
            FileRename = FileName + "-" + Today.Month.ToString + "-" + _
            Today.Day.ToString + "-" + Today.Year.ToString + "-" + _
            Now.Hour.ToString + Now.Minute.ToString + Now.Second.ToString + ".csv"
        Else
            FileRename = FileName + ".csv"
        End If

        'Build the output stream:
        Try
            outFile = My.Computer.FileSystem.OpenTextFileWriter(FileRename, False)
        Catch ex As IOException
            MsgBox(ex.Message)
            Return False
            Exit Function
        End Try

        'Build the headers (except the last one) and add a [,] Comma after each one:
        For x = 0 To SourceDataSet.Tables(0).Columns.Count - 2
            hdStr = hdStr & SourceDataSet.Tables(0).Columns(x).ColumnName & AComma
        Next

        'Add the last header without the [,] Comma after it
        hdStr = _
        hdStr & SourceDataSet.Tables(0).Columns(SourceDataSet.Tables(0).Columns.Count - 1).ColumnName

        'Send the Header Row to the CSV:
        outFile.WriteLine(hdStr)

        'Format each data row for the CSV
        For x = 0 To SourceDataSet.Tables(0).Rows.Count - 1
            Dim rowStr As String = ""

            For y = 0 To SourceDataSet.Tables(0).Columns.Count - 1

                ' Encapsulate text for use in a CSV
                Dim CleanedString As String = _
                EncapsulateCommasAndDoubleQuotes(SourceDataSet.Tables(0).Rows(x).Item(y))

                ' Then add the cleaned string to the rowStr 'array' :
                If y = SourceDataSet.Tables(0).Columns.Count - 1 Then
                    rowStr = rowStr + CleanedString  'Do not add a [,] Comma after the last item.
                Else
                    rowStr = rowStr + CleanedString & AComma 'Add a [,] Comma after other items.
                End If

            Next y

            'Send the Data Row to the CSV:
            outFile.WriteLine(rowStr)

        Next x

        ' Close (duh)
        outFile.Close()

        Return True

    End Function

Glad I could help myself...