15 Nov Sample Using Excel Import Feature
Sample Using Excel Import Feature
New with version 2003 Pro and higher is an import feature the greatly enhances the ease of importing list information for customers, vendors, items, and accounts from a Microsoft Excel spreadsheet. This new feature makes the process of changing from another accounting software (assuming it is possible to get the list information out of the other software and into Excel) to QuickBooks easier.
To begin the process, a separate spreadsheet for each type of list is usually easiest. At a minimum, the different list information should be on separate sheets in the document. Confirm all the information to be imported into QuickBooks is in the Excel spreadsheet. Although it is not necessary to have header columns, this may make the process easier. If there are not headers, be sure the first row in the spreadsheet contains the data because this information will be helpful when mapping the spreadsheet to QuickBooks. Close the spreadsheet and open QuickBooks.
In QuickBooks, choose to import from Excel.
QBRA-2004: File > Import > Excel Files
The file is the Excel file that contains the information to be imported. If the exact drive, folder, and name is unknown, click on the Browse button to navigate to the file.
The sheet is important so the software knows where to look for the information. The sheet titles (i.e. Sheet 1, Sheet 2, etc) will appear if there are multiple sheets in the file. If the sheet names have been edited when the file was open in Excel, the edited names will appear.
Check the box if the spreadsheet has header rows, uncheck the box if it does not.
Typically the mapping will be added new. If this type of list in this format has already been imported into this data file, the mapping has already been set up and can be chosen from the pull down list.
QBRA-2004: File > Import > Excel Files > Mapping pull down > Add New
Type in a name for this mapping then choose the import type. Note that the only choices are customer, vendor, item, or account.
Once the import type has been chosen, the bottom section will be filled in with the fields that can be imported. On the left, it is possible to choose from the pull down list from the spreadsheet which columns the information should be contained in.
QBRA-2004: File > Import > Excel Files > Mapping pull down > Add New > Customer
TRICK: With each import type, there are certain formatting issues that need to be addressed so it is important to know how the fields will be imported prior to setting up the spreadsheet for importing from. For example, the address information for the customers is set up as billing address lines and shipping address lines. I.e. the customer city state and zip should all be in one column in the spreadsheet to import properly.
Once the mapping is set up, click on save to preserve the mapping for use.
Click on Preview and it is possible to see the data as it will be imported. The statistics of how many rows were process with how many errors is available. By clicking on the line with the error, it is possible to see a description of what the error is.
Here are a few examples of situations that will cause errors:
- If the contact name is mapped for the Salutation field, an error will occur, because this field is designated as a limited number of characters for titles such as Mr. Mrs. Etc.
- It is possible to import jobs or customers, but not with the name containing the colon like was required in the previous iif import procedures.
- The types for items may not be consistent with those used in previous versions. For example, an “inventory” item now requires that the type be designated as “inventory part.”
While these annoyances may take some getting used to, this process is still preferable over trying to figure out which column in the spreadsheet is causing the problem in the prior versions. In addition, it is nice to see what will be imported prior to the import actually being done. The errors can be ignored, or imported with only the information that did not cause the error when possible.
QBRA-2004: File > Import > Excel Files > Fill in Set Up Screen > Preview
More information on this topic