4 Replies Latest reply on Sep 29, 2014 12:59 PM by JosephMauriello

    Advice/Guidance: Importing data from flat file



      Advice/Guidance: Importing data from flat file


      Hi All,

      We've been using filemaker for almost a year where I work to keep track of participants in a research program. We previously used excel. When we first switched over to filemaker, someone simply imported the excel file into a single table. 

      Recently, we realized that we need a more robust solution. I volunteered, since I have some experience programming data driven web sites. 

      I've built a somewhat complex, multi-table solution that fills all of our needs, and now the time has come to import the old data. Is there a good way to do this, maintaining the relationships in the data without the need for doing it manually? 

      The old table has no reliable ID field (though I suppose I could make one?). Even if I did, my new solution has it's own ID convention that I'd rather not loose... I suppose I could use a calculation that created the id and use that to populate the Primary Key field in my new table, but then would I be able to add auto-incrementing once the import has been done? 

      Has anyone done something like this? Can you offer any advice? Thanks!

        • 1. Re: Advice/Guidance: Importing data from flat file

          To avoid manually linking the data after importing it, you'll need some sort of value in your imported data that reliably identifies which rows of imported data should be linked to a common record in another table. That might be a name, an email address or a value that you add to the data before you import, just to name some possibilities.

          Exactly what you will need to do will depend on the data you import, what tables need to receive data from that import file and how you've linked those tables in relationships.

          But none of this should require that you change your current, internally generated system of primary key values. You can always use a different set of relationships that match on data imported from the import file in order for a Script or a Replace Field Contents operation to assign Primary key values to foreign key fields of new records created by your imports.

          • 2. Re: Advice/Guidance: Importing data from flat file

            I'll only need to really deal with two tables. A parent and a child table. The old database is configured from the point of view of the child, so there was a lot of duplicate information. 

            I could use the phone number as my identifying field and just import that field into both tables. But then how do I link things together? 

            • 3. Re: Advice/Guidance: Importing data from flat file

              Well, I'm still working mostly in the dark here, but see if this example works for you.

              Let's say that you have two tables: Contacts, Details linked like this in your database:

              Contacts::__pkContactID = Details::_fkContactID

              where __pkContactID is an auto-entered serial number. Contacts is the parent and Details is the child.

              Your data you plan on importing is one row per future record in Details with the data intended for Contacts repeated in each row. One column of data in the Contacts specific data is a phone number.

              Do this:

              Go to Manage | Database | Relationships, click Details to select the Details Table Occurrence and click the duplicate button (Two green plus signs) to make a new occurrence of Details. (This does not create a duplicate table.) Add a PhoneNumber field to Details and link the new table occurrence in like this:

              Contacts::PhoneNumber = Details 2::PhoneNumber

              Go to Contacts::PhoneNumber on the Fields tab of Manage | Database and specify a Unique Value, Validate always validation field option. (you can later remove this if this is a one time import of data.)

              I'll describe the next steps as though you are doing this manually, but this process may also be scripted if you need to do this on a repeating basis.

              Go to a layout based on Contacts. Select Import records and import your data into Contacts. Map the fields in contacts to the columns of data as needed. When you import data, the unique values setting will automatically filter out duplicate values on the phonenumber field so you don't get more than one copy of each set of contact data. When you click that "Import" button, a small funky dialog will appear. Be sure to click the option to enable auto-enter options so that each new contact record will automatically get the needed value in __pkContactID.

              Switch to a layout based on Details 2. (You'll need to create this layout and select "Details 2" from the "Show records from" drop down.) Put the Details 2::_fkContactID field on this layout. (be sure to select this field from the list for "Current Table" (Details 2), NOT details.)

              Import Records again.

              Now click in the _fkContactID field and select Replace Field Contacts from the Records menu. (This is NOT Find/Replace in the EDIT menu.) Select the calculation option and specify that the value to be entered is Contacts::__pkContactID.

              This will copy over the needed primary key value into each of your newly imported Details records, using the phoneNumber field as a "temporary match" to match up each new child record to the correct parent record.

              This method is only as successful as your import data is consistent and "clean" in its record of that phonenumber value.

              Caulkins Consulting, Home of Adventures In FileMaking

              • 4. Re: Advice/Guidance: Importing data from flat file

                Took me a while to finally get around to trying this out, but that is exactly what I was trying to do.

                Thanks again, Phil!