Write to CSV issue
SPONSORED LINKS
I have the following code:
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 ProjectNew Changes 20090710JnJCustomerCSVExporterCustomerCSVConfigurationCustomer.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 ProjectNew Changes 20090710JnJCustomerCSVExporterCustomerCSVConfigurationCustomer.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??
Read the original here:
Write to CSV issue