Write to CSV issue

[daniel50096230]

I have the following code:

Private Sub GenerateCustomerCSV()
        Dim objDB As New clsDB


        Dim CSVText As String
        Dim filename As String

        filename = "Customer.txt"
        CSVText = GeneratePOCSVText()

        If CSVText = "ERROR" Then
            MsgBox("CSV File cannot be downloaded", MsgBoxStyle.Critical, "Download CSV File")
            Exit Sub
        End If
        Dim csvFile As String = My.Application.Info.DirectoryPath & "\Test.csv"

        Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, False)
        ' outFile.WriteLine("Part Id, Part Number, Description, Price")
        outFile.WriteLine(CSVText)
        outFile.Close()
        Console.WriteLine(My.Computer.FileSystem.ReadAllText(csvFile))


    End Sub
   
    Private Function GeneratePOCSVText() As String
        Dim lstrText As String = String.Empty
        Dim Headercolumn As Integer


        Dim HeaderRow As Integer
       
        If GetHeaderDataset() = False Then Return "ERROR"

        Dim a As Integer = HeaderDS.Tables(0).Rows.Count - 1

        
        For HeaderRow = 0 To HeaderDS.Tables(0).Rows.Count - 1
            
            For Headercolumn = 0 To HeaderDS.Tables(0).Columns.Count - 1
                              
                lstrText &= AddSpace(FormatField(HeaderDS.Tables(0).Columns(Headercolumn).ToString, HeaderDS.Tables(0).Rows(HeaderRow).Item(Headercolumn).ToString), GetFieldLength(HeaderDS.Tables(0).Columns(Headercolumn).ToString))
            Next


        Next


        Return lstrText
    End Function

    Private Function FormatField(ByVal FieldName As String, ByVal FieldValue As String) As String
        Dim i As Integer
        Dim CSVConfigXML As New XmlDocument

        CSVConfigXML.Load("C:\JnJ Project\New Changes 20090710\JnJCustomerCSVExporter\Customer\CSVConfigurationCustomer.xml")
        With CSVConfigXML.SelectSingleNode("CSV/Customer")
            For i = 0 To .ChildNodes.Count - 1
                If UCase(.ChildNodes(i).ChildNodes(0).InnerText()) = UCase(FieldName) Then
                    Select Case UCase(.ChildNodes(i).ChildNodes(2).InnerText())
                        Case "TEXT"
                            Return FieldValue

                        Case "DATE"
                            If FieldValue <> "" Then
                                Return FieldValue

                                'Return Format(Date.Parse(FieldValue), .ChildNodes(i).ChildNodes(3).InnerText())
                            Else
                                Return FieldValue
                            End If

                        Case "BIT"

                            If UCase(FieldValue) = "TRUE" Then
                                Return "1"
                            Else
                                Return "0"
                            End If

                        Case "CURRENCY"
                            If FieldValue <> "" Then
                                Return Format(Double.Parse(FieldValue), .ChildNodes(i).ChildNodes(3).InnerText())
                            Else
                                Return FieldValue
                            End If

                        Case Else
                            Return FieldValue
                    End Select
                End If
            Next
        End With

    End Function

    Private Function GetFieldLength(ByVal FieldName As String) As Integer
        Dim i As Integer
        Dim CSVConfigXML As New XmlDocument

        CSVConfigXML.Load("C:\JnJ Project\New Changes 20090710\JnJCustomerCSVExporter\Customer\CSVConfigurationCustomer.xml")
        With CSVConfigXML.SelectSingleNode("CSV/Customer")
            For i = 0 To .ChildNodes.Count - 1
                If UCase(.ChildNodes(i).ChildNodes(0).InnerText()) = UCase(FieldName) Then
                    Return CType(.ChildNodes(i).ChildNodes(1).InnerText, Integer)
                End If
            Next
        End With

    End Function

    Private Function AddSpace(ByVal Value As String, ByVal lenght As Integer) As String

        While Value.Length <= lenght - 1
            Value = Value & " "
        End While


        Value &= ","

        Return Value

    End Function

    Private Function GetHeaderDataset() As Boolean
        Dim objDB As New clsDB
        Try
            Dim sql As String = [String].Empty

            sql = "SELECT TOP 1 C.Dist_ID, C.Cust_Code, C.Cust_Name1, CC.Outlet_Type1, CC.Outlet_Type2, CC.Outlet_Type3, T.Town, C.Cust_Name2, C.Address1, C.Address2,C.PostCode, C.Contact, C.PhoneNo, "
            sql &= "C.Region, C.ProvinceCity, C.Active_Ident, C.Cust_Group, C.Cust_Type, C.Cust_Field1, C.Cust_Field2 "
            sql &= "FROM Trn_Customer_Details C, Tb_Customer CC, Tb_PostCode_Town T "
            sql &= "WHERE C.Cust_ID=CC.Cust_ID "
            sql &= "Order By C.Cust_ID ASC ;"
            objDB.OpenDataSet(HeaderDS, sql)

        Catch e As System.Exception
            objDB = Nothing
            Return False
        Finally
            objDB = Nothing
            GetHeaderDataset = True
        End Try
    End Function

I can successfully retrieve the data into dataset, but the problem is some of the data contain comma. For example, for Address1, the data is "52000, London". So the code will separate 52000 and London to separate column in the csv. How can I change the code in order it will ignore the comma and put 52000,London into the same column in csv??

[Alsvha]

Do you have the text qualified with the quotes in the output file?

[daniel50096230]

what do you means by text qualified?Sorry that I am still new to csv.

[dglienna]

Once you get this "52000, London"

Check for the presence of a comma, or whatever breaks things

Replace each character with "" (nothing) until the data is good

[daniel50096230]

I know, I had use Replace to replace "," with " ". But when I generate the csv, I would like to insert complete statement "52000, London" to the csv.

[Alsvha]

Originally Posted by daniel50096230

what do you means by text qualified?Sorry that I am still new to csv.

The way a CSV normally works is using "text qualifiers" - usually the quote (") character to surround the text fields within the file. This is because the separator/delimiter often can be included in the textual values - as you've noticed with your comma.

So a CSV file with the content you suggest would have the value
5200, London
wrapped in quotes like this
"5200, London".
The the software reading the CSV will know that because your text is qualified with " that the , within the " boundaries should not be read as a separator/delimiter character. So for example you could have

id, location, name
1, "5200, London", "Smith, John"

This is a perfectly valid CSV file.

Then reading that file into software, you'd read the fields as
1
5200, London
Smith, John
because the text fields are qualified (") and thus the , inside the fields should not be split upon.

Therefore, the real question to your problem is, do you have problems writing the CSV or reading the CSV you've written?
Because if you have problems writing the file, then try to add " as a text qualifier around your fields, or perhaps your method of writing should be changed.

[daniel50096230]

Maybe you are right, but the problem is I retrieve from database, so I will store the "52000,London" to a string, so how can I use "" in my string???

[Alsvha]

In VB.NET you escape a " by adding another ", so
"""5200, London"""
should do the trick I would think.

[daniel50096230]

You means if I assigned the "52000,London" to a string called strLocation, then when write to csv, I should write the code as """ strLocation """?

[daniel50096230]

I had change the code to the following:

Private Function GeneratePOCSVText() As String
        Dim lstrText As String = String.Empty
        Dim Headercolumn As Integer

        Dim b As String
        Dim c As String
        Dim d As String
        Dim e As String
        Dim f As String
        Dim g As String
        Dim h As String
        Dim i As String
        Dim j As String
        Dim k As String
        Dim l As String
        Dim m As String
        Dim n As String
        Dim o As String

        Dim p As String
        Dim q As String
        Dim r As String
        Dim s As String
        Dim t As String
        Dim u As String
        Dim v As String
        Dim w As String

        Dim HeaderRow As Integer
       
        If GetHeaderDataset() = False Then Return "ERROR"

        Dim a As Integer = HeaderDS.Tables(0).Rows.Count - 1

        Dim csvFile As String = My.Application.Info.DirectoryPath & "\Test.csv"

        Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(csvFile, False)
        ' outFile.WriteLine("Part Id, Part Number, Description, Price")

       
        For HeaderRow = 0 To HeaderDS.Tables(0).Rows.Count - 1
          
            ' For Headercolumn = 0 To HeaderDS.Tables(0).Columns.Count - 1
            b = HeaderDS.Tables(0).Rows(HeaderRow).Item(0).ToString()
            c = HeaderDS.Tables(0).Rows(HeaderRow).Item(1).ToString()
            d = HeaderDS.Tables(0).Rows(HeaderRow).Item(2).ToString()
            e = HeaderDS.Tables(0).Rows(HeaderRow).Item(3).ToString()
            f = HeaderDS.Tables(0).Rows(HeaderRow).Item(4).ToString()
            g = HeaderDS.Tables(0).Rows(HeaderRow).Item(5).ToString()
            h = HeaderDS.Tables(0).Rows(HeaderRow).Item(6).ToString()
            i = HeaderDS.Tables(0).Rows(HeaderRow).Item(7).ToString()
            j = HeaderDS.Tables(0).Rows(HeaderRow).Item(8).ToString()
            k = HeaderDS.Tables(0).Rows(HeaderRow).Item(9).ToString()
            l = HeaderDS.Tables(0).Rows(HeaderRow).Item(10).ToString()
            m = HeaderDS.Tables(0).Rows(HeaderRow).Item(11).ToString()
            n = HeaderDS.Tables(0).Rows(HeaderRow).Item(12).ToString()
            o = HeaderDS.Tables(0).Rows(HeaderRow).Item(13).ToString()
            p = HeaderDS.Tables(0).Rows(HeaderRow).Item(14).ToString()
            q = HeaderDS.Tables(0).Rows(HeaderRow).Item(15).ToString()
            r = HeaderDS.Tables(0).Rows(HeaderRow).Item(16).ToString()
            s = HeaderDS.Tables(0).Rows(HeaderRow).Item(17).ToString()
            t = HeaderDS.Tables(0).Rows(HeaderRow).Item(18).ToString()
            u = HeaderDS.Tables(0).Rows(HeaderRow).Item(19).ToString()

            'lstrText &= AddSpace(FormatField(HeaderDS.Tables(0).Columns(Headercolumn).ToString, HeaderDS.Tables(0).Rows(HeaderRow).Item(Headercolumn).ToString), GetFieldLength(HeaderDS.Tables(0).Columns(Headercolumn).ToString))
            'lstrText &= (b & "," & c & "," & d & "," & e & "," & f & "," & g & "," & h & "," & i & "," & j & "," & k & "," & l & "," & m & "," & n & "," & p & "," & q & "," & r & "," & s & "," & t & "," & u)
            lstrText &= (b & "," & c & "," & d & "," & e & "," & f & "," & g & "," & h & "," & i & "," & j & "," & k & "," & l & "," & m & "," & n & "," & "p" & "," & q & "," & r & "," & s & "," & t & "," & u)
            'outFile.WriteLine(b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, q, r, s, t)
            'Next

            lstrText &= System.Environment.NewLine

        Next
        outFile.WriteLine(lstrText)

        outFile.Close()
        Console.WriteLine(My.Computer.FileSystem.ReadAllText(csvFile))
        Return lstrText
    End Function

The string m will store the "52000,London", so when I write to csv , is it I should write """ m """ rather & m &?

[Alsvha]

Originally Posted by daniel50096230

You means if I assigned the "52000,London" to a string called strLocation, then when write to csv, I should write the code as """ strLocation """?

Then it should be:
dim yourString as String = """" & strLocation & """"

The first " indicates it is a string, the next " escapes the third " and the fourth " ends the string.
So """" & strLocation & """" results in "52000, London" when printed out.

Originally Posted by daniel50096230

The string m will store the "52000,London", so when I write to csv , is it I should write """ m """ rather & m &?

m = """" & HeaderDS.Tables(0).Rows(HeaderRow).Item(11).ToString() & """"



It is a silly thing that VB.NET didn't incorporate proper escape sequence, but well - what can you do

[daniel50096230]

Thanks, Alvsha...I really appreciate your help....This problem cause me very pressure...But you had help me solve it....

Thanks..