Importing QuickBooks Inventory with Excel

| April 29, 2008 | 228 Comments

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

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

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

This article was updated on April 14, 2009.

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

Overview

Here is the general procedure that we will follow:

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

Export the List

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

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

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

Your spreadsheet will look something like the following:

Create/Edit the Items

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

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

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

To add an inventory part item, you must have:

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

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

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

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

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

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

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

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

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

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

Make a Backup

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

Start the Import

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

In QuickBooks 2008 you will see the following screen:

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

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

Map the Columns

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

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

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

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

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

Import

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

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

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

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

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

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

Review the Error Log

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

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

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

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

Problems, Restrictions, Notes

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

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

Conclusion

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

Thanks!

Tags: , ,

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 Accountex Report blog, as well as his California Wildflower Hikes blog.

Comments (228)

Trackback URL | Comments RSS Feed

  1. Elisa O"Brien says:

    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.

  2. 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.

  3. Roger Strickland says:

    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.

  4. 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.

  5. Ann says:

    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

  6. 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.

    • Meir says:

      RE: 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.

      >>> Is there any way I can export this ID too, so I can change the Item Name in bulk and will match by this ID?
      I found someone posting this idea somewhere “I found the trick. In the export file, there is a USEID field.
      When you change it to “Y”, QuickBooks will use the ID (REFERENCE
      field) instead of item name during the import, so you can rename
      anything you want as long as you keep the ID”,
      but I can’t identify where to get this column along with my Item export!?

      • Charlie says:

        Meir, that isn’t a part of the Excel import/export capability. That is a part of the IIF file format. You can see SOME info on IIF at http://qbblog.ccrsoftware.info/2010/02/quickbooks-add-on-programs-iif/. I’ve not done any work with the USEID field. Also, I am very, very cautious about any recommendation to use the IIF file format, because if you make a mistake in the file it can clobber your QuickBooks database. There is very, very little error checking there.

        You may want to look into the Transaction Pro Importer and Transaction Pro Exporter products from http://www.baystateconsulting.com. I don’t know if they work with the internal ID or not – but if they do, they would be the best way to do that kind of work. Check with them.

        Or, you can try the QODBC driver, from http://www.qodbc.com. I believe that they would export and import using the internal ID.

        Both the Transaction Pro products and QODBC driver (which you would use with Excel, for example) use the QuickBooks SDK, which is a much safer way to integrate with QuickBooks databases.

  7. Ann says:

    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

  8. 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.

  9. Sudha says:

    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.

  10. 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.

  11. Sudha says:

    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

  12. 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.

  13. Joanne says:

    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.

  14. 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/

  15. Carmen says:

    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?

  16. 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.

  17. Julie says:

    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

  18. 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.

  19. Amanda says:

    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!

  20. Justin says:

    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

  21. Charlie says:

    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…

  22. Tara says:

    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?

  23. Charlie says:

    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.

  24. Tara says:

    Thank you very much for your help, I was able to get it imported now!

  25. 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.

  26. Mark Smith says:

    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!

  27. Charlie says:

    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.

  28. Peter Cullen says:

    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 says:

      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.

  29. George says:

    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 says:

      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.

  30. HARRY hARALAMBOUS says:

    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

    • Charlie says:

      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.

  31. Daylin says:

    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 says:

      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.

  32. Daylin says:

    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 says:

      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.

  33. Daylin says:

    The items need to have new item names in order to import and show up?

    • Charlie says:

      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.

  34. Daylin says:

    Charlie, will inactivating items effect any old records such as purchase orders, sales order, invoices etc.?

    thank you.

  35. Harry says:

    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 says:

      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.

  36. Daylin says:

    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 says:

      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.

  37. Shubhangi says:

    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 says:

      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.

  38. Shubhangi says:

    sounds great.. the tool would be very helpful for our company.

  39. AJ says:

    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 says:

      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.

  40. AJ says:

    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

  41. NM says:

    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 says:

      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…

  42. NM says:

    Thanks Charlie now it works.You topic was of great help to me.

    Thanks once again
    NM

  43. Owen says:

    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.

  44. Alan M says:

    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 says:

      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…

  45. CoolMomLes says:

    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?

  46. CoolMomLes says:

    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!

  47. Cheryl says:

    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 says:

      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.

  48. Charlie says:

    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!

  49. Beth says:

    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.

  50. BethK says:

    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 says:

      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.

  51. Rodney says:

    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 says:

      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.

  52. 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 says:

      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.

  53. 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

  54. Josh says:

    is there any way to import inventory into QB from excel
    w/o switching to single user

  55. Charlie says:

    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.

  56. Josh says:

    Thanks for your help
    and if you come across a 3rd party product for that
    I would appreciate if you could email me

  57. Jim Bovard says:

    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

  58. Jim Bovard says:

    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.

  59. dan says:

    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.

  60. Charlie says:

    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)?

  61. HK says:

    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 says:

      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).

  62. HK says:

    I tried all 3 steps none of them work?

  63. Charlie says:

    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.

  64. AW says:

    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!

  65. Charlie says:

    AW – what “item type” are you importing, what version/edition/year/nationality of QuickBooks are you using?

  66. AW says:

    I’m importing Non-inventory items using Quickbooks Premier Contractor 2008 US

  67. Charlie says:

    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.

  68. Gene says:

    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.???

  69. Gene says:

    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.???

  70. Charlie says:

    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.

  71. Nihar Upadhyaya says:

    I have been using Qbooks for quite a while. Am using Ent 8.0 version. I had a problem because we manufacture products and each product is a custom item and was in a need where I can import the assembly items from Excel to quickbooks item list.

    However, went to plenty of forums and was not able to find anything helpful and everyone said that you cannot import inventory assembly directly into qbooks. So I have improvised and have been able to achieve the same. I would like to share it with everyone and especially who are in the same boat as me.
    1. First- You will need to create one item of each type of inventory assembly items manually in qbooks. Just like how you create a new item.
    2. Go to File—Utilities—Export—-iif files and check the box for items only.
    3. Select a location where you want to export the item list (.iif file).
    4. Open the exported iif file using excel. Because it is an iif file it will consider it as a csv or delimited text file. Just keep hitting next, next and finish on excel dilimited screen.
    5. Once you click finish, you will see the excel datasheet which looks like an excel datasheet.
    6. Locate the inventory assembly item which you are trying to duplicate or make a copy with the exact Bill of Materials (Raw Materials).
    7. Start copying everything from start of (!invitem). Select the whole row and each of the next of the rows till you see the row which has (EndAssembly)- So you will select total of 1 row for the inventory assembly item name and description + all the rows for all bill of materials (if you have 4 Bill of Materials you will selected all 4 rows and they should be the 4 rows after the name for inventory assembly) with the row which has the headers for Bill of Materials+ Row which contains !InvItem + Row which contains EndAssembly so total of (1(!InvItem) + 1 (Inventory Assembly item description and Name) 1 (BOM header)+4 (BOM) + 1 (EndAssembly)= 8 Total Rows to be selected.
    8. Copy them to a new excel datasheet. Now you can copy the same 8 rows for as many times as you want. So lets say if you want to add 10 similar items like the one you copied with same or similar Bill of Materials. Change the actual bill of materials or quantity of each one of them independently as you would do when you create a new item in Qbooks. Just make sure that the Bill of materials that you are using for these new items are already in quickbooks (items already created and you are using the same name- exact spelling). If quickbooks does not find the same BOM already defined in the system, it will create new items by the names which you have mentioned in the new file which you are working on).
    9. So only think you are changing is the item description – Text, Bill of Materials (BOM) of Inventory parts for the new assembly items you are tyring to create and the qty and prices for BOM.
    10. Once you have done so, you can save the excel file as others and save as Text (TXT) file.
    11. Remane the TXT file to IIF file (Just change the extension of the file_ Nothing else)
    12. Go qbooks- File—Utilities—Import and import the IIF file. Go to Item List and you will see your new Inventory Assembly Items in the list.

    It is complicated the first time you do it, but believe me you will get a hang of it pretty soon. It works so good when you are trying to create plenty of inventory assembly items with similar descriptions, similar BOM or as a matter of fact any inventory assembly items.

    I hope this is helpful to all the people who are wanting to import inventory assembly items from excel to quickbooks.

    Enjoy!!!!!

    • Charlie says:

      Nihar, lots of people will say that you can use the IIF import method to get assembies into QuickBooks, as you outline. I would like to point out that you should ALWAYS make a backup copy of your company file BEFORE importing from IIF, as that IIF process can possibly damage your data file (doesn’t happen always, but often enough to be a concern). In addition, you can’t “undo” an import easily, so you want to have that backup in case you have to roll things back if it didn’t work the way you expect.

      Also, if you are creating custom items, adding every new item as a new assembly can be problematical as you are filling up your item list with new items that you won’t reuse. There are some downsides to that.

  72. Steve P says:

    Hello,
    I am working with a company purchasing food items in need of a bar code system. It appears fishbowl is a good option. I am just organizing inventory on a spread sheet to set up an import to QB. Any advice?
    Steve

    • Charlie says:

      Steve, I don’t work with Fishbowl Inventory, so I can’t offer an opinion there. Look at it carefully – there is a lot of cost there and it moves your inventory entirely out of QB. There are good points to that and not so good points about that.

      I can’t say much about “bar code system” as that can mean thousands of different things. You can do simple things, you can do complex things, and different products provide you with different features.

  73. Steve Swartz says:

    Charley,
    Your website has been very helpful in setting up a new company file and cleaning up item masters. However i am trying to update costs and pricing and am having trouble with errors when i import from excel because there are too many decimal places in my cost and retail price cells. I have changed the decimal settings to 2 in excel but QB doesn’t seem to recongnize this. Part of the problem is that my new numbers in excel are derived from a formula tied to another sheet in the workbook. Are there any work arounds for this?

  74. Charlie says:

    How did you do the rounding, Steve? If you just use the “format” option in Excel, you are only changing the appearance of the numbers on screen – in the actual data of the spreadsheet it hasn’t been rounded. Several ways to deal with this. One is to copy the column that has the visually rounded values and use the “special paste” feature to paste just the VALUES into another column, then use that as the column in your import…

  75. Steve Swartz says:

    Okay that makes sense so i copied the cells and used the special paste chosing the “values and number format” option within the “speical paste” menu but the value in the “Fx” window still has 7 decimal places. I can’t seem to change that anywhere.

  76. Charlie says:

    Steve, I’m not an Excel expert and I don’t have something in front of me to mess around with right now – I think you just want to post “values”, not “values and format”. Or you can export the file to CSV (not sure if that will round things) then bring it back.

  77. Steve Swartz says:

    Thanks Charlie,
    I got it working.

  78. Whitney says:

    Hello Charlie –
    I am using QB’s POS v. 7.0 R6 Pro-level. I have been importing inventory using the Utilities/Import then using the Excel sheet provided and it uploads wonderfully. For years this has not been an issue, now, to make a long story short, when it imported the items it overrode several items of my past inventory, changed the quanties becasue of it and really messed up my inventory valuation, not to mention the deleted items.

    Can I undo an import? I have restored to the backup I made before the import, but the new inventory is still there, incorrctly, therefore the restore did not work.

    Please help! I had to shut down my store to fix this and can’t seem to.

    Thank you –
    Whitney

  79. Charlie says:

    Whitney – I don’t work with POS so I don’t have an answer. I’ll see if I can get a POS expert to look at your comment…

  80. Steve Swartz says:

    Hi Charley,
    With your help i was able to sucessfully get all my item files imported nicely into my new company file. One small issue i am having though after the fact is that none of the inventory parts have an average cost so when i do my profitablity report it does not bring any actual costs intot he report because i think it uses the average costs to generate these numbers. The average cost function won’t calculate until you purchase something. So my question is is there a way to get the average cost into the new file?

  81. Charlie says:

    Steve, if you have already imported the items, you can’t change that through this import. If you made a backup copy before the import, and can go back, you have an option. See the fifth bullet point under the “Problems…” heading.

    If you are ADDING a new item, you can map the “cost” and the “extended value” and this will update the average cost. You can’t do this if you are UPDATING an existing item.

    If you have 10 items and want an average cost of $20 per item, you would enter a quantity of 10 and a value of 200

  82. Nate says:

    I followed your procedure for advanced import in order to update my products with the new manufacturer cost. I imported it okay, but noticed that only the avg. cost was updated with the new cost. So, when I go to make a purchase order, the old cost shows up instead of the new cost.
    Any ideas?

    Thanks
    -Nate

  83. Charlie says:

    Nate, if you set up average cost, you imported the quantity and total value. For the “cost” field, you have to import a “cost” column separately.

    The good news is that this is a field that you can “update” with an import, unlike the average cost…

  84. Susan says:

    I have a 4 year old computer repair company. Over the past 4 years prices of Hard Drives have come down significantly. Currently my “Avg Cost” is greater than my actual cost which is a problem as it overstates COGS and therby understates Net profit. I have this issue for many items. How do I get around this?

  85. Charlie says:

    Susan, that is way off topic for this article. Also, I’m not a CPA, and you need to talk about this with your financial advisor. In a nutshell, you have inventory value in your asset account and you have been pulling that cost out of there as you sell items. To reduce that average cost, you have to pull out that value and send it to another account, probably an expense account. You can revalue your inventory, but that cost has to go somewhere and it will reduce your profit. It can be done, with a “value” inventory adjustment, but you need to get proper advise on this. ALSO, if you don’t have inventory left over from earlier years, the average cost should reflect your more current costs. The average cost should only be high if you have a number of those older, more expensive, hard drives left in stock…

  86. Don says:

    hello i have about 4,600 items and i need to up date the price and cost for these items every mounth the info comes to me in a excel spreed sheet some items taken off and some time new items add i tryed to inport the the price and cost but quick books told me these items all ready are in book book and would not add the twice if the item is all ready in buick books as a non invatory item how can i add the new price and cost to the item if already in quick books verison 10 enter prize

  87. Charlie says:

    Don, if you are updating using Excel and the Advanced Import method, you have to match both the ID and the “type”. So if you are importing as inventory part items, but they exist already as non-inventory items, you can’t do that. You have to import them as the same type as you have in your file already.

    If you have Enterprise 10, you can also use the Add/Edit Multiple List Entries feature and do a copy of the cost/price info from Excelto paste into the list in QuickBooks, as long as the items are in the same order.

  88. Don says:

    Charlie the problem is that some time there are items add and deleted and the excel spreed sheet does not line up with the exported iif file and there is 4,600 items so we dont want to up date them one at a time

    when i try to inport the new sheet and remove the old items and add all the info into excel quick book tell me the item number all ready exist in quick books and it ask for a new part nunber all i want to do is once a month is change our cost and the itme price for the item and keep the same part number for non inventiory items and just up date the cost and the item price we are using enterprise soulutions 10.0

  89. Charlie says:

    Don, if you are importing with IIF, that is a separate method than what is being discussed in this article. I almost never work with IIF, as it is antiquated and can potentially corrupt your database.

    If you are using the Excel import, there are two key fields. The item ID and the item type. If you do an import via Excel as outlined in this article, and the type and ID match, it will update. If the ID matches but the type doesn’t, you will get an error. If the ID doesn’t exist, the item will be added.

  90. Don says:

    is the item id the item part number and the item typ is Non-inventory Part

  91. Charlie says:

    Don, in the Advanced Excel Import you need to import “Name” and “Type” on the “QuickBooks” side of things. “Name” is going to be “Item Name/Number” in the Edit Item screen.

    If you have an item “ABC” in QuickBooks, and it has a type of “Non-inventory Part”, then your import file must have “ABC” as the name and “Non-inventory Part” as the type. They both have to match

  92. Don says:

    Thank i will let you know how it workout thank you for all your help!

  93. Don says:

    due you have the right mapping for non-inventory parts
    thank you Don it seems to working i just know need to up date the non inventory parts as well thank you for all the great info Don

  94. Don says:

    can i use the mapping in the artical for iventory items as well as non-inventory items i changed all the pricing for the inventory items and know i what to upload the no-inventory items pricing

  95. Charlie says:

    I don’t know what you mapped for your inventory parts, but for non-inventory parts I use the same as I use for inventory parts, if the non-inventory parts are “two sided” items. Just the name, type, cost and price

  96. Don says:

    Hello Charlie is there a way
    for non inventory parts to auto check mark the box from and inport of a excel sheet
    Check mark the box for ( This item used in assemblies or is purased for a specific customer job. )

  97. Charlie says:

    Don, review the “Problems/Restrictions/Notes” section at the end of my blog article for info on this…

  98. Mike K says:

    Hi, I hope someone can help me – I am trying to import a list of items into QB 2008, but the program freezes on my Vista PC every time I get as far as mapping fields. The Excel file is .xlx but I have tried CSV and 2003 file type, but same problem. Any ideas?

  99. Charlie says:

    Mike, does it freeze when you start to select a field in the mapping? If you look at the place side where the Excel fields would be listed, there is a drop-down arrow at the side. I’ve found that in some cases, if you only click that drop-down arrow to get the list, it won’t crash. If you click on the place where the field names show up, it crashes. That might not be your issue, though…

  100. Ryan Meray says:

    I’m attempting to do this procedure and I’m having the same problem as Mike. Using QB 2009, I go to advanced import, select the CSV/Excel file, select “Add new” mapping, and then anytime I try and select one of the mapping areas it freezes. I also don’t even see the drop-down box, the fields are just empty white.

  101. Charlie says:

    Ryan, this might or might not work. Some people have told me that it does. If you try this PLEASE let me know if it works.

    I had that problem awhile ago – it no longer happens so I can’t test this.

    Take a look at the screen shot at this link: http://screencast.com/t/Y2Q1NTZi

    When you have this blank screen, don’t click in the larger area where field names should be listed – which normally you can do – instead click on the VERY RIGHT side, where the dropdown list icon/arrow should be showing. Then you may actually get the dropdown list. If you select from the list and don’t actually click in the field itself, it shouldn’t freeze. At least that is how I got around it when I was seeing the problem awhile ago.

  102. Erin says:

    I just wanted to say a quick thank you, you just saved me hours of work!

  103. Jim says:

    is there anyway to bulk rename inventory assembly items?

  104. Charlie says:

    Jim, not really – since the “name” is the key, renaming them through an import would just add new items and leave the older ones still there.

  105. Jason Mercer says:

    I am trying to clean up a large item list full of old inventory entries. The user has a lot of old things in which there is “0” “on hand” that she’d like to get rid of.

    The list is too long to delete each of the unused items manually, so I’m trying to:
    1. export the current item list into excel
    2. remove the entries showing “0” “on hand”
    3. resave the new file in Excel
    4. import the file back into QB

    Whenever I get to the point of mapping the import, my QB freezes up (not responding). I’m working in QB Professional Retail 2009 / Windows 7 / Excel 2010. The computer is new and has plenty of horsepower, but something catching…

    Any thoughts?

  106. Charlie says:

    You can’t do a bulk delete that way. The import doesn’t remove items that aren’t in the import. The import isn’t a replacement of the item list, it is either an update (if there is a match) or an addition (if there is no match). Deleting can’t be done this way.

    And if you have any transactions using the items, you can’t delete them in the QB user interface either.

    The only thing you can do in a bulk approach like this is to change them from active to inactive, to hide them.

  107. Kyle says:

    Great Post, I just have one question. I have a copy of QB 2006 Pro and want to import items I sell in excel format. How many items can QB handle? I have over 250,000 products.

    • Charlie says:

      Kyle, there is a limit of 14,500 items in the item list in QuickBooks Pro and Premier. Enterprise had a limit of 29,000 back in 2006, but current versions technically have no limit. However, you may find that you run into speed issues when you get over 100,000 items in Enterprise, according to some people.

  108. Lori says:

    Good evening Charlie. I have built hundreds of QB files for clients and built and imported tens of thousands of assemblies using IIF files. In the latest file we’re building, they want to import custom fields. They are importing fine on the Inventory Items, however they are not importing at all on Assemblies. We have filled in the fields on one of the assemblies, exported the item list and followed the methodology on other items without success.

    Please advise as to whether we can import custom fields on assemblies, and if so, what might be causing the problem.

  109. Charlie says:

    Lori, I don’t work with IIF files. It doesn’t surprise me that there is an issue here, it fits a pattern with Intuit tools. Inventory Assemblies are often problematical.

    The best I an offer is to suggest that you look into the Transaction Pro Importer (http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/) – I know that it can import assemblies and BOM’s, I assume it can handle custom fields for the inventory assembly item itself. Haven’t tried that myself.

  110. Lori says:

    Thanks Charlie. I use Karl Irvin’s tools all of the time. Will be interesting to see if this is something else I should add to my quiver. 🙂

  111. Charlie says:

    Lori, that isn’t one of Karl’s tools – that is a tool from Baystate Consulting, which is another excellent software developer who I rely on (along with Karl…)

  112. Amy Grozier says:

    Very informative article! I’m researching how to do an export/import of a customer file. My new employer set up his own QB files and entered all of Customer Name field (Firstname Lastname) and now would like to clean it up without having to do it manually. Is this something you have experience with and can help me with? If not, is there somewhere/someone you can recommend? I’m fluent with Excel so that end of it is no problem, just need to be able to handle the import back in without bringing in duplicates and haven’t been successful in finding an answer from QB. We are running QB 2010 Pro.

  113. Charlie says:

    Amy, in the Excel import/export, the CUSTOMER NAME is the “key” field, if you export a record and then import it with changes, the key field has to match. So you are out of luck there. Internally, QuickBooks has a customer ID that is separate. If you use one of the more advanced import/export tools you should be able to make the change using that. The ODBC tool (www.qodbc.com) would let you do that, you can use ODBC with Excel. But that is a bit tricky. You can get the Transaction Pro Importer and Exporter tools to do this. The Exporter to export the customer list with the internal ID to Excel, the Importer tool to import back from Excel. I have written articles about those two tools that you can review. But that is more expensive. Or you can find a ProAdvisor who understands how to use the tools and who already has them, who can do it for a fee.

  114. Sophie says:

    hi Charlie,
    I am using QB 2007 pro.
    Today I tried follow your instruction to import some items. I always input items manually before.
    Everything was fine, but QB did not “added/updated” the qty. I got the error report,saying “On Hand: You can only set Quantity On Hand for new record. | Total Value: You can only set opening balance for new record. | Error Record: User selected to Skip”.
    I mapped “qty” with “on hand”, and “extended value” with “total value”.
    Thank you,

  115. Sophie says:

    hi Charlie,
    Another problem, when I tries to import the new items, the new items were imported fine but the qty was 0.
    thanks,

  116. Charlie says:

    Sophie, the error you got about on hand is saying that you tried to map the quantity, BUT you were importing items that already exist in the item list. You can only import quantity and value if you are adding NEW items. These matched something in the list already.

    For the second, without having hands on your file to see how you set up the mapping, it sounds like you didn’t have the quantity mapped correctly.

  117. Sophie says:

    hi Charlie,
    Thanks for the quick reply.
    In the article you posted, “You can set the initial on-hand balance for an item if you are adding the item. You need two columns, the quantity and the extended value. The extended value is the cost of that quantity of items – so if you are adding 10 items at $5.00 each, the extended value would be $50.00. Map the quantity to the On Hand field, and the extended value to the Total Value field. You cannot enter the average cost directly, you have to use the quantity and extended (total) values.”
    Does it mean that adding qty on the existing items are allowed?
    But in my case, it seems it was not allowed to do so. Is it because it is 2007 pro, and I should update to 2011?
    thanks, sophie

  118. Charlie says:

    Sophie, as it says, “if you are adding the item”. So for an item that isn’t on the item list, you can enter a quantity. Just above that I say “You cannot use this kind of import to adjust the on-hand balance of an item”. This means if the item already exists, you can’t use this to adjust or change the quantity. It is the same restriction that you have when manually adding or editing an item in the user interface – you can enter the quantity when adding the item, but you can’t change it when you edit an item (in that window).

    I just tested a copy of QB 2007, and it works the way I outline in this article. If you have NEW items, you can enter the quantity. If you want to UPDATE existing items, you can’t use this. You either have to figure out the IIF import method (complicated) for an inventory adjustment transaction, or use a tool like the Transaction Pro Importer (which I review in another article in the blog). Changing a quantity is an adjustment transaction, and that is a different thing entirely.

  119. Sophie says:

    thanks a lot, Charlie,

  120. Hi Charlie,
    Great instruction and thanks for the generous help!
    When importing an item that has a large description, it looks much better to use carriage returns to break lines and it give the details breathing room. For example:

    “Cambria Fabrication and Installation Includes templating of material. No sealing required.Color: (specify) Edge Style: Eased – unless otherwise noted.Sink/Cooktop Cutouts: (1)Note: estimate only, pricing confirmed following template.Includes mounting of undermount sink.Drop-ins, Farmhouse, and other sink styles are not handled by our installers. Additional cutting charges may apply depending on style of sink chosen.No plumbing work is included.”

    VS

    “Cambria Fabrication and Installation
    Includes templating of material.
    No sealing required.
    Edge Style: Eased – unless otherwise noted.
    Sink/Cooktop Cutouts: (1)
    Note: estimate only, pricing confirmed following template.
    Includes mounting of undermount sink.

    Drop-ins, Farmhouse, and other sink styles are not handled by our installers.
    Additional cutting charges may apply depending on style of sink chosen. No plumbing work is included.

    Note: Epoxy for an undermount sink must dry for 24 hrs before plumbing is reconnected. Otherwise epoxy may fail.”

    Is there a way to have this formatting carry over when importing over from excel?

  121. Charlie says:

    Angelo, I don’t think you can get that to work in the Advanced Excel import. You can do a paste operation in the Add/Edit Multiple List Entries feature, and it will work there. You may be able to do this with the Baystate Consulting import and export utilities (I’m pretty sure you can do it there).

  122. Sindi says:

    Hi Charlie,
    Ive exported my item list but have a problem importing it back. Im using stock part.My quantity on hand is a mess. When I try to import back it gives me an error message. I did everything that you wrote but still gives me the same problem.

    • Charlie says:

      Sindi, without knowing what the error message is, there isn’t a lot that I can recommend. Note, however, that you can’t use the Excel import to change the quantity on hand in the item list. You can only add a quantity when adding a new item. Changes are transactions, you have to do that in different ways.

  123. Sindi says:

    Charlie,
    It says my data contains duplicate list elements. If it can’t fix the the quantity on hand then how can I fix it? Im so desperate because I found the list messed up, now it’s not possible for me to use the Sales Order Fulfillment Worksheet and the Item List without opening the Adjustment Page first.

  124. Charlie says:

    Sindi, without hands on your system it is hard to give a specific answer. You may want to find a local ProAdvisor who can help you directly.

    You can enter adjustment transactions using a tool like the Transaction Pro Importer (http://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/) or you can use inventory adjustments directly in QB to get things into balance. But it sounds like you may have more going on than that…

  125. I own a filling station and I stock 3 products. Diesel, Unleaded and Premium.
    I have over 6 months of invoice data I would like to receive into inventory. I entered the first 5 deliveries using the ‘receive inventory with bill’ option and then started to look for a better way. I found your Import List tutorial expecting to learn how to import these inventory deliveries but it wasn’t covered.
    I have all the data in Excel and can provide just about any format that QB might accept.
    I also have 6 months of sales data to enter or import. I have two shifts/day and all products are lumped together for each shift so with 3 products and 2 shifts, I only have 6 sales transactions/day but it is also tedious to key and would appreciate any info on where to look.

    Thanks, George Barrowcliff

  126. Alan smith says:

    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

  127. Julia C. says:

    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!!!

  128. Charlie says:

    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…

  129. DaveWV says:

    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.

    • Charlie says:

      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.

  130. DaveWV says:

    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.

  131. humaira says:

    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

  132. John says:

    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

    • Charlie says:

      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.

      • John says:

        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.

        • Charlie says:

          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.

          • John says:

            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.

  133. Ruthann says:

    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!

    • Charlie says:

      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.

  134. Rachel says:

    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.

  135. JoAnn says:

    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.

  136. JoAnn says:

    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?

    • Charlie says:

      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.

    • Ryan says:

      JoAnn,

      Old thread but, FWIW, we have a tool – OpenSync – that will extract any and all data out into an easily manipulatable database (MySQL, Access, SQL Server) and subsequently plug changes back in. If Baystate’s – admittedly excellent – tool doesn’t solve that issue, you might want to give it a look (http://www.synergration.com/opensync)

  137. Reg D. says:

    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

    • Charlie says:

      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.

  138. Miru Borz says:

    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.

  139. kboyle says:

    Are these instructions specific to PC versions of Quickbooks or will they work with Quickbooks for Mac?

  140. Ravi Menneni says:

    I have a version 5.0 of Quickbooks POS.
    I want to import the inventory items, department, vendor and price and item on hand and tax information into QB.

    Is it possible?

    Can you please give me some clear navigation paths if it is possible?

    Thanks.

  141. Deena says:

    Your article points out things I wanted to know as I wasn’t sure about the effects the different options would have e.g. what happens if I select replace existing data with import data but ignore blanks. Or whether items in teh list would be duplicated when reimported.

    Thanks!

  142. laura says:

    Quick Question –

    I am using QB Pro 2010.

    I want to change all of my “Service” items to “Inventory Part” items.

    Can I do it using Export & Import using the instructions outlined here?

    • Charlie says:

      Short answer – “no”

      You can’t do anything through an export/import that you can’t do in the user interface, and as you probably have noted you cannot make that change in QuickBooks itself.

  143. Justus says:

    Charlie

    I am so grateful my client won’t have to retype all his inventory.

    Now I have problem with selling fixed assets, depreciation assets and bad debts. Please assist.

    Justus.

  144. Jusnet_Malindi_Kenya_+254720662325 says:

    Charlie

    my client is so impressed!
    I am impressed too!!

    • Ryan says:

      @Dana,

      As a QB developer, IDN member, and Mac user, I’ve been pushing for greater Mac functionality for years now. And at the very least I wish they’d open up the Mac API so I can handle it programmatically. No dice unfortunately.

      • Charlie says:

        Ryan, I doubt that they’ll ever do that in the same fashion as the SDK. We both know the technology used there and why it won’t work for the Mac, and also the thrust of Intuit is towards the Intuit Anywhere interface.

        I would be EXTREMELY surprised if they put the effort into the Mac. They view that as a product just for the smallest businesses. And, they have MANY more developers working on QuickBooks Online than any of the desktop products.

        Of course, I could be wrong, you never know what they are going to do.

  145. littledogie says:

    Thanks for the helpful page. I don’t think I can map custom fields during the import. At least, I don’t see the custom field names in the “Quickbooks” list, so I can’t assign the custom fields in the excel sheet to anything in Quickbooks. Am I missing something?

  146. Vanessa says:

    Helpful page. I understand about the mapping. I want to add inventory–new items. I’m confused on how Quickbooks handles the excel import once everything is udated. I’m assuming Inventory account goes up (Dr)–but I still owe so it would be a Cr to A/P but does QB do anything or do I have to adjust?

    • Charlie says:

      Vanessa, the value is posted to the inventory asset account that you specify on the import, and the matching posting goes to “Opening Balance Equity”. You would need to make adjustments to move the value out of that into the appropriate account. You can see this after the import, look in the inventory adjustment screen to see the posting that the import created.

  147. JOHN says:

    HOW DO I SORT OUT THIS ERROR

    thanks for your generous help..followed all the steps but got stuck on below.

    Account/Income Account: The specified Account does not exist in the list. | Expense/COGS Account: The specified Account does not exist in the list. | Asset Account: The specified Account does not exist in the list. | Error Record: User selected to Skip

  148. JOHN says:

    Found the solution for the above,Thanks…however am not able to import parent accounts with sub items.How do i go about this?

    thanks again

    • Charlie says:

      Accounts as in the Chart of Accounts? First you import the parent account itself – “ABC” for example (using a nonsense name). Then you import the sub account. If the sub account is “123” then you would import it as “ABC:123”

  149. AB says:

    Thanks for your response. I have a quick Question- I will be able to Populate the column “Quantity On Hand” with actual count I need to start while adding a whole range of new product?

    Thanks

    • Charlie says:

      You can import the quantity on hand only if you are adding a new item. You cannot update the quantity on hand if the item already exists on the list.

  150. Dena says:

    this tutorial does not seem to apply to importing an inventory list into the Mac version of 2012. might you be able to speak to that?

    i have an excel file that is an inventory list from y company and i would like to import that inventory list into my QB.

    Thanks so much for your help,
    Dena

    • Charlie says:

      Dena, I don’t work with the Mac, so I don’t have a recommendation for you. All of the articles in this blog pretty much relate to the Windows Desktop (US) version. Sorry!

  151. Robyn says:

    Hi Charlie:) this tutorial is by far the most complete I’ve found… and I’ve been looking ALOT. Thank you!
    Wondering if you can answer a few questions about importing in 2013 Premier edition and in general to set things up correctly.
    We have 10’s of thousands of items on updated price lists from our Vendors that we were considering importing. Ran into a few problems, and am now realizing that QB’s isn’t necessarily set up to handle the kind of imports I was considering… the first one I did was a few thousand items, and that was successful. The next was a list with over 12,000 items, and I learned that there is a limit which looks to be about 5,000… also as it was attempting to import, QB’s crashed several times first… making me realize just how much I was asking it to do! If we do what we were considering, there will be many more thousands of items added to the list. When importing, I’ve run into issues with Inventory/Non-Inventory drop box… is there any way to select YES for all without having to do each separately?… which isn’t an option! I was going to leave the items we don’t actually HAVE inventory of YET as NON-Inventory on the list, but then some of the information, such as Preferred Vendor is not an option, until the item is ‘turned on’ as Inventory. It’s screwing with the way they have the company set up currently, and I’m trying to get things organized and efficient, but running into many hurdles in QB’s.
    Got any ideas???
    Thank you so much for your help!
    Robyn

    • Charlie says:

      Robyn, I’m not sure if I follow exactly what you are saying, but I will point out that for large volume (and more complicated) imports I highly recommend the Transaction Pro Importer tool from Baystate Consulting. It solves a lot of problems on imports, and it can support more options than the Excel import. Of course, you have to pay for it, which is an issue for some people. Take a look at my latest review of this at http://www.sleeter.com/blog/2012/02/quickbooks-transaction-pro-importer/ (note, it imports lists as well as transactions). Highly recommended product!

      • Robyn says:

        Thank you very much! We will check out your suggestion for sure! Since my initial question to you, I’ve also talked with a QB ‘pro’ consultant to confirm a few of my thoughts on what I am dealing with here and attempting to do. Basically, the business I’m working for has garbage for inventory, vendor, and even customer… nothing is correct and was actually inherited from previous owner.
        I want to enter a new company and get things entered correctly so they can proceed and function properly. The only thing that concerns me when thinking of going this route is old invoices. Is there a way to pull the old invoices into the new company without screwing up the new inventory that we’ll start with?

        • Charlie says:

          You can move transactions from one file to another. You can use another Baystate product – the Transaction Pro Exporter, and then use the importer to import it. But if the items in the invoice are inventory parts, they affect the balance on hand. Any transaction will affect an account. You can also use the Data Transfer Utility by Karl Irvin (www.q2q.us) to move the transactions over. But, again, any transaction is going to affect some account.

          There are ways of doing this logically, it is best to work with someone who has experience with this.

  152. Denzil says:

    Hi
    Load invs into prior period as you would drs open item transactions via gen jrl.
    Your takeon trail bal eliminates the ‘ control’ acc on day 1

  153. RaeAnn says:

    I have several items in my inventory list that have 2 different part numbers. I know how to merge them in QB, but I have to be in single user mode, and my boss doesn’t want me kicking everyone else out of the system. If I export the list to Excel, make the item names the same, and then import to QB, will it merge the part numbers?

    • Charlie says:

      No, importing that way wouldn’t affect the item that you want to change. Note also that you generally can’t do anything in an import that you can’t do in the user interface, so if this DID work you would still have to be in single user mode.

      The merge only takes a few moments, so tell your boss that it is the only way to do it and you’ll be quick.

      HOWEVER, I do NOT usually recommend merging items if they are “inventory part” or “inventory assembly” items, because that will very likely make changes to your inventory asset and COGS calculations using those items – going back all the way to when you first started using QB. You can change financial statements in prior periods doing that. The better solution usually is to not merge those item types, just adjust them to a zero quantity on hand and then make them “inactive”.

  154. Jennifer says:

    I don’t know if you also have knowledge in this, but I need help! I started a new company and purchased POS to use in conjunction with Quickbooks Pro. Long story short, I hate it and think it will probably be better for me to do everything in QB. Can I export my inventory items from POS to QB? Will doing this affect my existing chart of accounts and inventory asset?

  155. Patrick says:

    Using QB Premier 2013. When I import tax rates for California through an CSV file, my preview shows no errors, but when I import I get errors on every line, and nothing is imported. I receive the following error on every line “Name: The version of QuickBooks you are running does not permit subitems.”

    I have no subitems. (I have not previously set up items for each of these cities, but that is why I am importing.)

    There are approximately 2,000 lines of information.

    What should I do?

    • Charlie says:

      Without seeing your CSV file and seeing how you map the import, I can’t say for sure. But I’ll guess that in the item name field you have a colon (:). The colon is used to create sub-item names, and that is what the program is complaining about. If you have something like “Yolo:Davis”, it is trying to create an item “Davis” as a sub item of “Yolo”, and it isn’t accepting that.

  156. Hi,
    I am trying to export my items to Quickbooks POS 2008. Some of the items I have are more than 32 characters which is the limit of an item’s name in Quickbooks POS. The reason is that those long items are subitems. How do I make the item’s names smaller? Can I export the name of the subitem without the name of the group item they belong to or is there any solution for that? Furthermore, my POS cash register is not connected to the computer with the Quickbooks program, how do I backup daily sales and transactions from the cash register to the computer to update my work? I have Quickbooks enterprise solution 2012. Thanks

  157. hassan says:

    Greetings, thank you for the info.
    Someone needs to help me export from pos to enterprise. Basically, i need to export the items, along with units of measure and available quantity etc etc.
    I also need to export work order and sales order.
    I see that the charles is not a pos guy, but someone needs to figure something out cause my mind no longer works trying to figure this thing out.
    please email any response to hassan@mawji.net
    this is kind of an urgent matter.
    Charlie, this one’s for you.

  158. Cbuck says:

    Hi Charlie,

    We are in the process of upgrading from Quickbooks online to Quickbooks Enterprise 2015 (platinum level). Our Intuit sales rep has warned us that “All Inventory and Service Items will be converted to Service items. You cannot change or edit the Item Type after import”.

    We have 1009 items that are classified as “Inventory Items” we need them to remain as inventory items after the conversion. We cannot have them change to service items.

    I was hoping we can export them to Excel and mass import them into Quickbooks Enterpise with the proper code so they remain as inventory items. Do you have any advice?

    • Charlie says:

      Well, the advice that I have you probably don’t want – I think that QuickBooks Online is NOT suited for any business that manages inventory.

      Conversion is a problem – you can’t convert just SOME of the file. The conversion moves everything over.

      You can check with Baystate Consulting (www.baystateconsulting.com) to see if their Transaction Pro Importer for QBO will let you import inventory items properly – but you will still have the existing items that were moved over as a part of the conversion…

      • Cbuck says:

        Hi Charlie – I have already checked with Baystate and they do not have a product that exports from QBO. We are a small business and QBO was a good fit for the first few years but we have outgrown it and are now moving to Enterprise. Thank you for the prompt reply it was greatly appreciated.

        • Charlie says:

          My apologies, I didn’t read your note carefully, I had in mind that you were going from Enterprise to QBO, so I had it backwards.

          You can export to Enterprise, then use Excel to export the items. Change the names of the existing items in Enterprise manually (“ABC” to “X-ABC” for example), change the “type” in the Excel list, then import back.

          If you have a LOT of items, that is tedious (renaming the existing items) and you double the size of your item list, at least initially.

          It depends on how much of the QBO information you really need to keep around. If you can summarize older information then you can do better list pruning.

  159. Sokhom says:

    Request template Excel for importing inventory

    • Charlie says:

      If you use the advanced method I talk about in this article, you just need to EXPORT your item list to Excel to see how it does things. But, you don’t have to do that, you can make your own Excel file with the columns needed, and then map it as I show.

  160. Steve Snelgrove says:

    Good Afternoon, Charlie.

    Thank you very much for this topic. It has helped immensely!

    I noticed that this article was written a while ago. Do you know if there has been any update to the ability of importing assembly item barcodes? We are currently running QB 16 Enterprise. After a LOT of wrangling, I have been able to put barcodes on Sales Orders and Purchase Orders.

    I appreciate your column and any advice you may have. Thank you.

Leave a Reply