Scripts and Programming

        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)