Import Data from Excel into an Existing Application

So you've found a wonderful application template and can't wait to use it. All you need to do is get your precious data out of Microsoft Excel and into your new QuickBase application.

In order to do so, you should understand that Microsoft Excel and QuickBase handle your data in very different ways. Excel is a flat data model. In other words, it's really just one long list of items on a spreadsheet. QuickBase, on the other hand, is multi-dimensional. This means that you should take the data that lived in a single spreadsheet and split it into multiple lists, where appropriate.

In QuickBase, these multiple "lists" are called tables. The trick, when moving from Excel to QuickBase, is to know which columns belong in which tables, and then to create relationships between the tables.  This brings you to Step One in the import process.

Step One: Decide which Excel columns belong in which QuickBase tables

This step could also be titled "Make a plan." Since you're splitting your data out into multiple dimensions, figuring out where everything belongs may take some thought. In fact, there's a whole separate topic on the subject. Read how to structure your application.

  1. Tip: If you don't need to get your data into an existing QuickBase application, you can save yourself some work. You can create a new application and import your entire spreadsheet into a single QuickBase table. Then split it out into multiple tables afterwards. This process is much easier than importing into an existing template or multi-table application. So, unless you require the special features of an existing application, import from Excel and create your application at the same time. Read how.

The Big Picture

Importing data from Excel sounds simple at first. Say you have a list of contacts and companies like the one in the following figure. Since Company information should live in one table, and Contact-related information (like First Name and Last Name) belongs in a different table, you'll need to do two imports, each into a different QuickBase table. But, how do you know which contacts belong to which company? For instance, in the illustrated spreadsheet below, John Smith is related to the company Smithco. Angie Stone is too. In order to maintain both these connections, you must import columns from your Excel spreadsheet in a specific order. You must start with the master table. The master is the "one" in the one-to-many relationship between tables. (Read more about relationships.)

import_existing.gif

This illustrates a rough overview of an import into existing tables. To import data into two tables that are already related to each other, you do two imports. First, you'll import master information, which in this case is the Company column. Then you'll match up related values (either in QuickBase or Excel). Next, you'll import detail records (like contact details from the First Name and Last Name fields. When you import the detail columns you'll include a key column of master values in order to maintain relationships between each contact and his or her company. Read on to learn how.

Step Two: Clean up your Excel Spreadsheet

For best results when importing data from Excel, your data must fit the structure that works well in QuickBase. By cleaning up before import, you save yourself work later. Read Prepare Excel Data for Import for more information.

Step Three: Import Master table data

After you've cleaned up your data, you're almost ready to import. As you've already read, you need to import data to the master table first.

To import data to the master table:

  1. In Excel, filter for unique values on the column you're about to import (again, this must be the master).

  2. You don't want to create duplicate records in your QuickBase application. So the first step is to reduce the master list down to unique values. To filter in Excel, highlight the column then select Data > Filter > Advanced Filter. The Advanced Filter dialog box appears. Turn on the Unique records only checkbox and click OK. Excel then shows only one instance of records that previously had duplicates.

  3. Clean up inconsistent entries.
    See if you have any entries, like company names, that were typed inconsistently (see the figure below). In order to get QuickBase to recognize these values as the same company, you must clean up typos and anomalies to make names consistent.

    Smithco and Smithco Companies are really the same company. You must make entries for the same items identical.

  1. Filter the column again (following the instructions in Step a) so that you again see a list of only unique values in the master column.

  2. Copy master record-related columns.
    Select the master column as well as any other master-level columns. For example, you'd copy the Company column as well as any other columns that contain company-level information, like address, main telephone number or Web site URL. To select multiple columns, hold down the Ctrl key and click on each column heading. After you select the columns, right-click and select Copy.

  3. Import these fields by pasting them into the desired table.  Read how.
     

Step Four: Match detail records with master records

The Excel spreadsheet you're working from lists master values (like company name) alongside detail values (like contacts). In QuickBase, these values will live two separate tables. So how do you know which contact belongs to which company? To keep this information related, you need to populate the detail record's reference field with the corresponding values from the master table. You'll keep this connection in place by doing one of the following two things:

Change the Key field of your new table

Once you have your list of master records in QuickBase, you can change the key field of that table. Doing so will let you maintain the relationship between the master list (like Companies) and the list of detail records (like Contacts) you're about to import. Essentially, you're changing the key field to values that match values in your Excel spreadsheet. Doing so gives your next import a hook to connect detail records with their master values.

BE CAREFUL: If you have data in your master table that existed prior to the import you conducted in Step Three, you may not want to change the key field. Key field changes disrupt existing relationships and may result in a loss of data. If you're worried about losing existing data, then there's a safer way. Instead of changing the key field, export the master list to Excel and match records using VLOOKUP (the other option discussed within this step).

In QuickBase, change the key field from the Record ID# to the master field you imported (the field that was the column of unique values that you filtered in Excel). For example, if you've imported a list of companies, then change the key field to the Company Name field. Read how to change the key field.

Export master records to Excel and Match on Record ID using VLOOKUP

If you have data that existed in your application prior to these imports or if you need to import several tables with complex relationships, changing the key field isn't always feasible.

For example, say you've come back from a trade show and you just want to import new leads and relate them to companies you already have in your Companies table. If that's the case, you'll want to match leads with your companies in Excel, prior to import.

To do so, you'd export master information (in this case a list of companies) from QuickBase to Excel and then use a special Excel function called VLOOKUP to match companies with leads.

After that you can import the leads into the contacts table along with the company's Record ID# (which you'll use to populate the reference field that matches detail records with a master).

To export master records to Excel and Match on Record ID using VLOOKUP:

  1. Export two fields from the QuickBase master table: A field that you can use to match values in your Excel file, like Company Name and the key field (usually Record ID#). (Read how to export to spreadsheet.)
    vlookup.gif
    Here, values form the Company Name and Record ID# fields have been exported to an Excel worksheet attached to the worksheet that holds detail records that will be imported to QuickBase next.

  1. Make sure the field containing values you want to search for is sorted field alphabetically (or numerically). For example, if you want VLOOKUP to match values in the Company Name column (column A in the figure above), you'd sort that column alphabetically.

  2. Within Excel, create a column that uses the VLOOKUP function to match master values you exported from QuickBase with detail records in your Excel spreadsheet.

  3. The VLOOKUP function matches values for you. The following figure shows how it works. Using the VLOOKUP function usually requires someone with some expertise in Excel. Read more about VLOOKUP on the Microsoft Web site.

  

vblookup3.gif
Cells in the Company ID column (Column I) contain VLOOKUP functions that match the value found in Activities tab's Column B with a value that appears on the list of companies imported onto Sheet 1. When VLOOKUP finds a match, it returns the corresponding QuickBase Record ID# from Sheet 1 (refer back to step 1 to see how the numbers match company names). The function in cell I14 is highlighted and displays in the toolbar above the spreadsheet. In English, this formula says: take the value in B14 (this value is different in each I column's cell to match its own row) and search Sheet1 cells A2 through B12. Where you find a matching value, return the corresponding value in column two of the search area (which is Sheet1's
column B).

Step Five: Import the detail table into your application

Now you're ready to import values that belong in the detail table.

To import the Detail table:

  1. Return to Excel, if you're not there already.

  2. Remove all filters from your data.

  3. To view all data rows again, select Data > Filter > Show All. Excel displays all your data.

  4. Copy columns for import.
    Select all the columns that contain detail information. For example, if you're importing contacts to go with companies, you'd select First Name and Last Name field as well as columns that contain information on each contact like Direct Dial Telephone and email Address, for example.
    IMPORTANT: You must also include the master column. Which column this is, depends upon the choice you made in Step Four: it's either the column of values that you made the key field in QuickBase or the ID field you created in Excel using VLOOKUP (like Company ID in the previous example). This master column will be the key that matches detail records to the appropriate master records as you import. To select multiple columns, hold down the Ctrl key and click on each Once you've selected all desired columns, right-click and select Copy.

  5. Import the selected fields by pasting into the desired table. Read how.
    When you do so, you'll match columns with QuickBase fields as you do in any import. In this case, you MUST import the master column into the reference field that links the detail table to the master table. Importing this data into the reference field will match up detail records with existing master records.
    Tip: If you're not sure which field is the reference field, you can find out by checking the Fields list in QuickBase. To do so, go to the application's table bar and click the detail table. Within the menu that appears, select Customize --tablename-- Table > Fields. The field list appears. Within the Info column, locate the field labeled reference. If the Info column does not appear in the field list, click the Advanced Options link, select Info, and click Save.

Your two QuickBase tables are now populated and related to each other.

Importing data into additional tables

If you have one or more other detail tables that are related to the first master table, you can do additional imports, repeating Step Five for each one.

If you have additional tables to import that are details of your details table or if you have other more complex relationships, you'll need to do some more creative imports and exports following the basic process above, matching master and detail values using the VLOOKUP function in Excel.

Related Topics

 

This page refers to an older version of QuickBase. Online help is now located at http://www.quickbase.com/user-assistance/default.html.

 

Return to top   

© 1999-2013 Intuit Inc. All rights reserved. Legal Notices.