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!!!