5 Replies Latest reply on Jan 25, 2013 2:07 PM by philmodjunk

    Importing from Excel

    donl1150

      Title

      Importing from Excel

      Post

           I have an Excel spreadsheet with data such as Company Name, Address, City, State, Zip, Phone, web site etc.  In the same spreadsheet, I may have many contacts i.e. last name, first name, title, e-mail, phone, etc. that are employed at the same Company.  Obviously, there could be a lot of duplicate information as relates the Company data.

           My FileMaker Solution has an Account tab and also a Contact tab which relates to the Account.  There can be many Contacts for the same Account.  How can I do an import to the FM solution so the Contacts are properly imported to the Accounts they belong with?

           Below are screenshots on how the Account and Contact tabs appear:

      screenshot.JPG

        • 1. Re: Importing from Excel
          philmodjunk

               How is the data structured in the Excel spreadsheet? Does each row in the spreadsheet identify a single contact does each row represent a single account with contacts listed in columns of that row?

               Depending on how the data is structured in the spreadsheet you may be able to import the data twice, once into each table mapping different fields to different columns for each of the two imports and with a validation rule on accounts to filter out duplicates. Or you may have to import the data into an interum table and use a script to merge the data.

               How do you link accounts to contacts in your database?

               Is the value, such as an AccountID number one of the columns in your spreadsheet data?

               Is there some other column in the spreadsheet that can be used to identify accounts for each contact?

          • 2. Re: Importing from Excel
            donl1150

                 In the spirit of full disclosure, I will tell you I know very little about the workings of FileMaker.  This solution is a free one from Richard Carlton Consulting that I have been using for some time now.  I have, until now, gradually built my database, one record at a time as needed.  This has served me well but now, I now want to put it to a new use and thus it invovles this importing of data.  I will try to answer the questions below:

            Does each row in the spreadsheet identify a single contact?  Yes

            Does each row represent a single account with contacts listed in columns of that row?  Yes

            Depending on how the data is structured in the spreadsheet you may be able to import the data twice, once into each table mapping different fields to different columns for each of the two imports and with a validation rule on accounts to filter out duplicates.   With my lack of FileMaker knowledge, this would be a struggle.

            Or you may have to import the data into an interum table and use a script to merge the data.  Ditto this one.

                 How do you link accounts to contacts in your database?  The FM solution handles it for me.

                 Is the value, such as an AccountID number one of the columns in your spreadsheet data?  No

                 Is there some other column in the spreadsheet that can be used to identify accounts for each contact? Yes

            • 3. Re: Importing from Excel
              philmodjunk
                   

              Does each row in the spreadsheet identify a single contact? Yes

                   

              Does each row represent a single account with contacts listed in columns of that row? Yes

                   Sorry, but these are mutually exclusive questions. It's one or the other, but not both. If each row represents a single contact, you would not alos have multiple columns to list the individual contacts. Either one row is one contact and the account info is repeated for each contact or you have one row for each contact and cells within that row list the contacts.

                   

              The FM solution handles it for me.

                   Sorry, but you'll need to do better than that. There is more than one way that FM can "handle it for you". Without knowing the exact method used, I can't tell you how to import your data so that the correct data is imported into the correct place.

                   

                        Is there some other column in the spreadsheet that can be used to identify accounts for each contact? Yes

                   And what data is that?

              • 4. Re: Importing from Excel
                donl1150

                     Excuse my confusing answers. I do appreciate your trying to help out.

                       
                1.           Each row contains specific contact info for that person. Columns include; first name, last name, e-mail, title, company name, address, city, state, etc.
                             
                2.      
                3.           Not sure how to describe the relationships, but perhaps this screen shot from Manage -> Database will help.
                             
                4.      
                5.           The unique identifier in the Excel spreadsheet is a 14 digit number that was generated from the sales lead software that I use to download sales leads.

                      

                      

                • 5. Re: Importing from Excel
                  philmodjunk

                       Am I correct that the identifier that you mention in 3 above does not have a corresponding field in your database? I will assume your answer is "yes".

                       The screen shot of your relationships is very helpful. It identifies the match fields used in your relationships: ID_Account

                       This looks like something that can be imported into your database with only a few changes made to use that ID column from 3 as a temporary way to match entries and also to prevent duplicate entries in your Accounts table.

                       Make sure that you have at least one back up copy of your file before you begin. That way, if you get in trouble, you always have the option of discarding the copy you are using to make these changes to get back to what you had before you started.

                       Add a field in Accounts for this ID number. Open Manage | Database | Fields, select Account from the tables drop down, enter ID_SalesLead as the account name, select Number (unless these account numbers include letters) and click Create. Then click the options button to open field options for this new field.

                       Select the validation tab. Click the Unique Values and Validate Always check boxes. Then click OK to get back to the Manage | Database window. Add this same field to the Contacts table, but do not specify the validation options because duplicate values in this field are required in the contacts table.

                       Click the relationships tab. Select one of the boxes in this window that refer to the Contacts table such as the one that starts with "T01c_..." Click the duplicate button (2 green plus signs) to make a copy of this table occurrence. You can double click this new object to open a dialog where you can rename it if you want. Click the control in the upper right corner until the list of fields are visible. Drag from ID_SalesLead in T01_Accounts to ID_SalesLead in this new occurrence of Contacts.

                       Click OK. to dismiss Manage | Database. Enter Layout Mode. Select New Layout. Select the name of your newly created Contacts table occurrence in the "Show Records From" drop down list. For our purposes, it does not matter what other options you select so you can select the option for a blank layout if you wish.

                       With a blank layout, you can still use Table View to get an overview of your data. Enter Browse mode and use the Modify button to add some fields from your new occurrence of Contacts to your layout. Be sure to include the ID_Account field.

                       You are now ready to import records from your Excel file.

                       Select Import Records from the File menu. Find and open your excel file. Map the columns of your excel table to corresponding columns in your contacts table. Be sure to map your Account ID number to the newly added ID_SalesLead field. There are probably columns in your excel file that will not be mapped to a field in the contacts table due to being data needed in the Account table and you will import that data after you import this data. Be sure to click the check box that enables auto-enter operations before importing your data. This check box appears in a small dialog box that pops up after you dismiss the field mapping dialog.

                       Change layouts to a layout based on T01_Accounts. In Layout mode, the "Table:" designation in the status tool bar will identify the table. Now Import records again from your Excel file, but now you are importing into the accounts table. Map fields as appropriate. Once again, enable auto-enter operations. You'll get a dialog that likely will tell you that not all records were imported. This is OK. This is because the "unique values" validation you specified is filtering out duplicates so that each set of account data is imported only once.

                       You are now almost done. The last step is to link your new contacts to the correct new account record.

                       Return to the newly added contacts layout.

                       Put your cursor in one of the fields in the ID_Account column. Select Replace Field Contents from the Records menu. Select the calculation option for Replace Field contents. For a calculation, enter T01_accounts::ID_SalesLead. Click OK until you are back to  your layout and your records are updated. This step copies over the ID_Account serial number so that your new contacts records are correctly linked by ID_account and not just by the ID_SalesLead pair of match fields.