Importing data from Excel into existing apps

So you've found a wonderful app on the Quickbase Exchange 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 app.

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 app.

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

  • If you decide that all your data belongs in one table in your existing app, then you can skip the rest of this topic. Simply import your data into that table.

  • If your data will live in two or more tables in your existing Quickbase app, read on.

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. To maintain both these connections, you must import columns from your Excel spreadsheet in a specific order. You must start with the parent table. The parent is the "one" in the one-to-many relationship between tables. (Read more about relationships.)

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 parent 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 child records (like contact details from the First Name and Last Name fields. When you import the child columns you'll include a key column of parent values 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 parent 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 parent table first.

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

  2. You don't want to create duplicate records in your Quickbase app. So the first step is to reduce the parent 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). 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.

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

  5. Copy parent record-related columns.
    Select the parent column as well as any other parent-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 each column heading. After you select the columns, right-click and select Copy.

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

Step Four: Match child records with parent records

The Excel spreadsheet you're working from lists parent values (like company name) alongside child 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 child record's reference field with the corresponding values from the parent 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 parent records in Quickbase, you can change the key field of that table. Doing so will let you maintain the relationship between the parent list (like Companies) and the list of child 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 child records with their parent values.

BE CAREFUL: If you have data in your parent 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 parent 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 parent 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 parent records to Excel and match on Record ID using VLOOKUP

If you have data that existed in your app 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 parent 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 child records with a parent).

To export parent records to Excel and match on Record ID using VLOOKUP:
  1. Export two fields from the Quickbase parent 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.)

    Here, values from the Company Name and Record ID# fields have been exported to an Excel worksheet attached to the worksheet that holds child 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 parent values you exported from Quickbase with child records in your Excel spreadsheet.

    The VLOOKUP function matches values for you. The following figure shows how it works. Read more about VLOOKUP.

    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 details table into your app

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

To import the details 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 child 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 parent 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 parent column will be the key that matches child records to the appropriate parent records as you import. To select multiple columns, hold down the Ctrl key and click 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 parent column into the reference field that links the details table to the parent table. Importing this data into the reference field will match up child records with existing parent 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, access the details table. Click Settingsin the Page bar. Click Fields to display the field list. 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 details tables that are related to the first parent 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 parent and child values using the VLOOKUP function in Excel.

Related topics: