The way you organize Excel data in your spreadsheet will impact how well it imports into Quickbase. Follow the guidelines in this article for the smoothest import from Excel to Quickbase.
Column and row organization
When you import your spreadsheet into Quickbase:
-
Every vertical column will become a field
-
Individual cells in vertical columns will become field values
-
Every row will become a single record
Horizontal rows
Each horizontal row should contain information related to a single thing.
-
Example: If you use your spreadsheet to track sales invoices, each horizontal row should contain information that pertains to a single invoice.
Vertical columns
Each vertical column should contain the same kind of information.
-
Example: In a spreadsheet that tracks sales invoices, column A in Excel might contain the name of the sales representative who made the sale. In this case, column A should be populated entirely with names and no other kind of information.
Column labels
Give every vertical column a descriptive label. You can set these labels as field names in Quickbase.
Ensuring all your data is imported
When Quickbase imports data from Excel, it starts with the first row and imports however many columns contain data.
Avoid spreadsheet titles
If your spreadsheet contains a row that contains only a spreadsheet title, delete it to ensure all your data is imported. Because the title is in the first row, Quickbase will see that only one column has data and only import one column.
Delete empty columns and rows
Empty columns that appear to the left of your first column of data are imported as blank values. Quickbase will import up to the last populated column in the first row.
-
For a cleaner import, delete empty columns from your spreadsheet.
-
If you do not delete them before import, you can select Do Not Import for those specific columns when previewing the import in Quickbase.
Empty rows are not imported. You can either delete them or ignore them in Excel.
Importing multiple worksheets in an Excel file
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 file again.
Excel formulas
Excel formulas are not imported into Quickbase. However, the value in an Excel formula cell will be imported.
-
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, after your data has been imported.
If your spreadsheet contains a column or row that shows totals, delete it. Quickbase automatically totals data.
-
If you do not delete the row, Quickbase will import the totals column with the values currently populating the cells. We suggest deleting it so you are not confused later when the column stays static and does not update when other values are updated.
Formatting for specific field types
Most fields are easy to import into, but some require special formatting in Excel. Keep reading to see how to format data for these field types.
Note: Not every field is covered; only the ones that may require additional work before import.
Multi-select Text
Note: Multi-select Text fields cannot be created at the time of import. They must already exist in your table before you can import into them.
If you have one cell in your spreadsheet that contains a list of items that might be common across many rows, you can import them into a multi-select text field.
Example: You may have a cell that lists different priority levels that can be assigned to different tasks.
You want one or more of these categories to be assigned to records in your Quickbase table, and you’ve determined that a Multi-Select Text field is the best way to do this.
To set this cell as the values to populate your Multi-Select Text field, remove any line breaks and separate each value in the Excel cell with a semi-colon.
If any of the values you want to import contain a semi-colon, add double quotes around the words that need to be grouped together.
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.
Address
Note: Address fields on mobile devices cannot be created at the time of import. They must already exist in your table before you can import into them.
Quickbase address fields are comprised of several subfields, including street address, city, state or region, postal code, and country.
If your Excel data stores complete addresses in one cell, you will need to break them up into components that match address subfields.
Numeric - Currency
When you import currency data, Quickbase by default imports it as US currency (dollars), even if you have formatted it differently in your Excel spreadsheet.
You can change the currency symbol in the field properties after import. Alternatively, you can create the field before importing and set the currency symbol beforehand.
Numeric - Percent
You can input any numeric data into a Numeric - Percent field. Quickbase will display it as a percentage, even if it was not formatted as a percentage in Excel.
Unlike when you set the data type in Excel, Quickbase will not do any calculations when you enter numeric data into a Numeric - Percent field type. So if a cell contains the value .86 in Excel and you import it into a Numeric - Percent field, it will display as .86%, not 86%.
Date / Time
When you import data into a Date / Time field, make sure that the time of day (AM or PM) is displayed visibly in the Excel cells. If it is not visible in each cell, Quickbase will input the default time set in the field properties.
Quickbase accepts a wide range of spacing and formatting, and you likely will not need to adjust the way the date and time is displayed.
After import, you can change how the date and time are displayed in the field settings. Alternatively, you can create the field before importing and set the format beforehand.
Time zone
Even if your Excel data contains time zone information, Quickbase will only display the time zone as has been specified for the account or app. See the Set the time zone for both the app and the account help article for more information. You can choose to turn time zone display on or off in the Date/Time field properties.
Time of Day
Quickbase accepts a wide variety of time formats, including a 24-hour-clock format. If your time data is not shown in the 24-hour-clock format, make sure that the time of day (AM or PM) is displayed visibly in the Excel cells. If it is not displayed, Quickbase will default to AM.
Time data will display in Quickbase according to the Time of Day field settings, not how it is formatted in Excel.
You can change how time is displayed in the field properties after import. Alternatively, you can create the field before importing and set the time format beforehand.
Duration
When you import into a Duration field, make sure you have specified the unit of measurement in each cell. You can adjust this unit in the field properties after import.
Make sure that none of the Excel cells contain combined units of measurement. For example, a cell cannot measure duration as “3 days 6 hours” or “4 hours 2 minutes.”
Quickbase recognizes most abbreviations and will accept decimal places (for example. 1.5 hours). However, it does not recognize colons to show the difference between hours and minutes (for example, it will not import 5:25.4). If Quickbase cannot recognize a duration value, it will import blank data.
Checkbox
Note that no Excel form elements, like checkboxes, can be imported into Quickbase. However, you can import data that is either true or false into a checkbox field.
The following values will import into a checkbox field:
| Checked | Unchecked |
|---|---|
| Yes | No |
| True | False |
| 1 | 0 |
List - User
List - user fields can be used to assign more than one user to a specific record. Just as when you are importing any user into Quickbase, import values for List - User fields using email addresses. Separate each address with a semi-colon. For more information on importing users, see the Rules for importing into list-user fields help article.