Importing QuickBooks Inventory with Excel

There are a number of reasons why you may want to use Excel to import items to your item list in QuickBooks: Converting a list from another program, making copies of existing items, bulk addition of a new product line, and more. Today I’m going to give you an overview of how to use Excel to manipulate the item list.

I’m going to use Premier 2008 in my examples, but the approach I’ll use works for older versions as well. Starting in 2008 intuit added an “import wizard” that simplifies the import process, but this method is very restricted and can’t be used for all situations. Instead, we’ll use the advanced import method – which is the method used in versions older than 2008.

Note that to be able to use Excel to import and export the item list you must have Excel installed in the same computer that you are using for QuickBooks.

This article was updated on April 14, 2009.

Update September 29, 2010: See this article on Rapid Data Entry in QuickBooks 2010.

Overview

Here is the general procedure that we will follow:

1. Export the item list to Excel – this is useful in setting up a template for the spreadsheet we will import later.
2. Edit the spreadsheet to add the information that we want to add or change.
3. Make a backup of your company file before importing!
4. Map the columns of the spreadsheet with the fields in the item list.
5. Import the spreadsheet to the item list.
6. Review the error log, if one is generated.

Export the List

I usually recommend starting with an export of the list, even if you are just going to import new items (rather than making changes to existing items), so that you can see how QuickBooks wants to set things up. Add a typical item of the type you want to add (such as, an inventory part) with the accounts set up appropriately, then export the list.

In the Export window, if “csv” is the only option (the Excel options are “grayed out”), then you either do not have Excel installed, or QuickBooks can’t find Excel. Sometimes errors in your Windows Registry prevents QuickBooks from seeing Excel even if it is installed – if this happens, reinstall Excel and then this option should be available.

For the item list we don’t need any of the options on the Advanced tab, and you don’t need the explanation worksheet. Usually you will create a new Excel workbook – if you add to an existing one, the new export will usually be added in front of the highest numbered worksheet and will be assigned the name SheetX, where “X” should be the next highest number in that spreadsheet.

Your spreadsheet will look something like the following:

Create/Edit the Items

We can import items to Excel for two reasons – to add new items, or to edit existing items.

Editing is fairly simple – if the value in the Item column matches an item in the item list, QuickBooks will import the changed values. Some values cannot be changed.

Adding is a bit more complicated. Depending on the type of the item, there are certain required fields, just as if you were adding the item via the user interface. For this tutorial I’ll focus on inventory part items, which are the most common items to add via Excel.

To add an inventory part item, you must have:

• A type that defines what type of item this is.
• An ID or Item field, the name of the item.
• An Asset account.
• A COGS account.
• A Sales account.

If you add a sample item of other types to your item list in QuickBooks before the export, you can see which fields are available for each item type.

QuickBooks is very picky about the spelling of certain values. The type value must be exactly one of the following:

• Service
• Inventory Part
• Inventory Assembly
• Non-inventory Part
• Other Charge
• Subtotal
• Group
• Discount
• Payment

If you use something like “Inventory” or “Part” for example, QuickBooks will reject the line. This is one of the most common errors that people make.

Note also that some of these item types cannot be imported, as described later.

The ID (or Item) must be unique if you are adding an item – if the ID already exists then QuickBooks will try to do an update instead of an add. If you are adding a “subitem” then you would enter the main item name, a colon, then the item ID. For example, if you want to add an item “Washer” that is a subitem of “Widget”, you would identify this as “Widget:Washer”.

The Asset, COGS and Sales accounts must be valid accounts in your chart of accounts. If you set up one item using the proper accounts in your item list through the QuickBooks “add item” screen, and then export the item list to Excel, you will have a sample that shows the exact spelling of these accounts.

One very common use of an export/import of the item list is to make copies of an item to add as similar items. In the exported spreadsheet simply locate the starting item, make a copy of the line, and change the item ID.

Note that you can make an active item inactive by mapping the active status column to the Is Inactive field. The value should be either “Active” or “Not-active”.

Make a Backup

This is very important! Make a backup copy of your company file before doing the import. If something doesn’t work right, or you don’t like the results, you must have a backup copy of your file so that you can restore it. This will save you a lot of grief if a problem occurs.

Start the Import

Once you have your spreadsheet set up and saved, you can import it. Select the Import Items option from the Excel button on the item list.

In QuickBooks 2008 you will see the following screen:

We will be using the advanced import, which gives you more control over what you can import. In versions earlier than 2008 you won’t see this screen, the only option you have is the equivalent of the advanced import.

Select your import file, and the worksheet that contains your information. Put a check in the header rows checkbox. Click the add or Select a Mapping drop down list, where we will add a “mapping”.

Map the Columns

When you import the Excel spreadsheet we have to make an association between your spreadsheet columns and the fields in QuickBooks, called a mapping. The mappings are saved and can be reused later, so you only have to create the mapping once if you are going to use the same format multiple times.

To set up a new mapping you must give the map a name and then select the import type of Item. In the left column you see a listing of the fields that QuickBooks maintains for records in the item list. You can click by any of these fields, in the right column, to get a drop-down list of the column headings from your Excel spreadsheet. Pick the column heading that matches the field on the left.

It always seems odd to me that the import column names, which were created by the QuickBooks export, don’t match the field names that QuickBooks wants to use in the import!

Here are the minimum fields that you need to map to add an inventory part:

Note that you can import values into custom fields in the item list – which you cannot do with other Excel imports (such as the customer list).

Import

Once you have saved this mapping click the Preview button in the Import dialog. You will be told if there are any errors, and if you click on the record with the error you will be able to see a note that describes the error. If the list is small and simple to understand, exit the program and correct the errors, then try again.

If the list is extensive, if the errors are hard to decipher, or if things work out so you don’t have any errors, click the Import button. I recommend that you select the Do not import rows with errors option.

Note that the “preview” does not catch all errors. For example, with my test file above the preview says that there are 2 errors. But later, when trying to import, the error file shows 5 lines with errors.

If you are updating any records you will see a warning like the following. Usually the best option is the middle one, to replace existing data but ignore blank fields. In this way only the columns that you have selected will be imported for updates – the ones that are blank will be left unchanged in your QuickBooks file.

When the import is done you may get the following message, telling you that there are some errors. I strongly suggest that you save the error log.

This will save the file in a “csv” format (comma delimited text file) in the location and name of your choice. Make sure you save it in a place you can find later!

Review the Error Log

The error log is saved as a “csv” file – you can open this in Excel by selecting the “text files” file type.

The error log will list the information you tried to import and will give you an error message describing the problem.

You can correct the problem in this file and save it as an Excel spreadsheet, and then import from this file, if the list is small.

Please note that some error messages might not make sense, or might be misleading. For example, if you try to import a Payment or Subtotal item, you may get a message that the “Name” field is incorrect, that “The version of QuickBooks you are running does not permit subitems”. Even if you are not trying to add a subitem AND the version you are using DOES support subitems.

Problems, Restrictions, Notes

There are a number of things that you cannot do with this import, unfortunately. Here is a partial list:

• You cannot import payment, subtotal, group or inventory assembly items.
• There are some fields in some item types that you can enter on the screen but that you can’t enter in the import.
• In service, other charge and non-inventory items QuickBooks has a box that you can check to make them a “two sided item” – adding fields for a cost, expense account and purchase description. This box is usually labeled This item is used in assemblies or is purchased for a specific customer:job. If you are ADDING an item, and you want to enter information here, create a column that has a value of “Yes” or “Y”, and map it to the field Is Reimbursable Charge. If you map this, the import will put a check in the box and allow you to upload a cost, expense account and purchase description. You CANNOT use this to UPDATE the expense account, unfortunately. Note that the is reimbursable charge column does not exist in the Excel export. My thanks to Laszlo Tordai for pointing this out. (Updated 4/14/2009)
• You cannot use this kind of import to adjust the on-hand balance of an item.
• You can set the initial on-hand balance for an item if you are adding the item. You need two columns, the quantity and the extended value. The extended value is the cost of that quantity of items – so if you are adding 10 items at $5.00 each, the extended value would be $50.00. Map the quantity to the On Hand field, and the extended value to the Total Value field. You cannot enter the average cost directly, you have to use the quantity and extended (total) values.
• For some item types (inventory parts, for example) you cannot change the income account. If you do that in the Edit Item window you will note that they ask how you want to apply that, but they can’t ask the question on the import, so they don’t allow it. (updated 10/28/2008)

Conclusion

This has been a long article, I hope that it is of value to you. Please give me some feedback if there are aspects to this that are of interest to you that I did not cover, or if any portion of this is not clear.

Thanks!