Results 1 to 14 of 14

Importing text file into Access Table

  1. #1
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7

    Importing text file into Access Table

    Hey everyone. Can anyone help me come up with a script that will allow me to import a text file that's in the format shown below? I'm trying to import each text file field item into a separate field in the database table "Data Archive". For example, the P.O box number which in this case is 105243, would be imported to the box number field in the table. The site number 12 would go into the Site Number field. The transaction code 4101 would go into the Trancode field. And fianlly the volume 00000000000009552 would go into the volume field in the table.

    The text file has spaces between each line which is what is giving me a fit. Please look at the file layout and example below. If anyone can help please do.

    Text File Layout:

    a = Lockbox #: (6) digits and (4) leading spaces. Postions 1 - 10
    b = Site ID: (2) digits and (8) leading spaces. Postions 11 - 20
    c = ISA #: 10 spaces. Postions 21 - 30. Not used currently
    d = Cust #: 5 spaces. Postions 31 - 35. Not used currently
    e = RG Key: 11 spaces. Postions 36 - 46. Not used currently
    f = Tran Code: (4) digits (4) spaces. Postions 47 - 50
    g = Record #: 4 spaces. Postions 51 - 54. Not used currently
    h = Count #: 2 Spaces. Postions 55 - 56. Not used currently
    i = Volume (17) digits, zero padded to the left of volume. Postions 57-73
    j = Dollars (14) digits, zero padded to the left of volume. Postions 74-87
    k = Description: 240 spaces. Postions 88 - 327
    l = GL #: 23 spaces. Postions 328 - 350
    m = Adjustments: 3 spaces. Postions 351 - 353

    Text File Example:

    105243 12 4101 00000000000009552

    105243 12 4102 00000000000000325

    105243 12 4108 00000000000043838

    105243 12 4110 00000000000043838

    105243 12 4205 00000000000000002

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    If you attach a sample of the TextFile maybe someone would create the script for you.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7
    Thanks for Responding. I have the text file but I don't know how to attach it using this forum. How do I do that?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    Press the Post Reply button and scroll down to the Manage Attachments button.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  5. #5
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7

    Importing text file into Access Table

    Thanks! Here is the text file in question.
    Attached Files Attached Files

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    Is this a one shot deal or will you be doing it repeatedly? Have you already tried the inport wizard as all text fields and then run an update query to trim all of the fields?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    This sample db should get you started.
    Attached Files Attached Files
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7
    Thank you so much!

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    You're welcome. Are you ready to mark this thread as Solved yet?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7
    Well, I thought that I was ready to close this thing out but I was thrown a curve ball. I had to alter the script to now accomdate a comma delemited text file. Everything works fine except for the fact that my script is skipping the first line in the file. What in the world am I missing that? Here is my script.



    filnam = Me![Text2]
    Set objFile = CreateObject("Scripting.FileSystemObject")
    Set objText = objFile.OpenTextFile(filnam)
    Set rs = CurrentDb.OpenRecordset("FlatFileImport")
    once = False
    If Not IsNull(Me.Text2) Then filnam = Me.Text2
    Open filnam For Binary As #1
    'Do While Not objText.AtEndOfStream
    Do Until objText.AtEndOfStream
    strTextLine = objText.Readline
    Data = Split(strTextLine, ",")
    If once Then
    rs.AddNew
    rs.Fields(1) = Me.TheSiteID 'Site ID
    'rs.Fields(1) = Data(0) 'Site ID
    rs.Fields(2) = Data(1) 'Client ID
    rs.Fields(3) = Data(2) 'Client Name
    rs.Fields(4) = Data(3) 'DDA Account
    rs.Fields(5) = Data(4) 'Process Date
    rs.Fields(6) = Data(5) 'Good Checks Singles

    rs.Update
    End If

    once = True
    MyLine = ""
    MyLine = MyLine & ch
    MyLocation = Loc(1)
    Loop

    Close #1 ' Close file.
    rs.Close
    'close and erase the file from memory
    objText.Close
    Set objText = Nothing
    Set rs = Nothing

  11. #11
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7
    Okay, I just figured out what what happening. I had an if statement that was causing the script to skip the file line in the file. Once I removed it everything started working properly. Thanks guys!!

  12. #12
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    Excellent! So this thread is ready for the Solved thread tool right?
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  13. #13
    Anthony is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2009
    Posts
    7
    Yep! We can close this puppy out!

  14. #14
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    10,862
    It's your thread so just follow the link in my sig.
    (RG for short) aka Allan Bunch MS Access MVP - WinXP Pro, Win7 Pro - acXP, ac07, ac10, ac13
    If your issue is resolved...go ahead and use the Thread Tools at the top of the thread and mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

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

Similar Threads

  1. copy data from text file into table in access
    By miziri in forum Programming
    Replies: 3
    Last Post: 08-12-2009, 02:02 PM
  2. Importing Txt file via Vb
    By jquickuk in forum Import/Export Data
    Replies: 1
    Last Post: 04-01-2009, 07:27 AM
  3. Importing file into Access
    By jquickuk in forum Import/Export Data
    Replies: 1
    Last Post: 03-23-2009, 08:18 PM
  4. Importing a tab delimited file into access table - please he
    By championss in forum Import/Export Data
    Replies: 0
    Last Post: 10-29-2006, 01:33 AM
  5. Importing a table created in word to Access
    By anthony_f_justin in forum Access
    Replies: 4
    Last Post: 01-04-2006, 07:06 AM

Posting Permissions

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