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.
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.
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?
It’s easy to import this data into Quickbase, using Multi-select Text and Text - Multi-line fields. If a field in your spreadsheet contains:
- Text notes that are free-form or applicable only to a single record, you can import that data into a Text - Multi-line field.
- A list of items that might be common across records, such as categories, you can import them into a Multi-select Text field type. Just separate each value in the field with a semi-colon.
If any of the values you want to import happens to contain a semi-colon, add double quotes around the phrase you want to keep together. For example:
On prepositions; “Semi-colons; so tricky”; The ellipsis
If you want the values you are importing to become the selectable values for the field, select the Allow users to add new choices checkbox in the new Multi-select Text field properties.
A Multi-select Text field can't contain more than 50 choices, so if there are more than 50 unique values in the spreadsheet, leave the Allow users to add new choices property unchecked. The values will be imported, but display in red and can’t be chosen in other records.
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.
Did this page help?
© 1999-2021 Quickbase, Inc. All rights reserved. Legal Notices.