I need help to migrate denormalized table content to normalized form- MS Access

Question

I have a staff database where we are storing information in Normalized form. There are 3 entity tables and 2 mapping tables connecting these with m-n relationships

However, for ease of collecting this information, we are gathering this in a flatfile in excel.(Employeeid, Fname, LName, ITskills(in multiple rows), projectName)

Our plan is to import this flatfile into MS Access into a Staging Table and then write a query to read the staging table and insert the equivalent records in the 5 tables- and creating the identifier keys systemically. (Employee, Skills, Project, employeeSkillsMapping, skillsProjectMapping)

We tried converting from flat-file to 5 tables in excel manually and importing- getting very challenging to implement the relationships. Any suggestions/ links on how to get this done- how will the keys get generated systemically in all the tables.

Answer 1

I don't really have any links, but I can give a general how-to of this:

After importing into Access: - A series of update scripts (or a single procedure, really) that copy the data into the appropriate tables - A purge statement clearing out the temp table for the next upload.

It sounds really simple, and it (actually) shouldn't be too difficult, but without knowing your table structure, it's hard to give true specifics.

Are the skills and projects using defined language? if so, this greatly simplifies the job.