Import and Transpose From File Text File Into Table (1 Viewer)

maruli

New member
Local time
Today, 18:38
Joined
May 18, 2020
Messages
6
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 CodeBranch NameGroup CodeGroup NameMonth PeriodYear PeriodTimeDateLoan ReferenceLoan TypeDebtor NamePrevious BalanceInstallmentPrincipalInterestTotal InstallmentBalanceTerm
0014BANDUNG BRANCHB.1.5292ANDY MARSING082020
7:53:36 AM​
7/24/2020​
1131025000136G22RUDI
126500009​
0​
0​
61743101​
61743101​
126500009​
60​
0014BANDUNG BRANCHB.1.5293ROBERT082020
7:53:36 AM​
7/24/2020​
1140211001030G22JOHANNES
2083348​
0​
0​
1688511​
1688511​
2083348​
36​
0014BANDUNG BRANCHB.1.8371ANTHONY082020
7:53:36 AM​
7/24/2020​
1200107000117G6AHERMAN
119937303​
7​
883594​
1599164​
2482758​
119053709​
84​
0014BANDUNG BRANCHB.1.8371ANTHONY082020
7:53:36 AM​
7/24/2020​
1200108000262G6AJAMES
110342320​
7​
812906​
1471231​
2284137​
109529414​
84​
0014BANDUNG BRANCHB.1.8371ANTHONY082020
7:53:36 AM​
7/24/2020​
1200115000258G6AABDULLAH
130491784​
7​
961351​
1739890​
2701241​
129530433​
84​
0014BANDUNG BRANCHB.1.8371ANTHONY082020
7:53:36 AM​
7/24/2020​
1200120000013G6AINDRA
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

Well-known member
Local time
Today, 07:38
Joined
Apr 9, 2015
Messages
4,337
Insert the text ,as is, into a table, THEN afterwards run a Crosstab query.
 

maruli

New member
Local time
Today, 18:38
Joined
May 18, 2020
Messages
6
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

..forever waiting... waiting for jellybean!
Local time
Today, 19:38
Joined
May 7, 2009
Messages
19,169
here is your Text scraper.
 

Attachments

  • ScrapeLoan.accdb
    512 KB · Views: 427

maruli

New member
Local time
Today, 18:38
Joined
May 18, 2020
Messages
6
here is your Text scraper.
oooohhhhhh..... :D :D :D 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
 

Users who are viewing this thread

Top Bottom