Developing DB from unformatted excel files?

[buliwyf]

Approx total 9,000 rows, 35 columns "query" type spreadsheet in DIFFERENT excel (.xls) files, with different column arrangements. NO ID's (primary key) for any record. Basically, they're spreadsheets created by non-IT people, usually managers/high level employees (you get the point).

Normalizing it, i get to split it into 4 tables. Ok cool I have a good DB design and all, but how do I compile them all and actually migrate/populate them to a RDBMS (sql server 2000, or heck even access would do) in order?

Has anyone of you gone through this kind of start? Im a young dba in his early career stage. I get to learn all those technical stuff in school and training and all, but they dont teach this hehe.

Just want to get tips on how to "prep" data to get organized. thanks

 

[it@flude.co.uk]

Quick Answer:
1. Save the excel files off as Text (Tab Delimited) files, one per worksheet
2. Set up a Progress program to import these into a series of temp-tables
3. Depending on the fields, their order and which data you want, re-export the relevant data into a series of tab-delimited text files, preferably in the same format
4. Import these new tab-delimited text files into a single set of temp-tables
5. Create your data based on the new temp-tables

As to the specifics, it would depend on what the excel spreadsheets contained.

Hope that helps

Simon