Trying to make XML files that has mulitple line items. RRS feed

  • Question

  • It seems each line in my excel file that I copy and paste into my app makes a single file for each document transfer. Sometimes an Inventory transfer might have one line item and other times multiple.

    The Excel is in this link:

    I would copy the column in the excel and paste to according textboxes in the app I made. Then click the add button and finally click Process.

    My code

    Public Class Form1
        Dim SQLString, SQLString2 As String
        Dim entriesDOCNUM, dates, entriesStoreNum, entriesStorName, quantities, UPCS, description As String
        Dim counters, counters2, counters3 As Integer
        Dim whereclaus As String
        Dim fileNumber, startFileNumber As Integer
        Dim inDirectory, outDirectory As String
        Dim CSVtext As String = ""
        Dim browserDirectory As New FolderBrowserDialog
        Dim n As Integer = "0"
        Dim lineData(), lineData2(), lineData3(), lineData4(), lineData5(), lineData6(), lineDate() As String
        Dim biggestCount As Integer
        Dim storeNameChange As Boolean = False
        Dim storeName1, storeName2 As String
        Dim storeNum1, storeNum2 As String
        Dim POnumber As Integer
        Private Sub BrowseBtn_Click(sender As Object, e As EventArgs) Handles BrowseBtn.Click
            If browserDirectory.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
                    DirectoryTxtBox.Text = browserDirectory.SelectedPath.ToString
                    DirectoryTxtBox.Text = DirectoryTxtBox.Text
                    inDirectory = DirectoryTxtBox.Text
                Catch ex As Exception
                End Try
                Exit Sub
            End If
        End Sub
        Private Sub Button4_Click(sender As System.Object, e As System.EventArgs) Handles ResetBox.Click
            entriesDOCNUM = ""
            dates = ""
            entriesStoreNum = ""
            entriesStorName = ""
            quantities = ""
            UPCS = ""
            description = ""
            POnumber = 0
            DateTxtBox.Text = ""
            DescriptionTxtBox.Text = ""
            DirectoryTxtBox.Text = ""
            FileNumberStartTxtBox.Text = ""
            InvtoryTransTxtBox.Text = ""
            QuantityTxtBox.Text = ""
            StoreNameTxtBox.Text = ""
            StoreNumTxtBox.Text = ""
            POtxtBox.Text = ""
            UPCtxtBox.Text = ""
            n = "0"
        End Sub
        Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles AddBtn.Click
            ' direcory
                fileNumber = FileNumberStartTxtBox.Text
            Catch ex As Exception
                fileNumber = 5050
            End Try
            'get the start point to match max later on
            startFileNumber = fileNumber
            entriesDOCNUM = InvtoryTransTxtBox.Text
            entriesStoreNum = StoreNumTxtBox.Text
            entriesStorName = StoreNameTxtBox.Text
            UPCS = UPCtxtBox.Text
            description = DescriptionTxtBox.Text
            quantities = QuantityTxtBox.Text
            dates = DateTxtBox.Text
            'takes to number of row
            lineData = Split(entriesDOCNUM, vbNewLine)
            'number of rows from invenotry transfer
            counters = lineData.Count()
            whereclaus = String.Join(" ", lineData)
            'shows second item in array
            'takes to number of row
            lineData2 = Split(entriesStoreNum, vbNewLine)
            'number of rows from invenotry transfer
            counters2 = lineData2.Count()
            whereclaus = String.Join(" ", lineData)
            'shows second item in array
            'takes to number of row
            lineData3 = Split(entriesStorName, vbNewLine)
            'number of rows from invenotry transfer
            counters3 = lineData3.Count()
            'takes to number of row upcs
            lineData4 = Split(UPCS, vbNewLine)
            'takes to number of row quantity
            lineData5 = Split(quantities, vbNewLine)
            'takes to number of row description
            lineData6 = Split(description, vbNewLine)
            'takes the date
            lineDate = Split(dates, vbNewLine)
            whereclaus = String.Join(" ", lineData)
            'shows second item in array
            If counters > counters2 Then
                biggestCount = counters
            ElseIf counters2 > counters3 Then
                biggestCount = counters2
                biggestCount = counters3
            End If
        End Sub
        Private Sub Button3_Click(sender As Object, e As EventArgs) Handles CloseBtn.Click
        End Sub
        Private Sub Button2_Click(sender As System.Object, e As System.EventArgs) Handles ProcessBtn.Click
            If browserDirectory.SelectedPath < "" Then
                Exit Sub
            End If
            'redlare after
            POnumber = POtxtBox.Text.ToString.Trim
            If POnumber.ToString.Length <> 5 Then
                MsgBox("PO needs to be a 5 digit number", MsgBoxStyle.Critical, "PO Length")
                Exit Sub
            End If
            Do Until biggestCount + startFileNumber = fileNumber Or n = biggestCount
                Dim linenum As Integer = 118
                storeNameChange = False
                SQLString = ""
                CSVtext = ""
                storeName1 = ""
                    storeName2 = ""
                    SQLString = "<?xml version=""1.0"" encoding="" UTF-8""?> " & vbNewLine &
    "<Tran><Form_Id>SDM856LD</Form_Id><FS_VER>5, 0, 1, 256</FS_VER><FS_UNITNUM></FS_UNITNUM><FS_LANG>E</FS_LANG><FS_FSNUM>FS2001112301287</FS_FSNUM><FS_PO_UNITNUM></FS_PO_UNITNUM><FS_Viewer><FS_Title>exAct® - Shoppers Drug Mart ASN DSD  - Faxinating Solutions Inc.</FS_Title><FS_STYLE>SDM856LD\SDM856LD_01.xsl</FS_STYLE></FS_Viewer><LINENUM>0000000127</LINENUM><STATUS>Y</STATUS><BSN02>" &
    lineData(n) &
    "</BSN02><VENDOR>54324</VENDOR><CONF>Y</CONF><CreationFlag>N</CreationFlag><Shipment><LINENUM>0000000126</LINENUM><STATUS>Y</STATUS><DTM02_067>" &
    lineDate(n) & "</DTM02_067><N102_SF>163453 CANADA INC</N102_SF><N104_SF>ER7</N104_SF><N102_ST>" & lineData3(n) & "</N102_ST><N104_ST>" & lineData2(n) &
    "</N104_ST><DerivedFlag>N</DerivedFlag><OrderPOSource></OrderPOSource><Order><LINENUM>0000000125</LINENUM><STATUS>Y</STATUS><PRF01>" &
    POnumber.ToString() & "</PRF01>"
                    ' Do Until storeName1 <> storeName2
                    If storeName1 = storeName2 Then
                        SQLString = SQLString & "<Item><LINENUM>" & "0000000" & linenum & "</LINENUM><STATUS>Y</STATUS><LIN02>UP</LIN02><LIN03>" & lineData4(n) & "</LIN03><SN102>" & lineData5(n) & "</SN102><SN103>EA</SN103><PID05>" & lineData6(n) & "</PID05></Item>"
                        linenum = linenum + 1
                        n = n + 1
                        If n >= lineData.Length Then
                            'Exit Do
                        End If
                        storeName1 = lineData3(n).ToString()
                        If n > 0 Then
                            If storeName2 = lineData3(n - 1).ToString() Then
                                storeName2 = storeName1
                            End If
                            If biggestCount + startFileNumber = fileNumber Then
                                storeNameChange = True
                                'Exit Do
                            End If
                            storeNameChange = True
                        End If
                    End If
                    SQLString = SQLString & "</Order></Shipment></Tran>"
                    CSVtext = SQLString
                    outDirectory = inDirectory.ToString & "\0000" & fileNumber.ToString() & ".xml"
                    'unquote top after the file is made 
                    Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
                    fileNumber = fileNumber + 1
                    If fileNumber = startFileNumber + lineData.Length Then
                        Exit Do
                    End If
                    If n = lineData2.Length Then
                        Exit Sub
                    End If
                Catch ex As Exception
                End Try
        End Sub
    End Class

    Friday, September 20, 2019 3:32 AM

All replies

  • Hi,

    I'm sorry, but could you describe where your problem is?

    It would be better if you could share us error message in text format.

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact

    Monday, September 23, 2019 7:05 AM
  • For some reason the software takes each line and makes a file. I want it to group by transfer # and make a file for each unique transfer #. Sometimes the transfer # repeats and there will be different line #s.
    Wednesday, September 25, 2019 9:20 PM
  • Hello,

    My recommendation is to import the Excel data into MS-Access then use a SELECT statement to place the data into a DataTable followed by performing a LINQ Group By.

    I took you modified sheet, changed names on row one so they would make it easy to import via MS-Access import feature. Once in used the code below where .DataRow property contains rows for a document number and has each column plus there is a group count e.g. group.DataRow.Count to see how many rows there are for each grouping.

    Data container class

    Public Class GroupData
        Public Property DocumentNumber() As Double
        Public Property Data As IGrouping(Of Double, DataRow)
        Public Property DataRow() As IEnumerable(Of DataRow)
    End Class

    Code as per above

    Imports System.Data.OleDb
    Public Class DataOperations
        Private ConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                                             "Data Source=C:\Dotnet_Development\VS2017\ExcelFiles\Database1.accdb"
        Public LastException As Exception
        Public Sub ReadData()
            Dim selectStatement =
                    "SELECT ID, DocumentNumber, DocumentDate, StoreNumber, shortName, ItemCode, Item, Quantity " &
                    "FROM ModifiedTable;"
            Dim dt As New DataTable
            Using cn As New OleDbConnection With {.ConnectionString = ConnectionString}
                Using cmd As New OleDbCommand With {.Connection = cn, .CommandText = selectStatement}
                End Using
            End Using
            Dim GroupResults = dt.AsEnumerable.GroupBy(
                Function(row) row.Field(Of Double)("DocumentNumber")) _
                    .Select(Function(group) New GroupData With
                                   .DocumentNumber = group.Key,
                                   .DataRow = group.Select(Function(x) x),
                                   .Data = group
            For Each group In GroupResults
                Console.WriteLine($"Doc number: {group.DocumentNumber} Count: {group.DataRow.Count()}")
                For Each dRow As DataRow In group.DataRow
                    Console.WriteLine($"     {dRow.Field(Of String)("Item"),-40}{dRow.Field(Of Double)("Quantity")}")
        End Sub
    End Class

    Kicked off in a console project (code above was placed in a class project)

    Imports AccessGrouping
    Module Module1
        Sub Main()
            Dim ops As New DataOperations
        End Sub
    End Module

    Of course you would not use a console project, I did because it makes things easy for me to demonstrate the logic.

    Bottom line is Excel is great except when there are attempts to use as a database or as you currently are pasting columns into your program.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Thursday, September 26, 2019 12:05 AM
  • I have never used LINQ commands. Any suggestions?
    Thursday, October 10, 2019 4:04 PM