Importing Microsoft Excel Data

When you step off the plane in a foreign land, you sometimes have trouble making yourself understood. What makes sense in your country doesn't always translate well to another. The same divide can appear when you try to plunk your Microsoft Excel data down into a QuickBase.

It's not that Excel speaks a different language; it's more a matter of customs and habits. Excel users often enter data in unique ways that suit a particular purpose. Excel enables this behavior by letting you enter pretty much anything you want anywhere. When you do so, the result is often a strangely formatted spreadsheet that doesn't fit the expectations of a traditional database table. If this is you, you need to tweak the format of your Excel data before you import it into QuickBase.

Another important difference between Microsoft Excel and a QuickBase application is that Excel is a flat data model. In other words, it's really just one long list of items on a spreadsheet. The application, on the other hand, is multi-dimensional-- it's a relational database. This means that the same data that lived in one Excel list should be split into multiple lists and each list can be related to other lists. This system is more efficient than the single list. For example, instead of a long list of purchases that include the customer name, product and invoice amount, you could have three separate lists (which are called tables in QuickBase): Customers, Products and Invoices. Under this system, you need to type the customer name just once. The trick, when moving from Excel to QuickBase, is to know which columns belong in which tables.

You can import data from Microsoft Excel by either importing the file or copying and pasting only the data that you need. (If the file was created with Microsoft Excel 2007 or later, you can import the file directly into QuickBase.  If it was created in a prior version of Excel, you can import the file by first converting it to .csv, and then importing the .csv file.)

Step One: Does your spreadsheet fit into a QuickBase at all?

Most Excel spreadsheets can translate easily into QuickBase. But, there's one exception. If you use a collection of Excel worksheets as forms, an import won't work very well. For example, if your Excel file consists of 32 individual worksheets, each of which contains a few fields, you can't import them all at once. QuickBase expects to see a list of individual records on a single worksheet, and imports only the first worksheet in an Excel file.

Step Two: Clean up your Excel Spreadsheet

You may not think your Excel spreadsheet is sloppy, but if QuickBase thinks so, you'll have trouble importing it into your new application. For best results, your data must fit the structure that works well in QuickBase. Clean up before import, and you'll save yourself work later.  If this is the first time you're importing data from Excel, read Prepare Excel Data for Import before proceeding.

Note: If you need to import an additional worksheet from an Excel file, move that worksheet to the first position on the left, save your changes, and then import the .xlsx file.

Step Three: Decide if you'll create a new application or import into an existing application.

You must decide if you'll create a new application via import (in other words, if you'll convert your data into a new QuickBase application) or if you want to take your Excel data and make it fit into an existing QuickBase application or template, thereby importing into one or more existing tables.  

If you want to create a new application, read Import Data from Excel to Create a New Application.

If you want to import into an existing application, read Import Data from Excel into an Existing Application

Related Topics

 

Return to top