Get external data - guidance needed

[Richio]

I am attempting a new project to add to an existing database and some initial guidance would be appeciated.

I am trying to import orders raised from a seperate "design" program. The program saves the order in note pad. I have played around with the "get external data" wizard and seems to be what I am after as I can get the (very) basics working but.......

1 In the notepad file I have to delete an address at the top and blank lines at the bottom in order for only the product code / description / qty lines to be picked up. Is there a way only to pick up certain data only?

2 In importing the data is there a way to add an extra field in repsect of the whole data - ie in this case a customer number (already in the main database) - one customer many lines to the order

3 Is there an (easy!) way to automate the whole procedure - Ie - click on a button and it will bring up a browse function to select the file - pick the file - and import / append to table automatically. At the moment there is alot of changes in terms of skipping certain fields and amending the layout of the field sizes each time I try to import.

I appreciate I am asking alot, so some guidance as to if it can be done and if I am thinking along the right lines initially would be appreciated.

Many thanks

Richio

 

[Fornatian]

I don't have the entire answer but I suggest that you import the data into a temporary table then add your customer number using an append query to move all the records from the temporary table into your main set up. This would then give you the opportunity to append whatever customer reference relates.

with regard to your query about selecting a file see the attached example.

with regard to your issues of deleting non-required data(addresses and extra lines), i would be minded to import the delimited file into excel then use a sub procedure built in another spreadsheet to do the housekeeping before actually importing the data into your db. the process flow I would follow is this:

1. open the delimited file in excel
2. delete header data in extra rows using predefined subprocedure
3. resave the data as a delimited text file
4. import the data into a temp table in your db
5. append the customer number whilst using a query to add the newly imported records to the real data table.
6. drink much coffee and read more on VBA.

all of the above could be achieved via excel by including excels reference library in the db and manipulating it's capacbilities from there.

make sense?

 

[Richio]

Many thanks

I think this one will keep me busy for a while......I am drinking coffee as we speak!

Richio