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.
Comments (177)
Trackback URL | Comments RSS Feed
Sites That Link to this Post
- Using Excel for import | Rustler’s Quickbooks Tips | May 13, 2009
- Importing QuickBooks Transactions with the Transaction Pro Importer : QuickBooks and Beyond | February 22, 2012
Hey charlie
i am setting up a new company and i don’t have any mapping done or a previous excel sheets to export can you help me step by step to import and excel file because i have been trying to import a file for a while but is not working. i am using a 2009 version to the quick books. can you also help me set up the mapping part.
Thank you
Alan, I’ve given you a lot of detail and step by step info in this article – I’m not sure what more you are looking for?
Charlie – Thank you for all of your generous help. I have read through everything and have a couple of questions. I am pretty sure that I got the answers from what I have read, but want to be sure.
1) I am wanting to totally revamp my item list. I have exported to Excel, am working on the list and plan on importing back to QB (Enterprise Solutions, v 11).
2) Will I have to go in an make every single item in my current list inactive in order to import and revamp my existing list? From what I am understanding from everything that I’ve read, it will ADD the new items to the existing list and not replace? Is that right?
3) We are just wanting to “replace” our inventory list in our existing QB and “start fresh” so to speak. But, I think from everything that you’ve instructed, that is not possible. Am I right?
Thanks so much!!!
Julia:
1) “Totally Revamp” – I guess that depends on what you want to do. If you want to change item types, rename things, then no, you wouldn’t necessarily export then import. You can’t change item types (for the most part), you can’t change the name of an item, through an export/import.
2) I’m not sure what you want to accomplish with the Inactive/Active stuff. Making an item inactive just keeps it from showing on the screen in QB itself. But the item is still there, the name is in use, so the item still exists.
3) You aren’t going to replace your item list in an export/import.
Once you use items in any transaction, you can’t get rid of it. You can change the name through the user interface, you can merge items together (which is a BAD IDEA for inventory parts), you can make them inactive to hide them. But you can’t really get rid of them.
You can hide all that you have, and import new items with new names if you wish. Or you can create a new file that doesn’t carry over the old stuff (including all your other transactions).
It is hard to give specific advice without more detail…
Charlie, We have been attempting to use IIF for the inventory item import, but none of the inventory items show up except the last record; and it us under “Service” item instead of “Inventory” category. I notice from your instructions that the term “inventory part” should be used instead of “inventory” although the latter is clearly defined in the IIF Kit. Perhaps that is why ours is not working?
We are converting records from an accounting package called Advantage or Excaliber although the former name is the newest. We shall try again using “inventory part”. Thanks for your valued article. Using Quickbooks PRO 2011.
DaveWV, I don’t use the instructions in the IIF Kit, there is no guarantee that they are up to date.
All I can say is that if you EXPORT an inventory part to IIF, it comes out as “inventory part”, so I always recommend using that when you IMPORT.
Also, IIF is unreliable. You may find that if you perform a “rebuild” of your data file after the import, items will show up that didn’t show up before. IIF doesn’t go through a lot of the error checking that it should. I highly recommend using a tool like the Transaction Pro Importer at http://www.baystateconsulting.com, which uses the SDK instead of IIF for importing. Very reliable.
Charlie,
Used your instructions. Everything worked perfectly! Thanks. I tried several IIF variations, but was never able to get it to function IAW the instructions. I suppose SDK is the best method for anything beyond simple lists.
Again, thanks for your instructions.
hi, the article is very informative, but i want to ask you one thing that i am using QB pro 2007 and we are merging with another company who is using QB enterprise so how am i supposed to export my inventory in that company, and also cutomers list. and what if i want to keep my company name yet still merging with that company, kindly explain both procedures separately.
thanks
Humaira, you can export your customer and item list through the Excel options, and then import them through Excel as outlined in general above.
The advance import feature for importing inventory items from Excel to Quickbooks does not have Unit of Measure in the mapping window. Without U/M for inventory parts the importing of items is almost useless. What good is importing thousands of inventory parts if you have to edit each and everyone with a U/M?
The U/M is exported to Excel if I choose export all items, but there is NO WAY to import the U/M back into Quickbooks because Quickbooks shows has no U/M field to map to.
Canadian Premier Edition 2012 version R5P
John, I don’t have all the different Canadian versions available to me, so I dan’t comment about the Canadian 2012 release.
In the U.S. versions, there is a “U/M Set” field in the Advanced Excel import, which is what you would use for the unit of measure. It works very well.
I took a look at the Canadian 2011 version and I don’t see the field there. So, this is another example of how the Canadian versions are not as developed as the US versions. I have no idea why there would be a difference here. Something for you to comment about to Intuit Canada, I guess.
You may want to look at the import tools from http://www.baystateconsulting.com – if anyone can import that with the Canadian version, they could, but you have to check with them.
Thanks for your reply Charlie.
This is not the first time that QuickBooks shortchanged the Canadian user. They charge $80.00 to $100.00 more for a Canadian version than for a US version. Check it out! Compare prices on Intuit.com and Intuit.ca. There is absolutely NO reason whatsoever to charge as much as they do. Canadian dollar has been at or near par with the US greenback for the last couple of years.
QuickBooks has continually increased the price and yet has not developed the software to function as advertised.
Another feature that the US has is the ability to delete all transactions. We have no such feature. I have just about had it with QuickBooks. At one time, they had a good program but they have screwed with it so much and increased the price that people are getting real tired of being ripped off!
As far as purchasing a third party tool well, QuickBooks should have to provide it to us at no cost. After all, it (QuickBooks) is already costing us lots more than it should compared to the US. Why should we have to spend yet more money to get their software program to work, as it should?
Thanks again for your time.
John, my comment about third party software was just to provide a suggestion as to a way to resolve the issue. Certainly Intuit should include the ability to do what you want, but since they don’t, I’m offering a suggestion on how to work around the shortcoming. I don’t control what they include (or don’t include) in their product.
I realize that it was just a suggestion on your part and I appreciate it.
What gets me Charlie is that in order to get Quickbooks to function properly we have to purchase another program. What is up with that? What are we paying for to begin with? A half finished product that Intuit boasts as the number software for small business. It is not working as advertised and yet they continue to increase the cost.
Thanks again for your time Charlie. I appreciate your effort to help us with this issue.
I am using Enterprise v12 and I am attemting to change the Cost of Goods account and the Income account for select inventory items. The are sub accounts of the original and they are set up in my Chart of Accounts. When I try the import the mapping preview does not show errors, but the accounts did not get updated. I have used the Advance Import function to update cost and price on items previously, and have not had a problem.
Do you have any suggestions?
Thanks!
Ruthann, I would have to check, but you might not be able to make those changes (in some cases) via an import like this.
Edit an item in the item list using the Edit Item window. Any time that you make a change to an account, if QuickBooks opens a window that asks you if you want to update existing transactions, or says that the change would affect transactions before a closing date – any question at all – then you usually can’t make the change in an import. That is because QuickBooks can’t ask you that question on an import, so it just won’t let you make the change that way.
Thank you so very very much for this helpful article! I was worried I’d have to retype all of my items until I read about the advanced import here on your site. Thanks so much.
This article was outstanding and walked me through the entire process. I had been trying – without success – for 1 1/2 days. However, it still did not work for me. At the very END of your article where you tell us what can NOT be done SHOULD BE AT THE TOP. I tried approximately 10 different imports before I got to the bottom and it said you cannot change income account. As it is, income account and COGS account are the 2 I was trying to change.
JoAnn, glad you found this article and that it helped. Guess this is a testimonial for reading the entire article before jumping in?
Is there a way to change the income account and COGS account in “Items” by bulk? I have approx. 2,000 items I need to change. I am trying to use Web Connector to integrate with my Volusion website. The Web Connector doesn’t allow for sub accounts, which is how I currently have my income and COGS accounts. I changed them in my chart of accounts, but now I need to change them in the inventory items. Any suggestions to make this happen without doing them one by one?
I’m not sure how this can easily be done, although I’ve not attempted it. I would talk to the folks at http://www.baystateconsulting.com to see if their import tool would let you make that kind of change. Generally, if it is hard or complicated to do through the user interface of QB, it is at least as hard (or harder) to do through the programming interfaces.
HI I tried to import with my inventory price changes and wont work even if I followed your step by step guide above. what else could be wrong? can you only do an import once a day and try again another day to take effect? I also changed my currency rate, does that have a bearing that my updated excel list wont import successfully?
efi
Efi: There is no limit to the number of times you can do an item list import. Do you get error messages? Do you look at the error report? What version/year/nationality of QuickBooks are you using? Are you using the “multiple currency” feature, and when you say you changed your “currency rate” are you talking about just an exchange rate for some currency in the system or what? You don’t give me much detail to work with here.
Hello Charlie,
Is there a way to alphabetize my items list by name (and not type)from the drop down menu while creating a new invoice/estimate?
Thanks so much.
Nope – they always sort by “type” first. Very annoying.