6 Replies Latest reply on Jul 26, 2011 8:14 AM by MichaelKaufman

    Trying to import excel data to populate a new database

    MichaelKaufman

      Title

      Trying to import excel data to populate a new database

      Post

      Lets say I have a database with the following structure:

      Table A             Table B          Table C

      --------             --------         ---------

      _pkA                 _pkB             _pkC

      A1                    B1                _fkA

      A2                    B2                _FKB

                                                 C1

                                                 C2

      I also have a series of Excel Files of the form:

      B1 B2 A1 A2 C1 C2

      B1 B2 A1 A2 C1 C2

      B1 B2 A1 A2 C1 C2

      Is there a simple way to populate the tables?  I suppose I could import all of the data into a new table and populate the other tables manually, but it seems like a lot of work for a script that is only going to be run once. 

      Any suggestions would be apreciated.  Thanks.

        • 1. Re: Trying to import excel data to populate a new database
          philmodjunk

          The key fields which aren't present in your Excel file will pose a problem here.

          You can import from your excel file 3 times--once into each table while selecting just the columns appropriate for that table. _PkA, _pkB and _pkC can auto-enter a value if they are serial number fields and you enable auto-enter options during import, but that still leaves _fkA and _fkB without the needed values to link C to the appropriate records in A and B.

          There are several ways you might solve that issue, but which one will work for you will depend on how the three tables in FileMaker should link to each other in their respective relationships.

          • 2. Re: Trying to import excel data to populate a new database
            MichaelKaufman

            Phil,

            Thanks for getting back to me. I had figured out about the multiple imports for tables a and b, its the foriegn key population I am trying to work out.

            For each _pkA and _pkB there will be at most one _pkC. The truth is I don't really even need a _pkC, because the relationship is unique on the two foreign keys.

            Any suggestions would be appreciated.

            Thanks, Michael

            • 3. Re: Trying to import excel data to populate a new database
              philmodjunk

              I'd need to know more about the data in your spreadsheet. When you look at that data, how can you tell which data will relate to which in your FileMaker tables A, B via a record in Table C?

              • 4. Re: Trying to import excel data to populate a new database
                MichaelKaufman

                Think about it this way. Every row in the spreadsheet is a row in table c. What I want to do is take the table A specific columns and use them to populate table A and the columns specific to table B and use them to populate Table B.

                ie:

                Spreadsheet with Name, Adress, Conference Name, Conference Date, Registration Date, Amount Paid

                would become:

                Table A           Table B                  Table C

                _pkA                _pkB                    _pkC 

                Name              Conference Name     Regsitration Date

                Address            Conference Date      Amount Paid

                                                                 _fkA

                                                                 _fkB

                • 5. Re: Trying to import excel data to populate a new database
                  philmodjunk

                  Notice how moving away from A,B,C makes this easier.

                  You may not be able to import your data and establish the links perfectly. You are at the mercy of how accurrately and consistently the data was entered into the spreadsheet.

                  Define an additional text field in A with this auto-entered calculation: Name & " " & Address. Set a unique values validation rule on it with the "validate always" option specified. Now, when you import your data into A, this validation will automatically filter out duplicate name/address combinations. Use the same technique for Table B to filter out duplicate combinations of conference names and dates.

                  Set up these temporary relationships:

                  TableA::Name = TableC::Name AND
                  TableA::Address = TableC::Address

                  TableB::ConferenceName = TableC::ConferenceName AND
                  TableB::ConferenceDate = TableC::ConferenceDate

                  Note that this requires temporarily defining all 6 fields in table C.

                  After importing your data you can use Replace field Contents on a layout to Table C to copy the PK values from tables A and B into the matching FK fields in Table C.

                  For TableC::_fkA, you'd put the cursor in this field and then use TableA::_pkA as the calculation needed to copy the matching value from Table A into table C.

                  Once you have successfully done this for both PK values, you can modify your relationships to use the primary and foreign key fields to link your records. You'll definitely need to review your imported data for issues due to similar names and addresses and data not entered exactly the same way each time.

                  Example: if I registered for two conferences, I might have registered as "Phil" in one of them and as "Phillip" for another. Given the above relationships, that would produce two records in table A instead of 1. You might be tempted to match just by address, but if my son, (Just for this example) Phil Jr. registered, he might be a different person, but with nearly the same name and exactly the same address.

                  • 6. Re: Trying to import excel data to populate a new database
                    MichaelKaufman

                    Phil,

                    That makes sense. I am mad at myself for not thinking of it. I must be getting old.

                    Thanks as always.