2 Replies Latest reply on Mar 26, 2014 3:23 PM by keywords

    Importing excel data into two related tables


      Hi Friends!



      Can anyone tell how to import data from excel into two or more related tables/layouts???



      Thansk in Advance,


        • 1. Re: Importing excel data into two related tables

          You can't easily. You can only import into a single table. You might find that the best way to do it is to create a temporary table, import into that and then start moving the data around via a script.

          • 2. Re: Importing excel data into two related tables

            I recently completed a task similar to this, breaking data out from an old single table database into a new multi-table version. Here is a brief description of the technique I used.


            As suggested by David, I would probably start by importing everything into a single temporary table and then move data from there into the final multiple tables. This gives you the facility to have each record in the temporary table tagged with a unique ID, which probably doesn't exist in excel.


            The key to success is to then create a temporaryID field in each of the final tables and import the ID from the temporary table into this field on each table, together with the data fields relevant for that table. Of course each table must also have its own unique ID field. The temporaryID can be then used to make a temporary match between tables to the bring across the real ID. Once that is in place you can reset relationships to use the real IDs, and eventually the temporary ID can be deleted once you are satisfied you no longer need it.