Results 1 to 6 of 6
  1. #1
    maruli090484 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    2

    Import and Conver From File Text File Into Table

    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/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).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....



  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Firstly why not ask whoever produces that report to give you a correctly constructed file for importing.?

    Otherwise you will need to parse each line and depending on what data that line holds capture to your relevant fields. Not an easy task.
    I would start with debug.print the strlistitems variable and then calculate the data to be extracted.

    There have been 58 views on this and this is your first reply.? That should indicate this is not an easy task?

    HTH
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Welshgasman. Your input data is a report from some other process. This indicates pre-processed/manipulated data.
    If you have a "business need" for the details of that report for your process, then you should request the raw data from the provider.
    Good luck.

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Cross-posted here with what seems like accepted solution from Arnelgp:
    https://www.access-programmers.co.uk...-table.315667/

    @Maruli -what is wrong with Arnel's solution? It would have been nice to mention your other post....

    Cheers,
    Vlad
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  5. #5
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Gicu View Post
    Cross-posted here with what seems like accepted solution from Arnelgp:
    https://www.access-programmers.co.uk...-table.315667/

    @Maruli -what is wrong with Arnel's solution? It would have been nice to mention your other post....

    Cheers,
    Vlad
    Nice spot.
    Crossposted AFTER a solution was given. That is me out.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  6. #6
    maruli090484 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2021
    Posts
    2
    Quote Originally Posted by Gicu View Post
    Cross-posted here with what seems like accepted solution from Arnelgp:
    https://www.access-programmers.co.uk...-table.315667/

    @Maruli -what is wrong with Arnel's solution? It would have been nice to mention your other post....

    Cheers,
    Vlad
    Yes, it has been answered. But it takes time for me to develop it. Yes...its true than arnelgp gave me the code... But I think the code is too advance for me and I still try to learn it

    Perhaps some of you could give me alternative code...the simple one

    But Arnelgp is spectacular

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 01-11-2017, 12:18 AM
  2. Import text file to table
    By anishkgt in forum Access
    Replies: 11
    Last Post: 10-19-2014, 03:52 PM
  3. Replies: 2
    Last Post: 02-11-2014, 09:38 AM
  4. Replies: 7
    Last Post: 11-20-2012, 05:08 PM
  5. Replies: 5
    Last Post: 12-01-2011, 05:38 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums