hi...iam beginner need help..
I want to insert (retrieve) text file and transpose into table
Example:
file attach
YAALN07P160587xx.txt
then I want to Import 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/20201131025000136 G22 RUDI 126500009 0 0 61743101 61743101 126500009 600014 BANDUNG BRANCH B.1.5293 ROBERT 08 2020 7:53:36 AM 7/24/20201140211001030 G22 JOHANNES 2083348 0 0 1688511 1688511 2083348 360014 BANDUNG BRANCH B.1.8371 ANTHONY 08 2020 7:53:36 AM 7/24/20201200107000117 G6A HERMAN 119937303 7 883594 1599164 2482758 119053709 840014 BANDUNG BRANCH B.1.8371 ANTHONY 08 2020 7:53:36 AM 7/24/20201200108000262 G6A JAMES 110342320 7 812906 1471231 2284137 109529414 840014 BANDUNG BRANCH B.1.8371 ANTHONY 08 2020 7:53:36 AM 7/24/20201200115000258 G6A ABDULLAH 130491784 7 961351 1739890 2701241 129530433 840014 BANDUNG BRANCH B.1.8371 ANTHONY 08 2020 7:53:36 AM 7/24/20201200120000013 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).Selected Items(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
If I user this code
If Left(strListItems, 1) = "1" Then
cn.Execute "INSERT INTO BufferYBT(ref,lt) VALUES('" & Left(strListItems, 13) & "','" & Mid(strListItems, 15, 3) & "')"
End If
only the record from each details insert into table
Thank you....