Import and Transpose From File Text File Into Table

[maruli]

hi...iam beginner need help..

I want to insert (retrieve) text file and transpose into table
Example:

BANK "A" G501 Z000 TIME: 7:53:36 DATE: 24-07-2020 PAGE:0001
OVERDUE REPAYMENT REPORT YAALN07K
BD PERIOD : 08 - 2020
BRANCH : 0014 - BANDUNG BRANCH
CCY : IDR - Rupiah
GROUP CODE: B.1.5292 - ANDY MARSING
------------------------------------------------------------------------------------------------------------------------------------
NO.LOAN TYPE DEBTOR NAME PREVIOUS BALANCE INS. PRINCIPAL INTEREST TOT INSTALLMENT BALANCE TERM
------------------------------------------------------------------------------------------------------------------------------------
1131025000136 G22 RUDI 126,500,009 0 0 61,743,101 61,743,101 126,500,009 60
------------------------------------------------------------------------------------------------------------------------------------
TOTAL INSTALLMENT PER GROUP CODE 1 126,500,009 0 61,743,101 61,743,101 126,500,009
====================================================================================================================================
CONT. PAGE 0002
BANK "A" G501 Z000 TIME: 7:53:36 DATE: 24-07-2020 PAGE:0002
OVERDUE REPAYMENT REPORT YAALN07K
BD PERIOD : 08 - 2020
BRANCH : 0014 - BANDUNG BRANCH
CCY : IDR - Rupiah
GROUP CODE: B.1.5293 - ROBERT
------------------------------------------------------------------------------------------------------------------------------------
NO.LOAN TYPE DEBTOR NAME PREVIOUS BALANCE INS. PRINCIPAL INTEREST TOT INSTALLMENT BALANCE TERM
------------------------------------------------------------------------------------------------------------------------------------
1140211001030 G22 JOHANNES 2,083,348 0 0 1,688,511 1,688,511 2,083,348 36
------------------------------------------------------------------------------------------------------------------------------------
TOTAL INSTALLMENT PER GROUP CODE 1 2,083,348 0 1,688,511 1,688,511 2,083,348
====================================================================================================================================
CONT. PAGE 0003
BANK "A" G501 Z000 TIME: 7:53:36 DATE: 24-07-2020 PAGE:0003
OVERDUE REPAYMENT REPORT YAALN07K
BD PERIOD : 08 - 2020
BRANCH : 0014 - BANDUNG BRANCH
CCY : IDR - Rupiah
GROUP CODE: B.1.8371 - ANTHONY
------------------------------------------------------------------------------------------------------------------------------------
NO.LOAN TYPE DEBTOR NAME PREVIOUS BALANCE INS. PRINCIPAL INTEREST TOT INSTALLMENT BALANCE TERM
------------------------------------------------------------------------------------------------------------------------------------
1200107000117 G6A HERMAN 119,937,303 7 883,594 1,599,164 2,482,758 119,053,709 84
1200108000262 G6A JAMES 110,342,320 7 812,906 1,471,231 2,284,137 109,529,414 84
1200115000258 G6A ABDULLAH 130,491,784 7 961,351 1,739,890 2,701,241 129,530,433 84
1200120000013 G6A INDRA 115,139,809 7 848,251 1,535,197 2,383,448 114,291,558 84
------------------------------------------------------------------------------------------------------------------------------------
TOTAL INSTALLMENT PER GROUP CODE 4 475,911,216 3,506,102 6,345,482 9,851,584 472,405,114
====================================================================================================================================
CONT. PAGE 0004

then I want to transpose and insert into table and become like this

Branch Code	Branch Name	Group Code	Group Name	Month Period	Year Period	Time	Date	Loan Reference	Loan Type	Debtor Name	Previous Balance	Installment	Principal	Interest	Total Installment	Balance	Term
0014	BANDUNG BRANCH	B.1.5292	ANDY MARSING	08	2020	7:53:36 AM​	7/24/2020​	1131025000136	G22	RUDI	126500009​	0​	0​	61743101​	61743101​	126500009​	60​
0014	BANDUNG BRANCH	B.1.5293	ROBERT	08	2020	7:53:36 AM​	7/24/2020​	1140211001030	G22	JOHANNES	2083348​	0​	0​	1688511​	1688511​	2083348​	36​
0014	BANDUNG BRANCH	B.1.8371	ANTHONY	08	2020	7:53:36 AM​	7/24/2020​	1200107000117	G6A	HERMAN	119937303​	7​	883594​	1599164​	2482758​	119053709​	84​
0014	BANDUNG BRANCH	B.1.8371	ANTHONY	08	2020	7:53:36 AM​	7/24/2020​	1200108000262	G6A	JAMES	110342320​	7​	812906​	1471231​	2284137​	109529414​	84​
0014	BANDUNG BRANCH	B.1.8371	ANTHONY	08	2020	7:53:36 AM​	7/24/2020​	1200115000258	G6A	ABDULLAH	130491784​	7​	961351​	1739890​	2701241​	129530433​	84​
0014	BANDUNG BRANCH	B.1.8371	ANTHONY	08	2020	7:53:36 AM​	7/24/2020​	1200120000013	G6A	INDRA	115139809​	7​	848251​	1535197​	2383448​	114291558​	84​

Now I have code like this but I dont know how to transpose it. I can only insert from field loan reference until term, but I dont know the code for loop then branch and group code each page. This is my code:

Private Sub cmdImportYBT_Click()
Dim f As Office.FileDialog
Dim str As String
FileList.RowSource = ""
Dim strListItems As String
Dim data As String

Set f = Application.FileDialog(msoFileDialogFilePicker)
f.AllowMultiSelect = False
f.Title = "Pilih File YBT Kuitansi"
f.Filters.Clear
f.Filters.Add "TXT", "*.txt*"
If f.Show Then
str = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
Open str For Input As #1
While Not EOF(1)
Line Input #1, strListItems
If Left(strListItems, 1) = "1" Then
cn.Execute "INSERT INTO BufferYBT(ref,lt) VALUES('" & Left(strListItems, 13) & "','" & Mid(strListItems, 15, 3) & "')"
End If

Wend
Close #1
MsgBox "YBT is imported"
FileName = str
End If

End Sub



Thank you....

 

[Ranman256]

Insert the text ,as is, into a table, THEN afterwards run a Crosstab query.

 

[maruli]

Insert the text ,as is, into a table, THEN afterwards run a Crosstab query.

if I use code below:

If Left(strListItems, 1) = "1" Then
cn.Execute "INSERT INTO BufferYBT(ref,lt) VALUES('" & Left(strListItems, 13) & "','" & Mid(strListItems, 15, 3) & "')"
End If

the result is only the content inside table text but the branch code, branch name, etcetera on top of table text is not included

 

[arnelgp]

here is your Text scraper.

 

[maruli]

here is your Text scraper.

oooohhhhhh..... spectacular...from this code I can modify many source file text similar like this and convert it into table...thousand thank to you master....


access VBA is getting interesting to learn

 

[Gasman]

Crossposted only today https://www.accessforums.net/showthread.php?t=82680

That is me out.