J
jbrotto
Guest
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
Continue reading...
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
Continue reading...