Import un-normalized data into relational db w/ one-to-many relations

[michelleb1]

There are several instances in which I have created one-to-many relationships and I'm not sure how to most efficiently extract and import the data from the old flat excel spreadsheets. For example, 'Jobs' was an excel spreadsheet with a field for a customer name. Now it is a table with a customer id to relate back to a brand new 'Customers' table. How can I substitiute the name with the correct id? The id in the customers table is an autonumber. I am dealing with close to 4000 records. A previous post: 'import excel spreadsheet and normalize it' suggested using append queries and macros but I'm not sure whre to go from there- any more detail on how to construct the append queries and macros?
Thanks in advance for any ideas,
Michelle

 

[MichaelRed]

generically, i try to always get info from the 'otherworld' into a database by linking or importing into a 'temp' recordset. from the 'temp', run whatever V&V is necessary, marking "what ain't right" (or it's logical negative). this can be at the field level, but i ususally try to do just a record level 'pass/fail' marking. "what am good" (the converse of "what ain't good", it is reasonably easy to just do 'ye olde' append query (or queries) with the necessary / appropiate selection criteria.

i'm pretty sure this doesn't REAllY answer your inquiry, but it migh at least give youy something to think about. for me, excel and legacy flat file residuals always seem to have dangerous 'sttuuuffff' which need to be filtered before admitting to any real / production app. i TRY to work on hte premise that grooming and filtering the aferent helps minimize problems in the remaining process.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[johnnymac43]

For the example given, and assuming that most of your excel data is clean, try the following:

> Import the spreadsheet into a temporary table.
> Add a CustomerID field to that table.
> Run an update query, setting the CustomerID field where there is a match on customer name with the Customer table (If customer name is not unique you will need to match on other fields as well).
> Copy the updated records into your permanent Jobs table and delete them from the temporary table.
> Manually correct any records remaining in the temporary table and repeat the update process (You may also discover that the new Customer table is incomplete).

HTH

John