2 Replies Latest reply on Jan 13, 2014 7:13 AM by EgbertRoos

    Importing fields from Excel question



      Importing fields from Excel question


           I have this excel sheet where information on over 100 hospitals, 15 departments and (about 20) fields for department specific information is stored.
           Hospitals are in rows, the department fields are in columns but they are grouped in blocks of 15 columns for 15 departments.
           So the first column defines the hospital, the second to 16th column give the values for field 1 for the 15 departments, 17th til 31st column hold the value for the second field for the departments etc. So in fact there are 3 dimensions put into a 2 dimensional sheet.
           In FM I have a table with hospital names (among other fields) and a table for the departments containing one of the 15 department names and the 20 department fields for that specific department. There is a one-to-many relation between hospitals and departments.
           Does anyone have an idea how to import the proper values for all fields for each department (of each hospital) from this Excel sheet?

        • 1. Re: Importing fields from Excel question

               Are you sure that is is actually "one to many" and not "many to many"? If you need 15 columns (fields) for each reference to a department, what kind of data is there in that group of 15 fields? Is any of this data that is specific to the pairing of a particular hospital with a particular department?

               In otherwords, let's say one of your departments is named "radiology". If you check the group of fields for "radiology" for one hospital, is some of the data recorded different from what is recorded for "radiology" for other hospitals with that department? If so, you might have a many to many relationship needing three tables rather than two. The next thing to check is whether there is data about "radiology" in that set of 15 fields that is always  the same for every hospital that has a radiology department. If you have both kinds of data here, it would appear that you have a many to many relationship.

               Whether or not you have a many to many relationship, there are two basic approaches you can consider for importing this data:

               1) import the data once into a single table with all needed fields to receive all columns of data from the excel file. After importing, you move the data from this table into you Hospital, department and (possibly) a join table.

               2) import the data multiple times, specifying different columns for import each time. The first import might populate your Hospital table. Subsequent imports might specify a different range of Hospital + 15 columns for populating the departments table.

          • 2. Re: Importing fields from Excel question

                 I created the one-to-many relation because a hospital can have each of these 15 departments. On the other hand a department will belong to only one hospital since the fields contain info on e.g. number of staff, expected attrition of personnel, number of people currently on education, etc. All data which is only valid for a certain department in a certain hospital (Dept 1 e.g. Radiology in Hospital A) which will differ from another department (with a different discipline) within the same hospital (dept 2 in hospital A) as will it differ from another department (with the same discipline e.g Radiology) in another hospital (dept 1 in hospital B).

                 BTW I have indeed a third table where all 15 department names are stored. This is a one-to many relation as well: one department name can occur in several departments (which will be in different hospitals).

                 Thanks for your suggestions on importing data. I will give it a try.