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.
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 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 account and purchase description. You CANNOT use this to UPDATE the cost 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!
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.
George | Jan 14, 2009 | Reply
Hello,
I am using Premier 2006 with 5 price levels activated. In exporting “Items” to an Excel or .IIF file structure, the prices established for the various price levels (2,3,4,5) are excluded from the exportation, while the “standard price” is included. I was attempting to export those prices levels (with the existing price data) and then modify the prices easily, and re-import to QB. However, the system will not export price levels beyond the “standard price” per item. Is there a “work around” or add-on…or does QB software simply not allow the price level prices to be manipulated by anyone. Thanks for your work on these forums.
Charlie | Jan 14, 2009 | Reply
George, you can use the IIF method to export/import pirce level lists, but ONLY fixed percentage lists, not per item lists. That kind of price level was developed after Intuit stopped updating the older IIF method.
I’m looking into a third party import/export utility, but I’m not sure at this time if it supports price level lists.
You can work with them if you get the ODBC driver, which can work with Excel, but that is a bit complicated if you haven’t worked with ODBC. I could also write a custom import/export to work with these lists.
HARRY hARALAMBOUS | Jan 22, 2009 | Reply
Dear Charles,
Thanks for a very interesting Blog. Unfortunately we need to use the UK version of QB Pro 2008 and an awful lot doesn’t apply. No Advanced import and mapping doesnt show custom fields. Any suggestions for imprting into custom.fields? Ive tried importing into an unused field (manufaturers Part No) and then copy paste into the custom field (barcode)? its taking forever. Any suggestions?
Best Regards
Harry
Daylin | Jan 22, 2009 | Reply
Charlie,
This was very helpful. However; i need to import a new item list all together and wish to deactivate the current one. Rather than clicking on each item one by one and deactivating them, is there an easier way to do this?
Thank you.
Charlie | Jan 22, 2009 | Reply
Daylin: You can change the “active status” of the items to inactive via the export/import. Export your list to Excel, change the “active status” value for each line from “Active” to “Not-active” (spelling must be exact), map an import that includes just the active status (”Is Inactive”), type and item (”name”) columns and import – that will turn your items to inactive items. Then you can add new items.
Charlie | Jan 22, 2009 | Reply
Harry: I don’t have the UK version set up on a system that has Excel, so I can’t test this out in detail, but I did play with the CSV file format. The UK 2008 version is similar to the older US versions – if you click on the “Excel” button to do the import, you are seeing the equivalent of the “advanced” import. So you have it. There are several differences from the US version- some “type” values are different, for example. And, from what I can see from a quick look, custom fields are not supported.
You do have the IIF file export/import function (under “File” then “Utilities”), and that should support custom fields. It won’t support the MPN field. I don’t usually work with IIF so I won’t go into detail on that. I use utilities from Big Red Consulting (www.bigredconsulting.com) to handle IIF files – I don’t know if they support the UK version.
Daylin | Jan 22, 2009 | Reply
I succesfully inactivated all items with the import export method. When i import the new item list it says i have imported all 6k items, but it doesn’t show on the item list… help?
Charlie | Jan 22, 2009 | Reply
Daylin: if you didn’t get a message saying that there were errors (which you have to save in a file to look at) the next step I would take is to do a “rebuild” of the data file – under “File” then “Utilities”. Sometimes the indexes don’t get updated properly.
Also, before that – are you sure you imported them as “active” items with different names than the inactive items in your original list? Make sure that your item list is set to show inactive items, to check.
Daylin | Jan 22, 2009 | Reply
The items need to have new item names in order to import and show up?
Charlie | Jan 22, 2009 | Reply
If you had item “A” in the list, and you first changed it to “inactive” – item “A” is still in the list. Just inactive. If you do an import for item “A” again, it is going to update the existing item, because it has a match. If a name matches, it updates. If you want to add a new item, you have to give it a new name.
Daylin | Jan 23, 2009 | Reply
Charlie, will inactivating items effect any old records such as purchase orders, sales order, invoices etc.?
thank you.
Charlie | Jan 23, 2009 | Reply
No, all that really does is to “hide” the item from the dropdown lists
Harry | Jan 23, 2009 | Reply
Charlie
Before i could read your suggestion I tried a work round of course did a back up,ive been bitten before. I exported the item list to excel put the barcodes into manufactuers part no reimported the excel file and re-exported as iif file hoping to copy paste and lo and behold when i opened as excel file directly there were five custom fields listed as customfield1 etc but no manufacturers part number field so I inserted columns in the iif field, imported part no Description and barcode to these to check alignment with original iif data (most data was coming out of another accounting program} reimported iif, custom field 1 which was already labelled barcodes and ticked use, file and success. Two things useful to your readers if some of the original data is chamged in iif file it doesn’t always reimport correctly and iif export file has data columns not in excel export and vice versa so on occassions one will do the job and the other won’t.
Now all we have to do is try and connect a hand scanner to it. Hope god has come back from holiday.
best regards
Harry
Charlie | Jan 23, 2009 | Reply
Yes – the IIF format (which I haven’t written much about) has things that the Excel format doesn’t – such as the ability to import inventory assembly items and the BOM. But it was frozen several years ago, so new things like the MPN aren’t there. Excel has many of the new things, but for some reason they don’t support all of the features in the current database, even some things that IIF supports. It can be frustrating. There are several third party tools that can help, but I haven’t found a PERFECT solution that covers all files and records at an affordable price. I’ll be writing more about other tools in the future.
Daylin | Feb 17, 2009 | Reply
Charlie,
do you know of any good website where i can ask general questions about QB E.9? I need to knwo if there is a way to have the phone number, fax etc field automatically fill in for each sales order,invoice etc….
Thanks.
Charlie | Feb 17, 2009 | Reply
Daylin: The Intuit Community web site forums are actually pretty good. I am one of several people who do answer questions through that. Try http://community.intuit.com/categories/contents/199
It is a user-to-user forum, sometimes you get good answers, sometimes your question doesn’t get a quick answer. I generally recommend trying it. If you have lots of questions, don’t pile them all into one discussion, as that makes it harder to answer (and less likely to get an answre).
There are other alternatives that are fee based.
Shubhangi | Mar 10, 2009 | Reply
Hi Charlie,
Thanks a million for this great blog for quickbook newbies like me. I saw your reply for the George’s question regarding importing/exporting price levels. Is there any third party tool I can use for import/export of price levels for the same item?
Thanks,
Shubhangi
Charlie | Mar 10, 2009 | Reply
Shubhangi, I’ve not found a third party import tool that will import this kind of list item – but I’ll admit that I haven’t searched very hard. The import tools I usually work with either don’t handle lists (just transactions) or use IIF which won’t support per-item price level lists.
I will be releasing an import tool of my own that will handle this – it should be available by mid April if not sooner. If you would like to contact me directly about this you can click the “about” link at the top of the page, where you will find my contact information. I’d rather not put a lot of information about that product in this comment section as the product is not quite ready for release at this time.
Shubhangi | Mar 10, 2009 | Reply
sounds great.. the tool would be very helpful for our company.
AJ | Mar 13, 2009 | Reply
I have had QB Pro 08 for about a year and this is the first time I have found any help that is useable. I am still paying for the phone support which seems to be a “you need to upgrade” broken record. Thanks for being available!
I need to change all/most of my items to inventory from non-inventory items. I will use this opportunity to expand the item descriptions and hope to put in the vendor at the same time.
Two questions:
1) If I change the existing non-inventory items to inventory items, can I set the date back to the starting of the company in order to allow the purchase and receipt of the goods we received last year or does QB automatically do that any way? We started the company in March 08, received our inventory and started doing business. We have since added items and received other shipments of both the original items and the new items. If I add items one at a time I have the option of setting the date back to the starting date. I have not recorded any purchases in QB yet and want to get them in.
2) I do not know which cost of goods sold account to use. These items are imported from China, some with duties and some without. Sometimes we will ship from China directly to your customers and sometimes we will take delivery. At this time we deal only with wholesale and therefore do not need to collect sales tax (Virginia). Any help would be appreciated.
Thank you.
Charlie | Mar 13, 2009 | Reply
AJ: These questions are off topic, and a blog like this isn’t a great way to answer complicated questions. To fully answer you I would need to know more about your situation. Also, I’m not a CPA.
If you convert non-inventory items to inventory parts it has a potentially dramatic effect on how COGS is calculated, usually. The effect is retroactive for the entire history of the file. If you have produced financial statements for the prior year, and used these items in transactions in the prior year, this can affect those statements for closed periods and that might not be what you want. The best I can say is to make a backup copy first before you try this (or, make a copy of your company file to set up as a test company and try it there).
COGS for your imported items depends on several issues. That really is something that you need to talk to an accountant or CPA about. There are tax implications that I’m not comfortable talking about. It depends on if you are treating them as non-inventory items, inventory items or whatever, and if you ever hold on to the items.
Sorry I can’t be more specific, but without knowing a lot more about your situation I can’t offer detailed help.
AJ | Mar 13, 2009 | Reply
Sorry. I hope to make these changes with the import from Excel so I mistakenly thought this was on topic.
Thanks for the reply, The “The effect is retroactive for the entire history of the file.” is what I hoped would happen.
Thanks again
NM | Mar 17, 2009 | Reply
Thanks for your blog,it was helpfull,I have imported my inventory into QB sucessfully without any error but I am facing a problem eg when i treid to create a purcahse order QB doesnot pick up the description column, in item column the name is there but description column is blank.
Please help me!
Charlie | Mar 17, 2009 | Reply
NM: Note that for inventory parts (and other items) there are two descriptions – the “Description/Description on Sales Transactions” and the “Description on Purchase Transactions”. Make sure that you have imported both…
NM | Mar 18, 2009 | Reply
Thanks Charlie now it works.You topic was of great help to me.
Thanks once again
NM
Owen | Mar 26, 2009 | Reply
If possible, I would like to follow up on a comment from Roger Strickland from June 10, 2008 regarding the import of items into QuickBooks with units of Measure.
We have a test company file set up in QB Enterprise 9.0 with 2400 inventory part items. We have about 150 Unit of Measure Sets along with designations for Base, Purchasing, Sales, and Shipping Units of Measure that are set up for about 2200 of the items. We would like to take this information from the first file and export it into a second company file if possible. The second company file is currently being used for QuickBooks payroll and will be used for all company transactions once it is set up.
Is it possible to export the item information including the Unit of Measure information from the first file into the second file? Do we need to set up the Units of Measure in the destination file prior to the import? We have had good success in importing the item information including custom fields from the first file into the second, but we have not had much luck with the Units of Measure.
Thanks for any input.
Alan M | Mar 28, 2009 | Reply
Thank you for the article.
Tried to find in article, comments, and at ‘Intuit Community’, may have missed it… is it possible to export entire item list to Excel file and then import only ‘additions’ to the list (ie delete all existing item rows in Excel and keep only new items… assume header row must remain in the spreadsheet at top(?)) ?
Thanks again.
Charlie | Mar 29, 2009 | Reply
Alan, You can alsways export the list to Excel. You can also import an Excel list and add items (most item types – you can’t import some like Assembly items). I’m not clear what you are saying – export a list and then only import new items – if they are already on your exported list, they aren’t “new” items? Or am I not understanding what you are asking…
CoolMomLes | Apr 6, 2009 | Reply
This is GREAT! Thanks for your detailed instructions with screen shots, it made my import chore a breeze! One question, the “as of” date, I would like to set for 12/31/08 and enter in all my new parts to be on hand as of beginning of this year. But, I don’t see that field on the edit items. Similar to the ‘location’ field is only found on the fixed assets listings, do you have any idea where the as of date field is, and if I can map it to 12/31/2008 or 1/1/09?
CoolMomLes | Apr 6, 2009 | Reply
hah! disregard question. i found it, as it is only listed for new items, which that applies to 99% of my item list, so i will go back in, restore my original file to just before the import so the data is no longer there, then adjust my mapping to include the as of date, and should be no problem.
again, your blog here, and also reading all the subsequent q&a has been a tremendous help!
Cheryl | Apr 13, 2009 | Reply
I’m a new user and trying to an import of my inventory and non-inventory items. Inventory imports fine, but my non-inventory items are purchased directly for jobs, and I’m unable to figure out a way to check the box that will allow for that on the import. I would like to enter an expense and revenue account for those items, but can’t get that done on the import. Can you please help?
Charlie | Apr 14, 2009 | Reply
Cheryl: Unfortunately, there isn’t a way to check that box when importing a new non-inventory item, so you are out of luck. You would have to try one of the third party import tools based on the SDK, or have a custom import written. The Excel import has lots of limitations, and this is one of them.
Charlie | Apr 14, 2009 | Reply
Cheryl: I’m going to correct my last posting (and update the article to include this) – Another QB user, Laszlo Tordai, pointed out to me that you CAN import NEW items and have this box checked – you need to create a column in the spreadsheet that has a “Y” or “Yes”, and map that to the “Is reimbursable charge” field in the advanced import. If you do this (and map the COGS account to a column) you can import the item with that box checked, with the additional cost account. When you Export the item list this column isn’t created, which is why I missed it. Thanks, Laszlo!
Beth | Apr 25, 2009 | Reply
This article was a tremendous help. I was ready to ditch QB and just keep inventory in EXCEL. After reading these instructions at least I could understand the process. My error log did not look or act like yours but I was able to work through it and I am able to upload my inventory.
Thank you.
BethK | May 14, 2009 | Reply
I’m just starting to use QB 2009 for Mac and it seems that I can’t use your wonderful descriptions for exporting, editing, and then importing my inventory. Drat! Intuit seems to have left that feature out of the Mac version. Using the IIF format seems unreliable. So I guess I’m going to need to do a lot of point and click.
Thank you so much for your discussion of groups. That looks like it will work for my situation. I had hopes that this importing would work, too.
Charlie | May 14, 2009 | Reply
BethK: I don’t add the disclaimers “This might not work on the Mac version, or some non-US versions” on all of my postings, but sometimes I think I should. I don’t know if the Mac version will ever catch up with the Windows version. I doubt it – but a lot of Intuit’s work is looking at web based development, and that should be available to everyone. Note also – there isn’t a programming toolkit for the Mac version, so any third party add-on in the Intuit Marketplace cannot work with the Mac version.
Rodney | Jun 20, 2009 | Reply
Charlie,
Thanks for the information! I have several non-inventory items that I would like to make inventory items. I tried importing but QB issues an error stating I can’t change the “account type” on the non-inventory items. Any suggestions? Thanks!
Charlie | Jun 23, 2009 | Reply
Rodney, you can’t make most type changes via the import – anything where they either don’t let you in the user interface (UI) of QuickBooks, or where they ask you a question before proceeding. Your option is to do it manually.
Nathan Sleadd | Jul 22, 2009 | Reply
Quick question- is there a way to edit Inventory *Assemblies* using Excel? All I need to do is enter values into a custom field.
Charlie | Jul 22, 2009 | Reply
Nathan, you can look into the IIF file format – I don’t work with it often, so I can’t say if it is feasible or not. IIF is complicated to work with.
Gaston M. San Roman | Aug 14, 2009 | Reply
Hi Charlie,
We are in the process of migrating our current system to QB Enterprise 9.0. One of the issues is to be able to import inventory assembly items. I know this can not be done with excel but you may do it through an IIF file. I am just trying to find a sample IIF file for Inventory Import with all the fields so I may edit it and do the migration without buying a 3rd party utility.
Thanks in advance for your help and congratulations for a great article
Charlie | Aug 17, 2009 | Reply
I would suggest creating a new “test” company, creating one inventory part, creating one inventory assembly that usese that part, then EXPORTING the item list to IIF. Then you have a template for what the IIF file should look like.
You can also look at the IIF reference at http://support.quickbooks.intuit.com/support/pages/knowledgebasearticle/bde7b06f
Josh | Sep 2, 2009 | Reply
is there any way to import inventory into QB from excel
w/o switching to single user
Charlie | Sep 2, 2009 | Reply
Josh: Not without going to a third party product of some sort (and I don’t have a recommendation at this particular time). But, then, importing should be something you only do infrequently, so it shouldn’t be that big of an issue usually.
Josh | Sep 3, 2009 | Reply
Thanks for your help
and if you come across a 3rd party product for that
I would appreciate if you could email me
Jim Bovard | Sep 14, 2009 | Reply
Hello Charles,
Thanks for your very helpful website. I am having troube importing. I am trying to import my first Item list after updating it. My issue: the ‘IMPORT DATA’ column on the mapping dialog box isn’t populate with the Excel data…it just stays blank. The file is open, the sheet is slected, the header box is checked, I can see the ‘Quckbooks’ column after I select the import type, but the ‘IMPORT DATA’ column is blank.
I’m using Excel 2007 & QB Premier 2009. I’ve save the execl file as xlsx, xls, cvs. All have the same ressult.
I can export fine, and am actually trying to import back the item list I sucessfully exported and updated.
Any suggestions what I must be missing?
Thanks,
Jim B
Jim Bovard | Sep 14, 2009 | Reply
Charles,
Please forgive me. I figured out me problem. I didn’t understand that I needed to click each line and wait 3-5 seconds for the drop-down arrow to appear. I guess there are no dumb questions, but this feels like one to me.
P.S. Thanks again for all your helpful advice. I am really looking forward to your column on setting up Item List. Hope you can write it soon. All my best.
Jim B.
dan | Nov 18, 2009 | Reply
I have done the above importing my list of items and when it’s complete only one item out of the list gets imported, it just randomly picks an item off the spreadsheet and doesn’t bring any others in.
Charlie | Nov 18, 2009 | Reply
Dan: Do you get errors listed? Does it tell you that they were imported correctly even if they don’t show up? If it says they were imported but they don’t show up, what happens if you do a “rebuild” of the file (File/Utilities/Rebuild Data)?
HK | Dec 23, 2009 | Reply
Charlie,
I imported the list and it shows me message import done with 0 errors but when I go to my item list I can just see 4 items. I don’t know where remaining items went? Please help me with this problem. Thanks
Charlie | Dec 23, 2009 | Reply
HK, try the following things, in turn (if one doesn’t work, try the next):
1) In the item list, click the item button at the bottom, and select “show inactive items”, to make sure they aren’t inactive.
2) Same place, select “re-sort list” and see if that brings them back.
3) If all else fails, “rebuild” the database. This is under File/Utiltities. It first makes a backup copy of your file, make sure you have that in a safe place in case something goes wrong (usually not, but I’m paranoid).
HK | Dec 24, 2009 | Reply
I tried all 3 steps none of them work?
Charlie | Dec 28, 2009 | Reply
Were you using the “advanced Excel” import, or another method?
Unfortunately, without having my hands on the files, there isn’t much more that I can tell you.
AW | Jan 5, 2010 | Reply
Charlie,
I have followed the steps that you listed to import from Excel and it seems to work fine with the exception of bringing in the COGS account and the Cost. Any idea as to why it wouldn’t be working correctly? I have double checked my mapping and the formats were copied directly from an items list export.
Thanks!
Charlie | Jan 5, 2010 | Reply
AW – what “item type” are you importing, what version/edition/year/nationality of QuickBooks are you using?
AW | Jan 5, 2010 | Reply
I’m importing Non-inventory items using Quickbooks Premier Contractor 2008 US
Charlie | Jan 5, 2010 | Reply
AW — read the third bullet point under Problems/Restrictions/Notes in the article.
I can map out the cost field fine, to get COGS you have to make it a two sided item, I believe.
Gene | Jan 21, 2010 | Reply
I’m attempting to import an item list, I selected the file from excel (I exported, modified pricing and saved) and QB will not allow me to import. It says only CSV or excel files are allowed. It is an excel file. I attempted to map the file and the drop out does not appear. When I choose add or select a map, no drop down, I select Mapping and it asks me to Specify file to import.???
Gene | Jan 21, 2010 | Reply
I’m attempting to import an item list, I selected the file from excel (I exported, modified pricing and saved) and QB will not allow me to import. It says only CSV or excel files are allowed. It is an excel file. I attempted to map the file and the drop out does not appear. When I choose add or select a map, no drop down, I select Mapping and it asks me to Specify file to import.???
Charlie | Jan 21, 2010 | Reply
Gene, you can also try the import tool that I review at http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/
Make sure that you didn’t damage the file when you edited it. A simple test is to export the file, delete all lines except one item, change the description to say “imported”, and then try the import. That is with just minor changes. Sometimes QB can get really cranky with this import feature.