Jump to content

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


rkass068
 Share

This topic is 2886 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 2886 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.