Importing Multi-Table Data From A Spreadsheet Into QuickBase

Quick Base makes it easy to import data from a spreadsheet. To import new data into a table, just select the table from the table bar, then choose More and Import/Export. That takes you to a Quick Base page where you can import from a spreadsheet file or paste data from the clipboard. Quick Base parses your data into columns and you can assign each field to an existing column in your table, create a new column in your table, or ignore that particular column. This process works great when your spreadsheet data maps directly to a single Quick Base table.

Sometimes our spreadsheets contain details that really should be mapped to two or more Quick Base tables, which are connected to one another via table-to-table relationships. For example, consider this spreadsheet of contacts you would like to import into Quick Base:

Spreadsheet Data

Company

First Name

Last Name

Job Title

Spacely Sprockets

Cosmo

Spacely

President

Slate Rock and Gravel Company

Fred

Flintstone

Crane Operator

Spacely Sprockets

George

Jetson

Digital Index Operator

Slate Rock and Gravel Company

Barney

Rubble

Crane Operator

Jellystone Park

Yogi

Bear

Bear

Jellystone Park

Boo

Boo

Bear

We could import these records directly into one Quick Base table. However, database design principles recommend that we segregate the repeating values (Company) into a separate table with one record for each company, and then connect the individuals (First Name, Last Name and JobTitle) to their respective companies using a table-to-table relationship in Quick Base.  So our tables would look like this:

Company Table

Record ID

Company

1

Spacely Sprockets

2

Slate Rock and Gravel Company

3

Jellystone Park

Contacts Table

Record ID

First Name

Last Name

Job Title

Related Company

1

Cosmo

Spacely

President

1

2

Fred

Flintstone

Crane Operator

2

3

George

Jetson

Digital Index Operator

1

4

Barney

Rubble

Crane Operator

2

5

Yogi

Bear

Bear

3

6

Boo

Boo

Bear

3

How do we do this in practice? Easy! Just follow these steps. The most important thing to remember is to create the tables highest in your hierarchy first. We only have two tables and the top of the hierarchy is Company. Copy the company-related fields from your source spreadsheet and paste into a new tab or worksheet. For us, that’s just the company name. Reduce the list to unique elements. Excel makes it easy to remove duplicates using the Remove Duplicates button on the Data tab.

Excel tells us that 3 duplicate values will be removed. That looks right so let’s continue.

After removing duplicates, we will import the company records into Quick Base and it assigns unique record IDs to each company. That’s important and we will need those record IDs in a minute. If you don’t already have a Companies table in Quick Base go ahead and create one with at least one field, “Name”. Then choose the More >> Import/Export command to import data. Paste your company records from the clipboard into the text area that Quick Base presents.

Then, press Import Data. Quick Base shows us a dialog which enables us to map our data columns to Quick Base table fields.

 

When you are happy with the mapping of spreadsheet columns to Quick Base fields, press the Import button and Quick Base will create your new company records.

Right after the Import we are going to export that same data and bring it back to Excel so that we can match the companies - and their Quick Base record ID#s - with the contacts. Create a report of Companies. I usually call it Export For Matching. Include only the Company fields that you will use to match your original spreadsheet records, plus the Quick Base-assigned Record ID#.

You will use Quick Base’s “More >> Save as a spreadsheet” command to export a CSV file. Once it is downloaded to your computer, open it with Excel and copy the rows to a new tab of your original spreadsheet. Call the new tab “Companies”.

Now we are ready to assign Related Company IDs to each record of your original spreadsheet using Excel’s VLOOKUP worksheet function. VLOOKUP is also available in other spreadsheets such as OpenOffice and Google Sheets. In the illustration below we have filled column E with VLOOKUPs. Here is the entry in cell E7:

=VLOOKUP(A7,Companies!A:B,2,FALSE)

The first parameter of VLOOKUP is the value to match. We want to match on company name so we use “A7”. The second parameter is a range on the Companies sheet. The left-most column of the range contains the value that we want to match. So we would expect to find a value of “Jellystone Park” somewhere in column A of the Companies worksheet. Parameter 3 tells us what value to return when we find a match. We have specified “2” so VLOOKUP will return the value in the second column of the range Companies!A:B. In the example, that value is “3”. The fourth parameter tells VLOOKUP to require an exact match.

Now you are ready to import your contacts  into the Contacts table. Select the range B1:E7 and follow the Quick Base import procedure that is described above. This time we import into the Contacts table. Because we include the values for Related Company, Quick Base automatically links our Contacts to their respective Companies.

You can repeat this procedure as many times as you need to import records and have them linked to parent tables. Just remember to start with tables that are highest in your hierarchy (parent tables) and then work down toward child tables.

Dan Pettengill

Leave a Reply

CAPTCHA
Type the characters you see in the picture. Not case sensitive.
Image CAPTCHA

Enter the characters shown in the image.