Prepare Excel Data for Import

Because Excel lets you enter anything in any cell, there are as many
spreadsheet formats as there are spreadsheet creators. While your spreadsheet
may make sense to you, a standard database program might have more trouble
understanding your intentions. If your spreadsheet is arranged in a way
that doesn't fit the model of a traditional database table (which you'll
be able to identify in a minute), you need to tweak the format of your
Excel data before you import it into QuickBase.

QuickBase needs your spreadsheet to be organized in consistent rows
and columns. Use the following guidelines to organize your spreadsheet
before importing it into QuickBase:

Each of your rows should represent one record. Rows become individual
records in QuickBase. All rows should look similar.

Cells in each column should contain the same kind of information.
Each column becomes a field and the cells in a column become
values for that field in QuickBase.

Your spreadsheet should not contain a title in the row, as shown
in the example
below. If it does have a title, delete it. If the
first row is missing data from one or more of the right-most cells,
QuickBase does not import any data from those final columns, even
if there is data in other cells in those columns. That means
that in the example below, only columns A and B would be imported.

Each cell should have a label in the first row of data indicating
the kind of information it holds. You can set these labels as
field names in QuickBase.

Blank rows are not imported. You can either delete them
or ignore them in Excel.

Your spreadsheet should not have empty columns that appear to
the left of your first column of data; they are imported as blank
values into QuickBase. You can delete them before import, or
you can select Do Not Import
for those columns when previewing the import in QuickBase.

Excel formulas are not imported; QuickBase imports only the
value in an Excel formula cell. Before importing, correct any Excel
formula cells that say "#ERROR". Excel formulas that
refer to values in a different row are not replicated by a QuickBase
formula-type field. QuickBase has its own formula language that
you can use to perform the same kinds of calculations your spreadsheet
performs.

Ensure text notes and currencies are handled correctly during
import. See other format issues,
below.

QuickBase imports only the first worksheet in an Excel file. If
you need to import an additional worksheet from an Excel file, move that
worksheet so that it is the first sheet in the workbook, save your changes,
and then import the .xlsx file.

Example:
Cleaning up your Excel spreadsheet

Before you begin cleaning up your Excel spreadsheet for import, it might
look something like the one in below. This is a list of equipment sales.
Most rows in this spreadsheet represent sales.

The Format Problems

This spreadsheet is a good start, but it's
not ready for import into QuickBase. The data is not consistent. For example,
the title in row 1 is not a record of a sale and does not contain data
in each of the columns that you want to import, so delete it. Also, QuickBase
won't know who sold the color printer in row 5. QuickBase reads
each row as an individual record and can't infer values based on indentation.
If you import that line as is, the sale won't be connected with a rep.

What you need to do

Since you
only want to import sales records, delete the title in row one. Then connect
each sale with a sales rep by filling in the blank cells, highlighted
in yellow. Leave the row of column headings. QuickBase will use it to
name your columns. Don't worry about entire rows that are blank. QuickBase
ignores them.

Note: If your spreadsheet contains
a column or row that totals, delete it. QuickBase
will total for you automatically. Excel formulas don't carry over into
QuickBase. The import will think your Totals column is another
column of data you entered manually, which may confuse you later when
it does not update automatically.

The final result

Following
the changes, this spreadsheet now flows seamlessly into QuickBase. Each
sale is connected with a Sales Rep and there's no extraneous non-sale
related data.

Other
format issues

Text Notes

Does one cell in your spreadsheet contain a list of items? For example,
do you list several contacts belonging to one company in a single cell?
Or do you type keep a "notes" type column to remind you of upcoming
dates?

Excel cells that list multiple items don't usually fit well into QuickBase.

If these are truly just text notes, that's fine. You can import the
column into a Text - Multi-line field type. But if a single field lists
discrete pieces of information that you want to track individually (like
dates and names), this kind of freestyle data entry won't import cleanly
into QuickBase. It's likely that this data should be split
up prior to importing. In the case of a deadlines list like the one pictured here, each event
should probably become an individual activity record or perhaps its own
date field. Unfortunately, there's no easy way to split them
up. You must do this manually. You can turn them into individual rows
or columns here in Excel, or import the column into a QuickBase text field
along with your other information and manually create additional records
or fields later in QuickBase. Either way, the process is cumbersome. However,
work you do now will pay off later in the form of an application that's
well-designed and easy to use.

Currencies

If your spreadsheet contains US currency (dollars), QuickBase correctly
sets the field type to Numeric - Currency. However, if your spreadsheet contains
non-US currency, QuickBase sets the field type to Text. You can change
the field type to Numeric - Currency when
previewing the import. Choose the appropriate currency symbol and its placement on the field properties
page for that field after the import. Alternatively, define the fields in the table prior to importing, and set the currency symbol and its placement for each Numeric-Currency field. Then when you import, the data from the import file is correctly formatted.