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 put your Microsoft Excel data into a Quick Base app.

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 Quick Base.

Another important difference between Microsoft Excel and a Quick Base app is that Excel has a flat data model. In other words, it's really just one long list of items. 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 Quick Base): Customers, Products and Invoices. Under this system, you need to type the customer name just once. The trick, when moving from Excel to Quick Base, 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 Quick Base.  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 Quick Base app at all?

Most Excel spreadsheets can translate easily into Quick Base. 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. Quick Base 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 Quick Base 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 Quick Base. 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 Quick Base application) or if you want to take your Excel data and make it fit into an existing Quick Base application, 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:


Go back      |       

© 1999-2019  QuickBase, Inc.  All rights reserved.  Legal Notices.