5 Replies Latest reply on Dec 2, 2013 11:37 PM by keywords

    Help with importing data

    mhyman

      Hello,

       

      I am brand new to FM12, but experienced with databases in general.

       

      I'm trying to import a flat file database, an excel spreadsheet, that has name and address type data into an existing database that has several tables representing that data. For instance the addresses got into a specific table, the phone numbers into a different table, etc.

       

      I assume I need a script to do this, but I'm completely new, so I'm hoping someone can show me a script that pulls data from a spreadsheet or other flat file and imports the various elements into multiple related tables. Once I have an example, I think I can modify it to apply to my situation.

       

      I hope someone has a minute to provide some help, I really want to move forward but I'm stuck.

       

      Thanks in advance!!

        • 1. Re: Help with importing data
          Stephen Huston

          There are some sections of FMP12's built-in Help system which cover this.

           

          However, the trick is you will need to be sure that your Excel file contains some field which can be used as the relationship keys for maintaining the links between records in different tables after the data has been imported into FMP12. That link ID field needs to be imported into each table during the data import process, either as the primary key (in the parent table), or as the foreign key in each child table to which you import.

          • 2. Re: Help with importing data
            Stephen Huston

            Also, set up each of your imports to the different tables as separate scripts so you can store the import field matching within the script, and then use a master/mother script to call each of the separate import scripts in turn. (If you try to put all of the import steps in one script, there is a high probability that the field matching won't hold up reliably.)

            • 3. Re: Help with importing data
              lkeyes

              Hi, mhyman,  A couple notes:

               

              If this is a one-shot operation to convert or utilize data contained in the spreadsheet...then I'm not sure why it needs to be scripted...you can pick and choose which fields to import to which tables by using the FileMaker import function. 

               

              I concur with Stephen that you need to have the key fields.  If it were me, I think I'd do the following:

               

              1. Figure out what the keys are on the FileMaker side. For example, my guess is the reason things are in different tables in FM is because a single person may have more than one address or phone number.  So, presumably, there is a "person" table which contains fname, lname, and some kind of key field which uniquely identifies each person.  Then in the phone table, there may be a key field which uniquely identifies which row....but there must also be a field which contains the key from the person table. And so on. 

               

              2. Create a column in Excel which will contain a key for each Excel row.

               

              3. Add a key for each row in Excel.

               

              4. Import the names. including the key field.  

               

              4. Now, when you import addresses and phones include the key field as well as the data field that you are importing for each table in FM.  For example, if you are importing the phone numbers, to the phones table choose  a.) the key field and b.)the phone number field. And, obviously, you'll import the key field repeatedly to each table. 

               

              All this is actually a prerequisite to scripting in any case, as you have to figure out how the keys are derived for the person records in FM and then assign compatible keys in the spreadsheet before doing the import.  Are they autoID? (via FileMaker's autoID function...) or are they pieced together from the name field?

               

              --- L   

              • 4. Re: Help with importing data
                alquimby

                mhyman,

                 

                     The responses you have received from Stephen and Lkeyes are "spot on." I have had to import from client's Excel spreadsheets many times. The first thing I do is make a FileMaker file out of the spreadsheet (just drag the Excel file on top of your FileMaker application icon). Then I can change field names in the Source file to match the FileMaker Target file, clean up data (usually have to trim out leading and following spaces in each column (field), delete empty rows, create one or more relationships between the Source and Target based on common fields, etc. I find working with 2 FM files much easier than one FM and one Excel. One thing you didn't tell us is whether your Excel source contains brand new people that are not already in your Target, or whether you are updating data for people that are already in the Target, or a combination.

                 

                     If you are adding completely new people your job is much easier. Find the highest ID in your Target file, then in your Source file, create an ID field and add serial numbers starting at least one higher than the highest ID in the Target file. Then when you import into the various tables of your Target, your new IDs will establish your relationships. If your Source includes people already in the Target, you can create a relationship between the two based on a common field (email address is usually a good field to start with). Then in the Source table you can lookup IDs in the Target for those records that have matching emails. Sometimes I have to create several separate relationships (name to name, address to address, etc., and do several look ups to get as many Target ID's as possible in the Source.

                 

                     One thing I never do, is create a script to do the job. There are always many variables that make the import a "hands on" job that usually requires many steps.

                 

                Al Quimby

                • 5. Re: Help with importing data
                  keywords

                  I agree with Allen's notion of converting the spreadsheet into an FM file, with one alternate suggestion. If this is going to be a repeat operation from you could set up an Imported Data table within your target file into which you import all the spreadsheet data, make sure your match key fields are set up, then transfer the relevant data to records in your other tables.