Results 1 to 2 of 2
  1. #1
    jquickuk is offline Novice
    Windows XP Access 2003
    Join Date
    Mar 2009
    Posts
    4

    Angry Importing file into Access

    Evening all,



    I have a problem importing a file into access.
    I have a large .txt file which have fixed widths which looks like this

    000004945ORDERTRACK 20081001080042
    A51200180018190200001OK248601SA00120080930222807
    A51200180018190200002RX991601SA00120080930222807
    A51200180018190200003IO217601SA00120080930222807

    It goes on for ages and there is also a trailer record.

    Each Monday I receive 7 files for each day of the previous week and contains the cancellations of orders placed on a website.

    I need to import these records into a single table on a database. Currently I just use the 'Get external data' wizard on Access and I have a saved import specification which sorts out the record into the correct fields


    A5120018 001819020 0001 OK2486 01 SA 001 20090313 222807

    Date 20090313

    The date field on each record can go back as far as two months ago but I need to report on cancellations for each day.

    I have added a field on the table named 'InsertDate' with a default value date() but because I only get these files once a week I can't have the file from last Friday showing as today's date!!

    Is there a way of being able to import the file like I do at the moment then a box asking me to input the date which then adds it to all the records that I have just imported. I could do this 7 times and my problem is sorted.

    Or is there some VB script that I could use that allows me to navigate to the file, splits up the record to make it go into the correct fields then for it to ask me to insert the date.
    The Header record also contains the correct date so maybe there is a way to use that??

    So my table needs to look something like this.

    A5120018 001819020 0001 OK2486 01 SA 001 20090313 222807 20090318

    Date 20090313
    Insert Date 20090318

    i've been playing around for ages but no luck

    Hope someone can help!!!

  2. #2
    thhui is offline Competent Performer
    Windows XP Access 2002 (version 10.0)
    Join Date
    Feb 2009
    Posts
    235
    You may use vba code to do the import instead.
    Then you can write vba code to run the query to update the date field after the import.

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

Similar Threads

  1. Importing Excel into Access with few requirements
    By pickolizac in forum Import/Export Data
    Replies: 1
    Last Post: 02-18-2009, 12:02 AM
  2. Importing Xls and CSV files into MS Access
    By Jeff in forum Import/Export Data
    Replies: 1
    Last Post: 02-17-2009, 11:56 PM
  3. Issues with dating when importing excel file
    By Lainie in forum Import/Export Data
    Replies: 0
    Last Post: 01-22-2009, 10:50 AM
  4. Importing Excel Sheet into Access dbase
    By tonystowe in forum Import/Export Data
    Replies: 0
    Last Post: 12-08-2006, 11:35 AM
  5. Importing a tab delimited file into access table - please he
    By championss in forum Import/Export Data
    Replies: 0
    Last Post: 10-29-2006, 02:33 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
  •  
Other Forums: Microsoft Office Forums