Importing 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.
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.
- 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!
Elisa O"Brien | Apr 30, 2008 | Reply
Thank you for your support. I am correcting an item list that is a mess. This is a drop ship company. There is one item that is manufactured that I will put in a group when the dust settles. My challenge is 3 or 6 fold. I need to change the inventory items to non inventory items and the non inventory items to inventory items. Change cost & price on some. Makes sure that the non inventory items hit the COGS. Please help!! ASAP Thank you.
Charles Russell | Apr 30, 2008 | Reply
Thank you for your comment, Elisa.
You can’t change an inventory part to a non-inventory part, unfortunately. You would have to make the existing item inactive, rename it, and add a new item with the new type.
Changing prices is easy, you can do that in QB directly or use the Excel export/import if that works better for you.
Changing costs is trickier - there are two cost figures in QB. The “real” cost, the one that affects your COGS, is the average cost. You can’t change that directly, you can’t change that for an existing part via Excel.
Costing on non-inventory parts is different than inventory parts, usually the cost hits when you enter the bill for the item.
Roger Strickland | Jun 10, 2008 | Reply
I am setting up a new company. I have all of the inventory items set up in excel, when I import them into QB there is no unit of measure. I did not see a option for the unit of measure in the mapping section. Is it possible to get a unit of measure imported because I have alot of items to be imported, some per foot, some per each.
Charles Russell | Jun 10, 2008 | Reply
Roger, what version/year/country edition of QuickBooks are you using? Unit of Measure support in the item list import is only available in the 2008/US versions, I believe. I see it in my copy of Premier 2008, but not in Premier 2007, for example.
In Premier 2008 the method varies slightly depending on the mode you have selected for the unit of measure feature. I can go into this further if there is interest.
Ann | Jul 30, 2008 | Reply
Thanks so much for your information…it has been very helpful. I, like Elisa, am trying to reorganize my item list. When I first started my business I had alot of work and not alot of time to get organized. Now I am thinking I would like to renumber my item list with a new numbering system I have created and add a bunch more. I am worried that if I rename/renumber my current items it could effect my financial statements/income accounts/processed invoices negatively so that my reports don’t show the correct values for income. If you could please let me know if this is true that would be very beneficial before I start my process of reorganization.
Thanks for your help!
Ann
Charles Russell | Jul 30, 2008 | Reply
I’m glad that this is helpful - let me know what other kinds of topics you would like to see discussed!
Renaming items is simple, although it could be tedious. You won’t do this via an Excel import, however. If you export to Excel and then change the name of the item, and reimport the list, that simply adds a new item with the new name. The Excel import keys off of the item name.
You can change the items directly in the “Edit Item” window in QuickBooks. Make a backup copy first (you shouldn’t need it, but ALWAYS make a backup copy or use a test company before trying something new). In the item list, double click on the item to open the “edit item” window. Then change the name of the item and click “OK”. QuickBooks will change the name of the item in EVERY TRANSACTION in the file, without affecting any of your financial statements. Internally, QuickBooks identifies the items by an internal code number that you don’t see. The “name/number” is just a label, not the “real” ID for the item, so it is safe to make this change.
Ann | Jul 30, 2008 | Reply
There are a few items that I may want to change from inventory to non-inventory therefor I read above that I will have to make them inactive and rename them. What does making an item inactive actually do? I assume it does not affect past transactions or reports? One other question off this subject is….When Quickbooks shows you “income” in reports does this income amount include sales tax or does that get removed since I have already set up my sales tax values and marked which customers/items are taxed?
Thanks again for the quick reply. I wish I could always get answers this fast. I am self taught in Quickbooks so there are still some gray areas.
If I think of some additional topics I will pass them along.
Sincerely,
Ann
Charles Russell | Jul 30, 2008 | Reply
You are correct, you can’t change the type from inventory to non-inventory in QuickBooks. By making the existing item “inactive” all it does is hide it from most reports and drop-down lists, so you don’t see it cluttering things up. It’s still there, so there is no change to your financials.
I don’t usually go off topic in these comments - the best way to get quick (free) answers is by posting a question in the user forums at http://www.quickbooksgroup.com, where you will find many people who are willing to answer questions. The short item is that sales tax will be posted to a current liability, not an income account.
Sudha | Aug 26, 2008 | Reply
Thank you so much for the very clear instruction it worked very well. One quick question, when importing the new setup for items I did not incounter any question regarding changing old data on invoices. Does this mean it only effects all new invoices?
Thanks for your response.
Charles Russell | Aug 26, 2008 | Reply
Thank you for your comment, Sudha. You don’t mention what data fields you were changing. Most changes in the user interface don’t affect existing invoices. Some do prompt you to ask if existing transactions should be affected.
I will guess that the import just assumes “no”, BUT that is just a guess. I have not looked at that issue.
Sudha | Aug 26, 2008 | Reply
Hi Charles
Thanks for responding immediately. I was trying to add some custom fields to the item list. And was looking for a way to have those fields applied across the past invoices. While importing I did not see any option that asked me this question if it needs to be applied on past invoices.
And I tried adding new invoices and did see those custom fields show up. The purpose was to get a report based on those customer fields from the previous invoices too. So was wondering if there was any way to do that.
thanks once again for responding
Charles Russell | Aug 26, 2008 | Reply
Sudha, that is complicated. However, in this case, you can’t do it this way. The item list import will just enter values into the item list. That doesn’t create the custom field in any transaction. Custom fields are populated to the transaction when the transaction is created. A copy of the value from the item list is moved to the transaction. These transactions don’t reflect changes made to the item list. So you can’t do it this way.
Joanne | Aug 28, 2008 | Reply
This is very useful, but I have another, related problem in POS. I am importing a very large spreadsheet using the spreadsheet provided by POS. My problem is understanding how to use the excel sheet to import items that are sold in multiple quantities - box, carton, skid. The regular price would be per box, but where do I enter the price for a carton and a skid? Are they separate spreadsheets with the same part numbers or what column do I put the additional prices in - then, how do I deal with the Base Unit of Measure column?
Any help you can provide, or if you have an example that you could post, that would be appreciated. Thanks in advance.
Charles Russell | Aug 28, 2008 | Reply
Joanne, my apologies, but I’m not a POS expert, and QB POS is very different than QB Pro/Premier/Enterprise. I don’t have an answer for you at this time. You may want to look into the POS forums at http://www.quickbooksgroup.com/.eea20f3/
Carmen | Sep 14, 2008 | Reply
Hi, I am trying to change the account type for all my items. The goal is to seperate type of sales or catagories. e.g. brand name, remanufactured, etc. I have imported many times but this one has me stumped. I have created each account in the chart of accounts, but it still keeps saying “the posting account cannot be changed”. Ironically, it lets me change each one individually in the items list? That would take days. (10,400 items)Any help, upgrade recommendations, etc?
Charles Russell | Sep 15, 2008 | Reply
Carmen - try the import with just ONE line in the import, as a test. If that does not work, tell me what “type” of item you are trying to import.
Julie | Oct 24, 2008 | Reply
Love this information and I have already done the font size change!!! Thank you!
Now comes my quandry…I have a large item list to which I have added several customized columns. I want to export to Excel, put in some formulas to update the values and then import the new data back in to QB. Must I include the required columns of Type and Account in this export/import? I do not display those columns in my item list currently. I have actually set up memorized reports for the items that I wish to export, tweak and import back in and am doing so through that memorized report…does this present a challenge or problem when I do the import back in? I noted that you used the export feature on the Item List rather than via a Memorized Report.
Thank you in advance for your information.
Regards,
Julie
Charles Russell | Oct 24, 2008 | Reply
Julie, to export from a report and then try to import back in can be very time consuming. I recommend that you use the Excel export that you find in the “Excel” button at the bottom of the item list. That will give you all items and all columns, but you can filter things there. Report exports are going to be more complicated to work with (but you can try). The minimum fields required are type and name if you are UPDATING. The accounts are only needed if you are ADDING items.
Amanda | Oct 29, 2008 | Reply
Thank you so much for this tutorial. I was racking my brain trying to figure out why my Parts List was not importing into quickbooks. Your detailed descriptions, screen shots, and answers to questions from others is just what I needed. You got me a pat on the back from the boss man. You rock!
Justin | Nov 5, 2008 | Reply
Hello, my question just relates to the actual import function within Quickbooks. Basically, we are running Quickbooks 08-09 Plus, however, we do not have an import function in th program. So we can export lists and modify however, we can not import. Have you encouteredt his issue - does it relate to our Quickbook version??
Please, any advice will be Great
Charlie | Nov 5, 2008 | Reply
Justin, I’m guessing that you have an Australian or other non-US edition of QuickBooks, as that naming convention isn’t used here. My tutorials all apply to the US edition. The Australian edition shares common roots with the US edition, but is a very different program now. I don’t have a copy of that so I can’t comment much. Most of the third party products that are available won’t work with the Australian edition as it has a different programming interface, too.
If I’m incorrect in my assumption then let me know…
Tara | Nov 17, 2008 | Reply
I am trying to import some new items into my QB2008 edition but I keep getting the same error and I don’t know what I need to change:
Account/Income Account: The specified Account does not exist in the list. | Expense/COGS Account: The specified Account does not exist in the list. | A posting account must be specified.
Any idea on what my next step might be?
Charlie | Nov 17, 2008 | Reply
Tara: Every item that you are adding must have one or more “accounts” associated with them. An inventory part, for example, needs an asset account, cogs account and sales account. You can see this if you add an item through QB itself.
You have to map a column for the account types that you are importing. The accounts you list in those columns must already existin in your chart of accounts.
The easiest way to see this is to add an item of the type you want to add, using the Edit Item window. Then export the item list, and look for that particular item. You will see the accounts that you used, then you can duplicate that for your import.
Tara | Nov 17, 2008 | Reply
Thank you very much for your help, I was able to get it imported now!
Dan - AxonWare | Dec 1, 2008 | Reply
Superb article - and a lot of what you are saying holds true for European versions of Quickbooks too.
The nomenclature of item types seems to have changed between v2005, 2006 and v2008 in the European editions.
So when someone tries to import an item list to a new Quickbooks v2008 data file (from say a v2006 IIF file) they get spurious results.
The trick is to export a sample v2008 item list first (to find if your items should be called “Inventory Part”, “Stock Part” or whatever). Modify the v2006 item list in excel - before re-importing to the v2008 data file.
Mark Smith | Dec 12, 2008 | Reply
Hi Charles,
We are new to QB and need to update the item cost on a monthly basis. I understand this could be done per item, but is there a modification available to allow us to do this based on a percentage factor for all items or a group of items?
We have several thousand items and with global steel prices fluctuating daily we will need to update our item cost monthly.
Thank you!
Charlie | Dec 12, 2008 | Reply
Mark, there are two cost fields in the item list (not counting assembly items) - “cost” and “avg cost”. You can’t easily modify “avg cost”, that is automatically calculated based on your purchase cost. This is the cost that your inventory is valued at. The “cost” field is not an accounting figure, it represents (usually) the last received cost for an item, which is what shows as the default in a PO. This you can edit - and you could do a mass update from an Excel spreadsheet.
Peter Cullen | Dec 29, 2008 | Reply
Charlie:
This question has to do with importing the complete BOM as an Assembly item in a QB Enterprise 9.0 file. In a previous post, I believe you mentioned that this could only be done using the IIF format, and was quite complicated. Is that still the case ??
Thanks Peter
Charlie | Dec 30, 2008 | Reply
Peter, they have not changed this feature in the 2009 release. You still either have to use IIF, or find some other import routine. I’ve written a couple of BOM structure import routines as custom programs.