Need some help with data import

[hpl2001]

Hello. I'm hoping someone can point me in the best direction for this task. I have a bunch of excel spreadsheets that I want to import into a database I've set up with 3 tables.

The project involves managing boxes of files, so I have the follwing tables:

A File table containing information about the individual file, including the number of the box it's in.

A Box table with box information including the facility it came from.

A Facility lookup table.

What's my best strategy to automating the population of these tables from my flat excel spreadsheets?

TIA.

Holly

 

[randy700]

Are the spreadsheets all similar? Same column names, etc?
How about...
1. Import all spreadsheets into 1 table.
2. Use append queries to copy from the "master" table to the other 3 tables.
3. Delete all data in the master table.


Randy

 

[hpl2001]

Thanks Randy, they are. I guess the part where I'm stuck is how to split the data across the three tables and create the links required.

 

[randy700]

How about this?

When you create your master table, include an autonumber key field. Use that key field as a key field in each of the other three tables, then link them based on that.


Randy

 

[hpl2001]

I think I'm not explaining this so well. I know how to link tables. But let's say a row in my spreadsheet has a file name, a box name and a facility. When I import ths data I'll want the filename to go into one table, the box name into the second, and the facility name into the third. If my box table is autonumbered, I will need an entry in my field table to tell it which box to point to. As I'm processing that one row of the spreadsheet, how do I maintain that connection between the file and it's corresponding box, if I'm using an autonumber. And Randy...thank you very much for repsonding.

 

[randy700]

Assume your spreadsheet has the following data:
FILE100 BOX111 FAC151 other fields
FILE200 BOX222 FAC252 other fields
FILE300 BOX333 FAC353 other fields

Import the spreadsheet to MasterTable with Autonumber:
1 FILE100 BOX111 FAC151 other fields
2 FILE200 BOX222 FAC252 other fields
3 FILE300 BOX333 FAC353 other fields

Now, using append queries, move data to tables WITHOUT autonumber fields.
You'll be moving the autonumber field to a numeric ID field.
FilesTable
1 FILE100 other fields
2 FILE200 other fields
3 FILE300 other fields

BoxTable
1 BOX111 other fields
2 BOX222 other fields
3 BOX333 other fields

FacilityTable
1 FAC151 other fields
2 FAC252 other fields
3 FAC353 other fields

Finally, run a delete query to remove everything from your MasterTable.

The next time you import, the master table will begin the auto numbering process at 4.


Randy

 

[hpl2001]

Awesome. Thank you so much.