5 Replies Latest reply on Dec 5, 2012 12:05 PM by philmodjunk

    Importing from excel

    EP

      Title

      Importing from excel

      Post

           I have a contacts database.  In this database, I want to import information related to each contact from another system via excel.  I created a new table for the import.  I created a contactID MatchField in the new import table and linked it to the Contact's ID field (serial #) via a relationship.  When I import the information, how can I make sure that it links to the correct contact?  This imported information will be displayed in a portal on the Contact's layout.  Thanks,

        • 1. Re: Importing from excel
          philmodjunk

               What data in the original excel file identifies the contact record in your database file to which it should be linked?

               Without some such data in the the Excel file, there's no way to do this on an automatic basis and could well be impossible to do manually.

               Matching by contact names could be especially troublesome due to the possibility of two contacts with the same name and the fact that the same contact may have its name spelled or entered in more than one way (such as entering "Jon" for a first name in one place and "John" as the first name in another.)

          • 2. Re: Importing from excel
            EP

                 What data in the original excel file identifies the contact record in your database file to which it should be linked?

                 A FullName field matched with the Name field in the import file.  I don't have any other way of doing this unfortunately, so I have to match by name.

            • 3. Re: Importing from excel
              philmodjunk

                   Then your matching may match the wrong records or fail to match when they should. It depends on how "clean" is your data in the excel file.

                   Assuming perfection in the imported data...

                   You can create an additional occurrence of your contact table and use fullname as the match field. Immediately after importing--either by hand or in a script, you can use Replace Field Contents with this relationship to copy over the Contact ID from the contact table. You can also set up the ContactID field in your child table to auto-enter this value via the name based relationship during the data import if you enable auto enter operations during the import. (There's a check box for that that appears in the very last little dialog box just before you initiate the records import.)

                   But there will be no subsitute for manually inspecting your data and linking the records if your data is "messy"--with data entry errors or name variations such as including or omitting name details such as "Jr". And with duplicate names, there may be no way to link the records even when you personally inspect the data.

                   One thing that you will be able to do is find all records in the imported data that fail to match to a contact record by name so that you can inspect them and resolve any possible issues that kept them from linking to a contact record.

              • 4. Re: Importing from excel
                EP

                     Ok, everything is imported to the table.  How exactly do I use Replace Field to pull in the Contact ID from the contact table, via a calculation?

                • 5. Re: Importing from excel
                  philmodjunk

                       Do you have the necessary relationship in place?

                       If so....

                       Immediately after import, the found set of records in your table will consist of the records that you just imported. Replace Field Contents will update a specified field for every field in the found set.

                       Put the cursor in the ContactID field.

                       Select Replace Field Contents from the records menu. (NOT the Edit menu, Find/Replace is a different tool.)

                       Select the calculation option and enter a calculation similar to this:

                       MatchByNameContacts::ContactID

                       but use the table occurren of your newly added occurrence of Contacts instead of MatchByName.

                       Peform the Replace.

                       Replace Field Contents can modify huge numbers of records all in one batch operation and cannot be reversed. Thus, you may want to save a backup copy of your file before attempting to use this tool.