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:
- Export the item list to Excel – this is useful in setting up a template for the spreadsheet we will import later.
- Edit the spreadsheet to add the information that we want to add or change.
- Make a backup of your company file before importing!
- Map the columns of the spreadsheet with the fields in the item list.
- Import the spreadsheet to the item list.
- 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!
Category: Featured, Import/Export, Inventory, Manufacturing
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70′s, focusing on inventory and accounting software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor. Look for Charlie’s articles in the QuickBooks and Beyond blog, as well as his California Wildflower Hikes blog.
Connect with Charlie at Google
Subscribe
If you enjoyed this article, subscribe to receive more just like it.
Ryan, this might or might not work. Some people have told me that it does. If you try this PLEASE let me know if it works.
I had that problem awhile ago – it no longer happens so I can’t test this.
Take a look at the screen shot at this link: http://screencast.com/t/Y2Q1NTZi
When you have this blank screen, don’t click in the larger area where field names should be listed – which normally you can do – instead click on the VERY RIGHT side, where the dropdown list icon/arrow should be showing. Then you may actually get the dropdown list. If you select from the list and don’t actually click in the field itself, it shouldn’t freeze. At least that is how I got around it when I was seeing the problem awhile ago.
I just wanted to say a quick thank you, you just saved me hours of work!
is there anyway to bulk rename inventory assembly items?
Jim, not really – since the “name” is the key, renaming them through an import would just add new items and leave the older ones still there.
I am trying to clean up a large item list full of old inventory entries. The user has a lot of old things in which there is “0″ “on hand” that she’d like to get rid of.
The list is too long to delete each of the unused items manually, so I’m trying to:
1. export the current item list into excel
2. remove the entries showing “0″ “on hand”
3. resave the new file in Excel
4. import the file back into QB
Whenever I get to the point of mapping the import, my QB freezes up (not responding). I’m working in QB Professional Retail 2009 / Windows 7 / Excel 2010. The computer is new and has plenty of horsepower, but something catching…
Any thoughts?
You can’t do a bulk delete that way. The import doesn’t remove items that aren’t in the import. The import isn’t a replacement of the item list, it is either an update (if there is a match) or an addition (if there is no match). Deleting can’t be done this way.
And if you have any transactions using the items, you can’t delete them in the QB user interface either.
The only thing you can do in a bulk approach like this is to change them from active to inactive, to hide them.
Great Post, I just have one question. I have a copy of QB 2006 Pro and want to import items I sell in excel format. How many items can QB handle? I have over 250,000 products.
Kyle, there is a limit of 14,500 items in the item list in QuickBooks Pro and Premier. Enterprise had a limit of 29,000 back in 2006, but current versions technically have no limit. However, you may find that you run into speed issues when you get over 100,000 items in Enterprise, according to some people.
Good evening Charlie. I have built hundreds of QB files for clients and built and imported tens of thousands of assemblies using IIF files. In the latest file we’re building, they want to import custom fields. They are importing fine on the Inventory Items, however they are not importing at all on Assemblies. We have filled in the fields on one of the assemblies, exported the item list and followed the methodology on other items without success.
Please advise as to whether we can import custom fields on assemblies, and if so, what might be causing the problem.
Lori, I don’t work with IIF files. It doesn’t surprise me that there is an issue here, it fits a pattern with Intuit tools. Inventory Assemblies are often problematical.
The best I an offer is to suggest that you look into the Transaction Pro Importer (http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/) – I know that it can import assemblies and BOM’s, I assume it can handle custom fields for the inventory assembly item itself. Haven’t tried that myself.
Thanks Charlie. I use Karl Irvin’s tools all of the time. Will be interesting to see if this is something else I should add to my quiver.
Lori, that isn’t one of Karl’s tools – that is a tool from Baystate Consulting, which is another excellent software developer who I rely on (along with Karl…)
Very informative article! I’m researching how to do an export/import of a customer file. My new employer set up his own QB files and entered all of Customer Name field (Firstname Lastname) and now would like to clean it up without having to do it manually. Is this something you have experience with and can help me with? If not, is there somewhere/someone you can recommend? I’m fluent with Excel so that end of it is no problem, just need to be able to handle the import back in without bringing in duplicates and haven’t been successful in finding an answer from QB. We are running QB 2010 Pro.
Amy, in the Excel import/export, the CUSTOMER NAME is the “key” field, if you export a record and then import it with changes, the key field has to match. So you are out of luck there. Internally, QuickBooks has a customer ID that is separate. If you use one of the more advanced import/export tools you should be able to make the change using that. The ODBC tool (www.qodbc.com) would let you do that, you can use ODBC with Excel. But that is a bit tricky. You can get the Transaction Pro Importer and Exporter tools to do this. The Exporter to export the customer list with the internal ID to Excel, the Importer tool to import back from Excel. I have written articles about those two tools that you can review. But that is more expensive. Or you can find a ProAdvisor who understands how to use the tools and who already has them, who can do it for a fee.
hi Charlie,
I am using QB 2007 pro.
Today I tried follow your instruction to import some items. I always input items manually before.
Everything was fine, but QB did not “added/updated” the qty. I got the error report,saying “On Hand: You can only set Quantity On Hand for new record. | Total Value: You can only set opening balance for new record. | Error Record: User selected to Skip”.
I mapped “qty” with “on hand”, and “extended value” with “total value”.
Thank you,
hi Charlie,
Another problem, when I tries to import the new items, the new items were imported fine but the qty was 0.
thanks,
Sophie, the error you got about on hand is saying that you tried to map the quantity, BUT you were importing items that already exist in the item list. You can only import quantity and value if you are adding NEW items. These matched something in the list already.
For the second, without having hands on your file to see how you set up the mapping, it sounds like you didn’t have the quantity mapped correctly.
hi Charlie,
Thanks for the quick reply.
In the article you posted, “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.”
Does it mean that adding qty on the existing items are allowed?
But in my case, it seems it was not allowed to do so. Is it because it is 2007 pro, and I should update to 2011?
thanks, sophie
Sophie, as it says, “if you are adding the item”. So for an item that isn’t on the item list, you can enter a quantity. Just above that I say “You cannot use this kind of import to adjust the on-hand balance of an item”. This means if the item already exists, you can’t use this to adjust or change the quantity. It is the same restriction that you have when manually adding or editing an item in the user interface – you can enter the quantity when adding the item, but you can’t change it when you edit an item (in that window).
I just tested a copy of QB 2007, and it works the way I outline in this article. If you have NEW items, you can enter the quantity. If you want to UPDATE existing items, you can’t use this. You either have to figure out the IIF import method (complicated) for an inventory adjustment transaction, or use a tool like the Transaction Pro Importer (which I review in another article in the blog). Changing a quantity is an adjustment transaction, and that is a different thing entirely.
thanks a lot, Charlie,
Hi Charlie,
Great instruction and thanks for the generous help!
When importing an item that has a large description, it looks much better to use carriage returns to break lines and it give the details breathing room. For example:
“Cambria Fabrication and Installation Includes templating of material. No sealing required.Color: (specify) Edge Style: Eased – unless otherwise noted.Sink/Cooktop Cutouts: (1)Note: estimate only, pricing confirmed following template.Includes mounting of undermount sink.Drop-ins, Farmhouse, and other sink styles are not handled by our installers. Additional cutting charges may apply depending on style of sink chosen.No plumbing work is included.”
VS
“Cambria Fabrication and Installation
Includes templating of material.
No sealing required.
Edge Style: Eased – unless otherwise noted.
Sink/Cooktop Cutouts: (1)
Note: estimate only, pricing confirmed following template.
Includes mounting of undermount sink.
Drop-ins, Farmhouse, and other sink styles are not handled by our installers.
Additional cutting charges may apply depending on style of sink chosen. No plumbing work is included.
Note: Epoxy for an undermount sink must dry for 24 hrs before plumbing is reconnected. Otherwise epoxy may fail.”
Is there a way to have this formatting carry over when importing over from excel?
Angelo, I don’t think you can get that to work in the Advanced Excel import. You can do a paste operation in the Add/Edit Multiple List Entries feature, and it will work there. You may be able to do this with the Baystate Consulting import and export utilities (I’m pretty sure you can do it there).
Hi Charlie,
Ive exported my item list but have a problem importing it back. Im using stock part.My quantity on hand is a mess. When I try to import back it gives me an error message. I did everything that you wrote but still gives me the same problem.
Sindi, without knowing what the error message is, there isn’t a lot that I can recommend. Note, however, that you can’t use the Excel import to change the quantity on hand in the item list. You can only add a quantity when adding a new item. Changes are transactions, you have to do that in different ways.
Charlie,
It says my data contains duplicate list elements. If it can’t fix the the quantity on hand then how can I fix it? Im so desperate because I found the list messed up, now it’s not possible for me to use the Sales Order Fulfillment Worksheet and the Item List without opening the Adjustment Page first.
Sindi, without hands on your system it is hard to give a specific answer. You may want to find a local ProAdvisor who can help you directly.
You can enter adjustment transactions using a tool like the Transaction Pro Importer (http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/) or you can use inventory adjustments directly in QB to get things into balance. But it sounds like you may have more going on than that…
I own a filling station and I stock 3 products. Diesel, Unleaded and Premium.
I have over 6 months of invoice data I would like to receive into inventory. I entered the first 5 deliveries using the ‘receive inventory with bill’ option and then started to look for a better way. I found your Import List tutorial expecting to learn how to import these inventory deliveries but it wasn’t covered.
I have all the data in Excel and can provide just about any format that QB might accept.
I also have 6 months of sales data to enter or import. I have two shifts/day and all products are lumped together for each shift so with 3 products and 2 shifts, I only have 6 sales transactions/day but it is also tedious to key and would appreciate any info on where to look.
Thanks, George Barrowcliff
George, this article talks about a LIST import. The item list. What you are looking for is a TRANSACTION import, and that is a different thing altogether. Not covered in this article. You can do it with IIF imports (http://qbblog.ccrsoftware.info/2010/02/quickbooks-add-on-programs-iif/ ) but that is complicated to figure out. You can get an import tool from Big Red Consulting (www.bigredconsulting.com) to help. The easiest would probably be the Transaction Pro Importer (http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/ )