I have a big text file with 2k plus lines. The code for the moment is looking for specific tags and it put in a column. At tag Value Date is creating a new row.
One of the things is: I have tag AMT and until the code creates a new row, AMT is overwritten by another AMT value. The only way to avoid this is somehow to load in memory 2 lines from text and based on the previous value to print or not the next value.
I could accept a code to input the text file and output with AMT fixed and then run my below code.
Sub OpenFilesConvertTexttoCOL()
Dim MyFolder As String
Dim MyFile As String
Dim textline As String
MyFolder = "C:\Folder\"
MyFile = Dir(MyFolder & "\*.txt")
Dim filename As String
Dim currentrow As Long: currentrow = 2
Do While MyFile <> "" 'This will go through all files in the directory, "Dir() returns an empty string at the end of the list
filename = MyFolder & "\" & MyFile 'concatinates directory and filename
Open filename For Input As #1
Do Until EOF(1) 'reads the file Line by line
Line Input #1, textline
'text = text & textline
If (textline = " ") Or _
(textline Like "*Amount: Cur:*") Then 'error handler, if line was empty, ignore
Else
Dim splitline() As String
splitline() = Split(textline, ": ", -1, vbTextCompare)
' because of how my specific text was formatted, this splits the line into 2 strings. The Tag is in the first element, the data in the second
If IsError(splitline(0)) Then
splitline(0) = ""
End If
Select Case Trim(splitline(0)) 'removes whitespace
Case "Currency"
'currentrow = currentrow + 1 'files that didn't have a description row, resulted in empty rows in the spreadsheet.
ActiveSheet.Range("A" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), " ")(0) '= splitline(1) ,= Trim(splitline(1)), " ")
Case "Value Date"
ActiveSheet.Range("B" & currentrow).Cells(1, 1).Value = Trim(Right(splitline(1), 42)) 'Trim(Right(splitline(1), 42))
currentrow = currentrow + 1 'files that didn't have a description row, resulted in empty rows in the spreadsheet.
Case "Reffor the A O"
ActiveSheet.Range("C" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), " ")(0)
Case "Amt"
ActiveSheet.Range("D" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), " ")(0)
Case "Ref"
ActiveSheet.Range("E" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), " ")(0)
Case "DCM"
ActiveSheet.Range("F" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(2)), " ")(0)
Case "NO. TR"
ActiveSheet.Range("H" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), "VOR")(0)
Case "Id Code"
ActiveSheet.Range("I" & currentrow).Cells(1, 1).Value = Split(Trim(splitline(1)), " ")(0)
Case "ZGR"
ActiveSheet.Range("K" & currentrow).Cells(1, 1).Value = Trim(splitline(1))
'Case "Supplementary Details"
'ActiveSheet.Range("L" & currentrow).Cells(1, 1).Value = splitline(1)
'...etc. etc... so on for each "tag"
End Select
'different Separators
splitline() = Split(textline, "XXX-", -1, vbTextCompare)
Select Case Trim(splitline(0))
Case "YOUR REF "
ActiveSheet.Range("G" & currentrow).Cells(1, 1).Value = Trim(splitline(1))
End Select
splitline() = Split(textline, "-ZAHLUNG", -1, vbTextCompare)
Select Case Trim(splitline(0))
Case "/REMI/AUSLANDS"
ActiveSheet.Range("J" & currentrow).Cells(1, 1).Value = Trim(splitline(1))
End Select
End If
Loop
Close #1
MyFile = Dir() 'reads filename of next file in directory
Loop
End Sub
If is needed I could put a template of my TXT file, but due to the privacy of the data it will take some time to clear the file.
LATER EDIT: My TXT example:
enter code here
Block 3
Block 4
F20: T R N
F25: A I - A
F28C: St Number/Se Number
Statement Number: 00109
Sequence Number: /002
F60M: O B - D/C - Date - Cur - Amt
DCMark: D/C Mark: C
Date: 180606 2000 Apr 01
Currency: EUR EURO
Amt: 21489135,63 #21489135,63#
F61: SSLL
Value Date: 180606 2000 Apr 01
DCM: Debit/Credit Mark: C
Amt: 694,88 #694,88#
Transaction Type: N
Id Code: TRF
Reffor the A O: 43000180606Z9257
Ref: //034542452450
Supplementary Details:
F86: info22
/BENM//GT2543534534534 aaa aaaa
/ORDP/aaaa aaa aaaaa 8/18
/REMI/UBERWEISUNG OUR REF: 034564654456
YOUR REF: 43000180606Z9257 S103 FAV Y/ddsdsXXX
ZGR: HHFHD SFOI DNNDS AGT: EUR 694,88
/AT934300030233977005 BIC:VBOEATWWXXX
F61: SSLL
Value Date: 180606 2000 Apr 01
DCM: Debit/Credit Mark: C
Amt: 2000,00 #2000,00#
Transaction Type: N
Id Code: TRF
Reffor the A O: 3801920873
Ref: //03GT180606136336
Supplementary Details:
/TGDFD/EUR2000,00/
F86: info1
/BENM//GT39FEMA16126000011201000470400 S-K, HA
/ORDP/
/REMI/UIS OUR REF: 03GT180606136336
YOUR REF: 3801920873 S103 FAV Y/ddsdsXXX
ZGR: /RFB/WITHDRAWAL AGT: EUR 2.000,00
F61: SSLL
Value Date: 180606 2000 Apr 01
DCM: Debit/Credit Mark: C
Amt: 2845,00 #2845,00#
Transaction Type: N
Id Code: TRF
Reffor the A O: FTT7681198 01
Ref: //014454154564
Supplementary Details:
/GTF/EUR2845,00/
F86: info22
//ewrewtweqterqyfrgdfgaddfag
/343253256346456/gfdgadfgasdadf
/REMI/UBERWEISUNG OUR REF: 180606192446
YOUR REF: 4354356346
ZGR: /fdgafg/3464363434 AGT: EUR 2.845,00
F62M: C B - D/C - Date - Currency - Amt
DCMark: D/C Mark: C
Date: 180606 2000 Apr 01
Currency: EUR EURO
Amt: 354543634 #54654656#
Block 5
If you put the code applied on upper text you will result in row 5 excel: AMT 354543634 instead of 2845,00. This is happening because AMT is overwritten and is imposibile, for me, that function EOF to ignore a string based on previous string. Line "F**:" is always the same so if i can relate to this line to ignore X numbers of lines it will solve my situation.
Any other suggestion to work around is accepted including other software of extracting the data.
Many thanks for future help.