This is the first in a series about importing data into QuickBooks. We will look at the tools provided by Intuit, with our primary focus being importing data from Excel. We will also look at some common utilities that permit more sophisticated forms of import.
.
Using the QuickBooks Import Excel Data Feature – Part 1: Basic Considerations
.
You can import lists of customers, vendors, accounts, or items into QuickBooks from Excel spreadsheets. Data that you import can be used to add new records to your QuickBooks lists or update existing records.
Your Data Import File
The QuickBooks Import feature allows you to use an Excel template worksheet; however, the data is limited in comparison to the ‘Advanced Import’ feature. Your import file can be any data spreadsheet application file that you create and save in Microsoft Excel (.xls or .xlsx) or comma-separated values (.csv) format. Loading, please wait . . .
For each list that can be imported, there are requirements for which fields are allowed and which fields are required. We will look at examples of these requirements in Part 2 of this mini-series.
Preparing Your Import – Things to Remember
Map only the fields required, or you need - As a minimum for most QuickBooks lists there are only a few fields to successfully create a list entry, for example a mandatory field to create a Customer record is the Customer Name/Number field. Reducing the number of fields that you map will reduce the chances of encountering an error when importing into QuickBooks.
Update QuickBooks list values in QuickBooks - Many errors encountered are due to list items that either do not exist, or already exist, in the QuickBooks company file you are importing into. Many times you select the option to ‘add’ your import data into an existing list. If the entry already exists, you will generate an error. Similarly, you might select the option to ‘update’ existing list entries, but if an entry in your list does not exist, again you will receive an error.
Verify your data format - Some fields require that the data be of a set format. If the data is not in the correct format then the import will fail and an error will be returned. Similarly, data fields in QuickBooks are of a specified length. If you try to import more data than the field permits, an error will also be returned, and the import can fail. If you experience errors you must verity that the format of the data that you are trying to import conforms to the QuickBooks format.
Avoid using special formatting or characters in Excel spreadsheets - If you are using a lot of special formatting in Excel the import process may not be able to read the data in the format that it needs to import into QuickBooks, or it may misinterpret the data you included in your spreadsheet. For example, if you combine fields in Excel from several fields into one, you need to paste the combined fields into an additional column in which you reformat that data as ‘values only’. The import engine used for Excel to QuickBooks within the utilities feature is not designed to read ‘formula data’ expressed in your Excel spreadsheet. Accordingly you need to convert to plain data before importing into QuickBooks to minimize errors.
Stay tuned for Part 2 when we look at some specific examples of list importing, specific requirements by data types, and specific mapping applicable for each data type.