Importing from excel script-data to join table and other related tables

[rkass068]

Sorry, I could not find any post with the same problem I have so I decided to make a new post. Here is the problem I am having:

 

I have a database with 5 tables

 

Table1: Main

 

Table2: Entries

 

Table3: Labs

 

Table4: Treatment

 

Table5: Events

 

The database is related in this way:

 

Main: pk_main----->Entries:fk_main

                                Entries:fk_Lab<----------Lab:pk_Lab

                                Entries:fk_treatment<--------Treatment:pk_treatment

 

The other table is related the same way. In other words, entries is a join table in order to display the information in report/summary of all the labs, treatment and events the 1 patient had.

 

Every thing works well and the database has over 7000 records in it right now. Now the docs are able to get a spreadsheet for each patient that has all of their lab results and we would like to import them into the database without having to manually input them. For example, they will get a spreadsheet of all "John Smith's labs". I want to make an import script that imports all those records. Each spreadsheet has a date and the lab data. The date is in the Entries table and the lab data is in the lab table. How do I add these records while maintaining the primary keys and foreign keys since the spreadsheets do not come with either? Or will I have to manually make primary keys and foreign keys first?

[Kris M]

You could import the data into a table specifically constructed to house the imported spread sheet data then write scripts to move the data to where it needs to go and populate relationships appropriatley. Only problem is that if the spreadsheet format is ever changed then your routine will have to be edited.

 

If you are producing the spreadsheet from FM then it should be easy to add the required relational field data to the .xls file to make the import/move/massage routine fairly easy

[rkass068]

Thanks Kris, this sounds good but how would I be able to transfer the records in the import table to the actual database tables I use? This sounds like a difficult script

[Kris M]

I didnt say it was going to be easy just possible. You will have to understand what data comes in, where it should go, how it should be edited to fit the destination, and how to populate missing entities to ensure data integrity.

[rkass068]

alright thanks