none
not sure why I am looping out when I hit the second word in my conversion proegram RRS feed

  • Question

  • Right now I am testing out the     Sub InvoiceAtlantic() and I tend to exit my loop at the DUNs or the blank PO number in the H row.

    The file I am working on 

    #	Line Type	DUNS number	PO Number	Invoice  Number	Ship date	PO date	Invoice Date	Total Units	Total Weight	Total	Units UOM	Ship To Name	Ship To Number	terms percent	terms number days	terms net days	FOB: Payment	FOB: Location	Department Number	Total GST/HST Amount	confirmation	Internal Number	LineNumber	
    1	H	2492059980000		510125	Nov  9 2017 12:00AM		Nov  9 2017 12:00AM	50.000000		209.880000	EA	FOODLAND # 9296 (MILFORD)					PP	OR	004	27.380000	Y	213694	1	
    2	D	UP	624262230137	50.000000	EA	3.650000	CHRISTMAS $4.99 DVD														213694	2	
    3	H	2492059980000		530781	Jan 30 2019 12:00AM		Jan 30 2019 12:00AM	200.000000		1132.750000	EA	FOODLAND # 9296 (MILFORD)					PP	OR	004	147.750000	Y	234350	1	
    4	D	UP	624262499015	100.000000	EA	3.650000	DVD4B														234350	2	
    5	D	UP	624262699026	50.000000	EA	5.100000	DVD6B														234350	4	
    6	D	UP	624262719991	50.000000	EA	7.300000	DVD9B														234350	6	
    7	H	2492059980000		536944	May 16 2019 12:00AM		May 16 2019 12:00AM	200.000000		1132.750000	EA	Stewiacke Foodland Store					PP	OR	004	147.750000	Y	240513	1	
    8	D	UP	624262499015	100.000000	EA	3.650000	DVD4B														240513	2	
    9	D	UP	624262699026	50.000000	EA	5.100000	DVD6B														240513	4	
    10	D	UP	624262719991	50.000000	EA	7.300000	DVD9B														240513	6	
    11	H	2492059980000		536945	May 16 2019 12:00AM		May 16 2019 12:00AM	150.000000		713.000000	EA	FOODLAND # 9296 (MILFORD)					PP	OR	004	93.000000	Y	240514	1	
    12	D	UP	624262499015	100.000000	EA	3.650000	DVD4B														240514	2	
    13	D	UP	624262699026	50.000000	EA	5.100000	DVD6B														240514	4	
    

    Public Class Form1
        Dim inDirectory, outDirectory As String
        'header line strings
       Dim outputCSV As String
        'repeat detail line
        Dim repeatDetailLine As Boolean = True
        'comma string
        Dim cString As String = ","
        Dim fixInv As Integer
        Dim TextDate As String
        Dim EDIdate As String
        Dim docEntry As String
        Sub Directory()
            Dim fdlg As OpenFileDialog = New OpenFileDialog()
            fdlg.Title = "No Frills .txt Open File Dialog"
            'fdlg.InitialDirectory = "c:\"
            fdlg.Filter = "Text files (*.txt)|*.txt|All files (*.*)|*.*"
            fdlg.FilterIndex = 1
            fdlg.RestoreDirectory = True
            If fdlg.ShowDialog() = DialogResult.OK Then
                TextBox1.Text = fdlg.FileName
                inDirectory = TextBox1.Text.ToString
                outDirectory = inDirectory.Replace(".txt", ".csv")
                TextBox2.Text = outDirectory.ToString
            End If
    
        End Sub
        Sub containingText(ByRef textToConvert, ByVal textToLookFor)
            If textToConvert.ToString.Contains(textToLookFor) = True Then
                textToConvert = textToConvert.ToString.Replace(textToLookFor, "")
            End If
        End Sub
        Sub containingTextSpace(ByRef textToConvert, ByVal textToLookFor)
            If textToConvert.ToString.Contains(textToLookFor) = True Then
                textToConvert = textToConvert.ToString.Replace(textToLookFor, " ")
            End If
        End Sub
    
        Function dateConverter(ByRef NonFormattedDate As String)
            Dim year, month, day As String
    
            Dim TextMonth As String = ""
            Dim FormattedDate As String = ""
            'month = System.DateTime.Today.Month.ToString()
            TextMonth = NonFormattedDate.Substring(0, 3)
            TextDate = NonFormattedDate.ToString
            Select Case TextMonth.ToUpper
                Case "JAN"
                    month = "01"
                Case "FEB"
                    month = "02"
                Case "MAR"
                    month = "03"
                Case "APR"
                    month = "04"
                Case "MAY"
                    month = "05"
                Case "JUN"
                    month = "06"
                Case "JUL"
                    month = "07"
                Case "AUG"
                    month = "08"
                Case "SEP"
                    month = "09"
                Case "OCT"
                    month = "10"
                Case "NOV"
                    month = "11"
                Case "DEC"
                    month = "12"
                Case Else
                    month = System.DateTime.Today.Month.ToString()
                    If month < 10 Then
                        month = "0" & month
                    End If
            End Select
            year = NonFormattedDate.Substring(6).Trim()
            day = NonFormattedDate.Substring(4, 2).Trim()
            If day.Length < 2 Then
                day = "0" & day.ToString()
            End If
            FormattedDate = year.ToString() & month.ToString() & day.ToString()
            EDIdate = year.ToString() & month.ToString() & day.ToString()
            Return FormattedDate
        End Function
    
        Private Sub BrowseBtn_Click(sender As System.Object, e As System.EventArgs) Handles BrowseBtn.Click
            Directory()
        End Sub
    
    
        Sub Credit()
            Dim rowCounts As ULong
            Dim errors As Integer
            Dim newLinesString As String = vbNewLine.ToString & vbNewLine.ToString
            Dim commaCleanBool, CRdetailLineBool As Boolean
            Dim CSVlength, WordCounter As ULong
            Dim wholeFile As String
            Dim lineData() As String
            Dim fieldData() As String
            Dim currentYear As String = "2019"
    
            Dim CSVtext As String = ""
    
            Try
                Me.Enabled = False
                Dim filePath As String = inDirectory.ToString
                If inDirectory.ToString() = "" = True Or Nothing = True Then
                    MsgBox("Enter a valid directory.", MsgBoxStyle.Information, "Invalid Path")
                    Exit Sub
                End If
                wholeFile = My.Computer.FileSystem.ReadAllText(filePath)
                If wholeFile.Contains(vbTab.ToString() & "CR" & vbTab.ToString()) = True Then
                    CRdetailLineBool = True
                End If
                lineData = Split(wholeFile, vbNewLine)
                For Each lineOfText As String In lineData
                    WordCounter = 0
                    fixInv = 0
                    If lineOfText.Contains("       #	Line Type	Invoice Date	Invoice  Number	PO date	PO Number	Department Number	Name	Store Number	DUNS number	terms percent	terms days	terms net	Ship date	Total	confirmation	Numerator	LineNumber") = True Then
                        lineOfText = Nothing
    
                    Else
                        If lineOfText.Contains(vbTab & "Y" & vbTab) = True Then
                            Try
                                docEntry = lineOfText.Substring(lineOfText.Length - 8, 5).Trim()
                                'MsgBox(docEntry, MsgBoxStyle.Information, docEntry)
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(vbTab & "Y" & vbTab.ToString()) + 3))
    
                            Catch ex As Exception
    
                            End Try
                        End If
    
                        If lineOfText.Contains("D	UA") = True Then
                            Try
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(docEntry))) & vbTab.ToString() & "" & vbTab.ToString() & ""
                                lineOfText = lineOfText.Replace("						", "")
                                lineOfText = lineOfText.Replace(".000000", "")
                            Catch ex As Exception
    
                            End Try
                        End If
    
                        If lineOfText.Contains("S	C") = True Then
                            Try
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(docEntry)))
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(".") + 3)) & vbTab.ToString() & " "
                                lineOfText = lineOfText.Replace("										", "")
    
                            Catch ex As Exception
    
                            End Try
    
                        End If
                        'If lineOfText.Contains("H") = True And lineOfText.Contains("003") = True Then
                        '    Try
                        '        lineOfText = lineOfText.Replace(vbTab & "0.00" & vbTab, vbTab & vbTab)
                        '
                        '    Catch ex As Exception
                        '
                        '    End Try
                        '
                        'End If
                        lineOfText = lineOfText.Replace(vbCr, "")
                        lineOfText = lineOfText.Replace(",", "")
                        lineOfText = lineOfText.Replace("(", "")
                        lineOfText = lineOfText.Replace(")", "")
                        lineOfText = lineOfText.Replace("$", " ")
                        lineOfText = lineOfText.Replace("#", " ")
                        lineOfText = lineOfText.Replace("É", "E")
                        lineOfText = lineOfText.Replace("È", "E")
                        lineOfText = lineOfText.Replace("/", "")
                        lineOfText = lineOfText.Replace("\", "")
                        lineOfText = lineOfText.Replace("   ", " ")
                        lineOfText = lineOfText.Replace("  ", " ")
    
    
    
                        fieldData = lineOfText.Split(vbTab.ToString)
                        rowCounts = rowCounts + 1
    
                        For Each wordOfText As String In fieldData
                            fixInv = fixInv + 1
                            WordCounter = WordCounter + 1
    
                            If wordOfText = TextDate Then
                                wordOfText = EDIdate.ToString
                            End If
    
                            If errors < rowCounts Then
                                wordOfText = wordOfText.Replace(rowCounts.ToString(), "")
                                errors = errors + 1
                                commaCleanBool = True
                            End If
    
    
                            If wordOfText.Contains("12:00AM") = True Then
                                wordOfText = wordOfText.Replace(" 12:00AM", "")
                            End If
    
    
    
                            If wordOfText.Contains("-") = True Then
                                If wordOfText.Contains("0.000000") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("000000", "")
                                    'wordOfText = wordOfText.Replace("00000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                'If wordOfText.Contains("0000") = True Then
                                '    wordOfText = wordOfText.Replace("0000", "")
                                'End If
                                Try
    
    
                                    If wordOfText.Length() = wordOfText.IndexOf(".") + 1 Then
                                        wordOfText = wordOfText.Replace(".", "")
                                    End If
                                    'If wordOfText.Length = 4 Then
                                    '    wordOfText = wordOfText.Replace("0.00", "")
                                    'End If
                                    'If wordOfText.Length() >= wordOfText.IndexOf("0.00") + 3 Then
                                    '    wordOfText = wordOfText.Replace(".00", "")
                                    'End If
                                    'End If
                                Catch ex As Exception
    
                                End Try
    
                            Else
                                If wordOfText.Contains("0.000000") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("000000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                'Try
                                '    If wordOfText.Substring(wordOfText.Length - 1, 1).Contains(".") = True Then
                                '        wordOfText = wordOfText.Replace(".", ".00")
                                '    End If
                                'Catch ex As Exception
                                '
                                'End Try
                                'If wordOfText.Length > 60 Then
                                '    wordOfText = wordOfText.Substring(0, 60)
                                'End If
                            End If
                            wordOfText = wordOfText.Replace(",", "")
                            If wordOfText.Contains(" ") = True Then
                                'done to speed up the processing and only need to be changes in the year 2100 and later.
                                If wordOfText.Contains(currentYear) = True Then
                                    Try
                                        wordOfText = dateConverter(wordOfText)
                                    Catch ex As Exception
    
                                    End Try
                                End If
    
                            End If
                            'If wordOfText.Contains("(") = True Then
                            '    Dim bracketLocation As Integer
                            '    bracketLocation = wordOfText.IndexOf("(")
                            '    wordOfText = wordOfText.Substring(0, bracketLocation).Trim()
                            'End If
    
    
                            wordOfText = wordOfText.ToUpper()
    
    
                            'If fixInv = 5 Then
                            'wordOfText = wordOfText.Replace(".00", "")
                            'End If
                            'Try
                            '    If wordOfText.Length < 6 Then
                            '        If wordOfText.Substring(wordOfText.Length - 1, 1) = "." Then
                            '            wordOfText = wordOfText.Replace(".", "")
                            '        End If
                            '    End If
                            '
                            'Catch
                            'End Try
                            'Try
                            '    If WordCounter = 6 Or WordCounter = 13 Then
                            '        If wordOfText.Substring(wordOfText.IndexOf(".", 3)) = ".00" Then
                            '            wordOfText = wordOfText.Replace(".00", "")
                            '        End If
                            '    End If
                            'Catch ex As Exception
                            '
                            'End Try
                            If commaCleanBool = True Then
                                CSVtext = CSVtext & wordOfText.ToString
                                commaCleanBool = False
                            Else
                                CSVtext = CSVtext & wordOfText.ToString & ","
    
                            End If
    
                        Next wordOfText
    
                        CSVlength = CSVtext.Length
    
                        CSVtext = CSVtext.Substring(0, CSVlength - 2) & vbNewLine.ToString()
                        'End If
    
    
    
                    End If
    
    
    
                Next lineOfText
    
                If CSVtext.Contains(newLinesString.ToString()) = True Then
                    CSVtext = CSVtext.Replace(newLinesString.ToString(), "")
                End If
    
                Dim finalRowNumberCleaner As Integer = rowCounts
                Do Until finalRowNumberCleaner = 0
                    If CSVtext.Contains(vbNewLine.ToString() & finalRowNumberCleaner) = True Then
                        CSVtext = CSVtext.Replace(vbNewLine.ToString() & finalRowNumberCleaner, vbNewLine.ToString())
                        finalRowNumberCleaner = finalRowNumberCleaner - 1
                    Else
                        Exit Do
                    End If
                Loop
                CSVtext = CSVtext.Replace(",,1,", ",,,")
                MsgBox("Number of rows " & rowCounts.ToString, MsgBoxStyle.Information, "Number of rows " & rowCounts.ToString)
    
                Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
    
                outFile.WriteLine(CSVtext.ToString())
    
                outFile.Close()
    
                MsgBox(My.Computer.FileSystem.ReadAllText(outDirectory).Substring(0, My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("EA") + My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("MX")), MsgBoxStyle.Information, "Output Sample")
                Me.Enabled = True
    
            Catch ex As Exception
    
            End Try
            Me.Enabled = True
        End Sub
        Private Sub ProcessBtn_Click(sender As System.Object, e As System.EventArgs) Handles ProcessBtn.Click
            If rdbInvoice.Checked = True Then
                ''  InvoiceIGA()
                If rdbAtl.Checked = True Then
                    InvoiceAtlantic()
                ElseIf rdbOntario.Checked = True Then
                    InvoiceOntario()
                Else
                    InvoiceQuebec()
                End If
            ElseIf rdbCredit.Checked = True Then
                ''   Credit()
                If rdbAtl.Checked = True Then
                    CreditAtlantic()
                ElseIf rdbOntario.Checked = True Then
                    CreditOntario()
                Else
                    CreditQuebec()
                End If
            Else
                MsgBox("Select Type of document to convert", MsgBoxStyle.Critical, "Select invoice or credit")
            End If
    
        End Sub
    
        Sub InvoiceQuebec()
    
        End Sub
        Sub CreditQuebec()
    
        End Sub
    
        Sub InvoiceOntario()
    
        End Sub
        Sub CreditOntario()
    
        End Sub
        Sub InvoiceAtlantic()
            Dim rowCounts As ULong
            Dim errors As Integer
            Dim newLinesString As String = vbNewLine.ToString & vbNewLine.ToString
            Dim commaCleanBool As Boolean
            Dim CSVlength, WordCounter As ULong
            Dim wholeFile As String
            Dim lineData() As String
            Dim fieldData() As String
            Dim currentYear As String = ""
    
            Dim CSVtext As String = ""
            Try
                If txtYear.Text = "" Then
                    currentYear = "2019"
                Else
                    currentYear = txtYear.Text
                End If
            Catch ex As Exception
    
            End Try
            Try
                Me.Enabled = False
                Dim filePath As String = inDirectory.ToString
                If inDirectory.ToString() = "" = True Or Nothing = True Then
                    MsgBox("Enter a valid directory.", MsgBoxStyle.Information, "Invalid Path")
                    Exit Sub
                End If
                wholeFile = My.Computer.FileSystem.ReadAllText(filePath)
    
                lineData = Split(wholeFile, vbNewLine)
                For Each lineOfText As String In lineData
                    WordCounter = 0
                    fixInv = 0
                    If lineOfText.Contains("#	Line Type	DUNS number	PO Number	Invoice  Number	Ship date	PO date	Invoice Date	Total Units	Total Weight	Total	Units UOM	Ship To Name	Ship To Number	terms percent	terms number days	terms net days	FOB: Payment	FOB: Location	Department Number	Total GST/HST Amount	confirmation	Internal Number	LineNumber") = True Then
                        lineOfText = Nothing
    
                    Else
                        If lineOfText.Contains(vbTab & "Y" & vbTab) = True Then
                            Try
                                docEntry = lineOfText.Substring(lineOfText.Length - 8, 5).Trim()
                                'MsgBox(docEntry, MsgBoxStyle.Information, docEntry)
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(vbTab & "Y" & vbTab.ToString()) + 3))
    
                            Catch ex As Exception
    
                            End Try
                        End If
    
                        If lineOfText.Contains("D	UP") = True Then
                            Try
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(docEntry))) & vbTab.ToString() & "" & vbTab.ToString() & ""
                                lineOfText = lineOfText.Replace("						", "")
                                lineOfText = lineOfText.Replace(".000000", "")
                            Catch ex As Exception
    
                            End Try
                        End If
    
    
    
                        lineOfText = lineOfText.Replace(vbCr, "")
    
                        containingText(lineOfText, ",")
                        containingText(lineOfText, "(")
                        containingText(lineOfText, ")")
                        containingText(lineOfText, "- ")
                        containingText(lineOfText, "/")
                        containingText(lineOfText, "\")
                        containingText(lineOfText, "–")
                        containingText(lineOfText, "-")
                        containingTextSpace(lineOfText, "$")
                        containingTextSpace(lineOfText, "#")
                        containingTextSpace(lineOfText, "   ")
                        containingTextSpace(lineOfText, ",")
    
    
                        lineOfText = lineOfText.Replace("É", "E")
                        lineOfText = lineOfText.Replace("È", "E")
                        lineOfText = lineOfText.Replace("Ë", "E")
                        lineOfText = lineOfText.Replace("Ö", "O")
                        lineOfText = lineOfText.Replace("Ï", "I")
                        lineOfText = lineOfText.Replace("Á", "A")
                        lineOfText = lineOfText.Replace("À", "A")
                        lineOfText = lineOfText.Replace("Ä", "A")
    
    
                        fieldData = lineOfText.Split(vbTab.ToString)
                        rowCounts = rowCounts + 1
    
                        For Each wordOfText As String In fieldData
                            fixInv = fixInv + 1
                            WordCounter = WordCounter + 1
    
                            If wordOfText = TextDate Then
                                wordOfText = EDIdate.ToString
                            End If
    
                            If errors < rowCounts Then
                                wordOfText = wordOfText.Replace(rowCounts.ToString(), "")
                                errors = errors + 1
                                commaCleanBool = True
                            End If
    
    
                            If wordOfText.Contains("12:00AM") = True Then
                                wordOfText = wordOfText.Replace(" 12:00AM", "")
                            End If
    
                            If wordOfText.Length > 30 Then
                                wordOfText = wordOfText.Substring(0, 30)
                            End If
                            'done to replace zeros to atlantic
                            If WordCounter < 8 Then
    
                                Try
                                    If WordCounter < 6 = True Then
                                        wordOfText = wordOfText.Replace(".000000", "")
                                    End If
                                    If wordOfText.Contains(".") = True Then
                                        wordOfText = wordOfText.Replace("000000", "")
                                    End If
                                    If wordOfText.Contains(".") = True Then
                                        wordOfText = wordOfText.Replace("0000", "")
                                    End If
                                Catch ex As Exception
    
                                End Try
    
    
                            End If
    
                            If wordOfText.Contains(" ") = True Then
                                'done to speed up the processing and only need to be changes in the year 2100 and later.
                                If wordOfText.Contains(currentYear) = True Then
                                    Try
                                        wordOfText = dateConverter(wordOfText)
                                    Catch ex As Exception
    
                                    End Try
                                End If
    
                            End If
    
                            If commaCleanBool = True Then
                                    CSVtext = CSVtext & wordOfText.ToString
                                    commaCleanBool = False
                                Else
                                    CSVtext = CSVtext & wordOfText.ToString & ","
    
                                End If
    
    
    
                        Next wordOfText
    
                        CSVlength = CSVtext.Length
    
                        CSVtext = CSVtext.Substring(0, CSVlength - 2) & vbNewLine.ToString()
                        'End If
    
    
    
                    End If
    
    
    
                Next lineOfText
    
                If CSVtext.Contains(newLinesString.ToString()) = True Then
                    CSVtext = CSVtext.Replace(newLinesString.ToString(), "")
                End If
                Dim finalRowNumberCleaner As Integer = rowCounts
                Do Until finalRowNumberCleaner = 0
                    If CSVtext.Contains(vbNewLine.ToString() & finalRowNumberCleaner) = True Then
                        CSVtext = CSVtext.Replace(vbNewLine.ToString() & finalRowNumberCleaner, vbNewLine.ToString())
                        finalRowNumberCleaner = finalRowNumberCleaner - 1
                    Else
                        Exit Do
                    End If
                Loop
                'replaces the 1 2 in detail uom
                CSVtext = CSVtext.Replace(",,1,", ",,,")
                CSVtext = CSVtext.Replace(",,2,", ",,,")
    
                'replaces the extra spaces in all document
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                MsgBox("Number of rows " & rowCounts.ToString, MsgBoxStyle.Information, "Number of rows " & rowCounts.ToString)
    
                Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
    
                outFile.WriteLine(CSVtext.ToString())
    
                outFile.Close()
    
                MsgBox(My.Computer.FileSystem.ReadAllText(outDirectory).Substring(0, My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("EA") + My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("MX")), MsgBoxStyle.Information, "Output Sample")
                Me.Enabled = True
    
            Catch ex As Exception
    
            End Try
            Me.Enabled = True
        End Sub
        Sub CreditAtlantic()
    
        End Sub
        Sub InvoiceIGA()
            Dim rowCounts As ULong
            Dim errors As Integer
            Dim newLinesString As String = vbNewLine.ToString & vbNewLine.ToString
            Dim commaCleanBool, CRdetailLineBool As Boolean
            Dim CSVlength, WordCounter As ULong
            Dim wholeFile As String
            Dim lineData() As String
            Dim fieldData() As String
            Dim currentYear As String = ""
    
            Dim CSVtext As String = ""
            Try
                If txtYear.Text = "" Then
                    currentYear = "2019"
                Else
                    currentYear = txtYear.Text
                End If
            Catch ex As Exception
    
            End Try
            Try
                Me.Enabled = False
                Dim filePath As String = inDirectory.ToString
                If inDirectory.ToString() = "" = True Or Nothing = True Then
                    MsgBox("Enter a valid directory.", MsgBoxStyle.Information, "Invalid Path")
                    Exit Sub
                End If
                wholeFile = My.Computer.FileSystem.ReadAllText(filePath)
                If wholeFile.Contains(vbTab.ToString() & "CR" & vbTab.ToString()) = True Then
                    CRdetailLineBool = True
                End If
                lineData = Split(wholeFile, vbNewLine)
                For Each lineOfText As String In lineData
                    WordCounter = 0
                    fixInv = 0
                    If lineOfText.Contains("#	Line Type	Invoice Date	Invoice  Number	PO date	PO Number	Department Number	Name	Store Number	DUNS " & vbCr & "" & vbCr & "number	terms percent	terms days	terms net	Ship date	Total	confirmation	Internal Number	LineNumber	") = True Then
                        lineOfText = Nothing
    
                    Else
                        If lineOfText.Contains(vbTab & "Y" & vbTab) = True Then
                            Try
                                docEntry = lineOfText.Substring(lineOfText.Length - 8, 5).Trim()
                                'MsgBox(docEntry, MsgBoxStyle.Information, docEntry)
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(vbTab & "Y" & vbTab.ToString()) + 3))
    
                            Catch ex As Exception
    
                            End Try
                        End If
    
                        If lineOfText.Contains("D	UA") = True Then
                            Try
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(docEntry))) & vbTab.ToString() & "" & vbTab.ToString() & ""
                                lineOfText = lineOfText.Replace("						", "")
                                lineOfText = lineOfText.Replace(".000000", "")
                            Catch ex As Exception
    
                            End Try
                        End If
    
                        If lineOfText.Contains("S	C") = True Then
                            Try
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(docEntry)))
                                lineOfText = lineOfText.Replace(lineOfText, lineOfText.Substring(0, lineOfText.IndexOf(".") + 3)) & vbTab.ToString() & " "
                                lineOfText = lineOfText.Replace("										", "")
    
                            Catch ex As Exception
    
                            End Try
    
                        End If
                        'If lineOfText.Contains("H") = True And lineOfText.Contains("003") = True Then
                        '    Try
                        '        lineOfText = lineOfText.Replace(vbTab & "0.00" & vbTab, vbTab & vbTab)
                        '
                        '    Catch ex As Exception
                        '
                        '    End Try
                        '
                        'End If
                        lineOfText = lineOfText.Replace(vbCr, "")
                        'lineOfText = lineOfText.Replace(",", "")
                        containingText(lineOfText, ",")
                        containingText(lineOfText, "(")
                        containingText(lineOfText, ")")
                        containingText(lineOfText, "- ")
                        containingText(lineOfText, "/")
                        containingText(lineOfText, "\")
                        containingText(lineOfText, "–")
                        containingText(lineOfText, "-")
                        containingTextSpace(lineOfText, "$")
                        containingTextSpace(lineOfText, "#")
                        containingTextSpace(lineOfText, "   ")
    
                        'lineOfText = lineOfText.Replace("(", "")
                        'lineOfText = lineOfText.Replace(")", "")
                        'lineOfText = lineOfText.Replace("$", " ")
                        'lineOfText = lineOfText.Replace("-", " ")
                        'lineOfText = lineOfText.Replace("#", " ")
                        'lineOfText = lineOfText.Replace("- ", "")
                        lineOfText = lineOfText.Replace("É", "E")
                        lineOfText = lineOfText.Replace("È", "E")
                        'lineOfText = lineOfText.Replace("/", "")
                        'lineOfText = lineOfText.Replace("\", "")
                        'lineOfText = lineOfText.Replace("   ", " ")
                        'lineOfText = lineOfText.Replace("  ", " ")
    
    
                        fieldData = lineOfText.Split(vbTab.ToString)
                        rowCounts = rowCounts + 1
    
                        For Each wordOfText As String In fieldData
                            fixInv = fixInv + 1
                            WordCounter = WordCounter + 1
    
                            If wordOfText = TextDate Then
                                wordOfText = EDIdate.ToString
                            End If
    
                            If errors < rowCounts Then
                                wordOfText = wordOfText.Replace(rowCounts.ToString(), "")
                                errors = errors + 1
                                commaCleanBool = True
                            End If
    
    
                            If wordOfText.Contains("12:00AM") = True Then
                                wordOfText = wordOfText.Replace(" 12:00AM", "")
                            End If
    
                            If wordOfText.Length > 30 Then
                                wordOfText = wordOfText.Substring(0, 30)
                            End If
    
                            If wordOfText.Contains("-") = True Then
                                If wordOfText.Contains("0.000000") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("000000", "")
                                    'wordOfText = wordOfText.Replace("00000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                'If wordOfText.Contains("0000") = True Then
                                '    wordOfText = wordOfText.Replace("0000", "")
                                'End If
                                Try
    
    
                                    If wordOfText.Length() = wordOfText.IndexOf(".") + 1 Then
                                        wordOfText = wordOfText.Replace(".", "")
                                    End If
                                    'If wordOfText.Length = 4 Then
                                    '    wordOfText = wordOfText.Replace("0.00", "")
                                    'End If
                                    'If wordOfText.Length() >= wordOfText.IndexOf("0.00") + 3 Then
                                    '    wordOfText = wordOfText.Replace(".00", "")
                                    'End If
                                    'End If
                                Catch ex As Exception
    
                                End Try
    
                            Else
                                If wordOfText.Contains("0.000000") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("000000", "")
                                End If
                                If wordOfText.Contains(".") = True Then
                                    wordOfText = wordOfText.Replace("0000", "")
                                End If
                                'Try
                                '    If wordOfText.Substring(wordOfText.Length - 1, 1).Contains(".") = True Then
                                '        wordOfText = wordOfText.Replace(".", ".00")
                                '    End If
                                'Catch ex As Exception
                                '
                                'End Try
                                'If wordOfText.Length > 60 Then
                                '    wordOfText = wordOfText.Substring(0, 60)
                                'End If
                            End If
                            wordOfText = wordOfText.Replace(",", "")
                            If wordOfText.Contains(" ") = True Then
                                'done to speed up the processing and only need to be changes in the year 2100 and later.
                                If wordOfText.Contains(currentYear) = True Then
                                    Try
                                        wordOfText = dateConverter(wordOfText)
                                    Catch ex As Exception
    
                                    End Try
                                End If
    
                            End If
                            'If wordOfText.Contains("(") = True Then
                            '    Dim bracketLocation As Integer
                            '    bracketLocation = wordOfText.IndexOf("(")
                            '    wordOfText = wordOfText.Substring(0, bracketLocation).Trim()
                            'End If
    
    
                            wordOfText = wordOfText.ToUpper()
    
    
                            'If fixInv = 5 Then
                            'wordOfText = wordOfText.Replace(".00", "")
                            'End If
                            'Try
                            '    If wordOfText.Length < 6 Then
                            '        If wordOfText.Substring(wordOfText.Length - 1, 1) = "." Then
                            '            wordOfText = wordOfText.Replace(".", "")
                            '        End If
                            '    End If
                            '
                            'Catch
                            'End Try
                            'Try
                            '    If WordCounter = 6 Or WordCounter = 13 Then
                            '        If wordOfText.Substring(wordOfText.IndexOf(".", 3)) = ".00" Then
                            '            wordOfText = wordOfText.Replace(".00", "")
                            '        End If
                            '    End If
                            'Catch ex As Exception
                            '
                            'End Try
                            If commaCleanBool = True Then
                                CSVtext = CSVtext & wordOfText.ToString
                                commaCleanBool = False
                            Else
                                CSVtext = CSVtext & wordOfText.ToString & ","
    
                            End If
    
                        Next wordOfText
    
                        CSVlength = CSVtext.Length
    
                        CSVtext = CSVtext.Substring(0, CSVlength - 2) & vbNewLine.ToString()
                        'End If
    
    
    
                    End If
    
    
    
                Next lineOfText
    
                If CSVtext.Contains(newLinesString.ToString()) = True Then
                    CSVtext = CSVtext.Replace(newLinesString.ToString(), "")
                End If
                Dim finalRowNumberCleaner As Integer = rowCounts
                Do Until finalRowNumberCleaner = 0
                    If CSVtext.Contains(vbNewLine.ToString() & finalRowNumberCleaner) = True Then
                        CSVtext = CSVtext.Replace(vbNewLine.ToString() & finalRowNumberCleaner, vbNewLine.ToString())
                        finalRowNumberCleaner = finalRowNumberCleaner - 1
                    Else
                        Exit Do
                    End If
                Loop
                'replaces the 1 2 in detail uom
                CSVtext = CSVtext.Replace(",,1,", ",,,")
                CSVtext = CSVtext.Replace(",,2,", ",,,")
    
                'replaces the extra spaces in all document
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                CSVtext = CSVtext.Replace("  ", " ")
                MsgBox("Number of rows " & rowCounts.ToString, MsgBoxStyle.Information, "Number of rows " & rowCounts.ToString)
    
                Dim outFile As IO.StreamWriter = My.Computer.FileSystem.OpenTextFileWriter(outDirectory, False, System.Text.Encoding.Default)
    
                outFile.WriteLine(CSVtext.ToString())
    
                outFile.Close()
    
                MsgBox(My.Computer.FileSystem.ReadAllText(outDirectory).Substring(0, My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("EA") + My.Computer.FileSystem.ReadAllText(outDirectory).IndexOf("MX")), MsgBoxStyle.Information, "Output Sample")
                Me.Enabled = True
    
            Catch ex As Exception
    
            End Try
            Me.Enabled = True
        End Sub
    
    End Class
    
    

    Tuesday, July 23, 2019 8:12 PM

All replies

  • Hello,

    With this much code all we can do is make an educated guess to this issue. The real way to tackle the problem is by using debugging techniques e.g.. set breakpoints and trace through the code at strategic points in your code.

    Many developers never learn how to debug, this is the perfect opportunity for you to learn how to debug code.


    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.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Tuesday, July 23, 2019 8:24 PM
    Moderator
  • Hi

    Lots of code. You don't really explain what is happening and what should happen in an ideal world. And what does 'tend to'mean?

    One thing:  don't leave a Try...Catch  with empty Catch.  At the very least, put a MessageBox,Show() in there so you will know you have an issue.


    Regards Les, Livingston, Scotland

    Tuesday, July 23, 2019 11:17 PM
  • I did use the debugger and I found in the first line starting with H by the second word it exit out for the loop before it makes the csv file as I am trying to remove excessive 0s and replace the tabs with commas.
    Wednesday, July 24, 2019 5:03 PM
  • I did use the debugger and I found in the first line starting with H by the second word it exit out for the loop before it makes the csv file as I am trying to remove excessive 0s and replace the tabs with commas.
    Wednesday, July 24, 2019 5:03 PM
  • I did use the debugger and I found in the first line starting with H by the second word it exit out for the loop before it makes the csv file as I am trying to remove excessive 0s and replace the tabs with commas.

    Hi

    Please tell us where in the code that the exception is happening.

    Better still, post the relevant code as well.


    Regards Les, Livingston, Scotland

    Wednesday, July 24, 2019 5:56 PM