Importing data

[jquickuk]

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

 

[jdraw]

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

I think you can make things a little easier. However, there are a few questions first.

What is in the header, and trailer records?
Do you apply your data against an operational database, or are you storing the transactions that were received from the website?

You said you have import specs for the process and these are Fixed Width records, but the Header record is different than the data. How do you handle that?

How proficient are you with VBA?

Can you tell from the file names which records to be imported go with which date?
Have you ever had imports that failed for some reason? Is that an issue? Can you delete partial records and rerun the import?

What happens to the files of records to be imported after you do your thing?

Can cancellations from one date be overridden on a later date (within your weekly files)?

Anyway, this is not meant as criticism, it's just trying to understand where all this fits.