Cloud Accounting QuickBooks

QuickBooks Online Inventory Import

Written by Charlie Russell

I’ve been talking about working with QuickBooks Online inventory, and in this article I want to talk about how you can import inventory items into the Products and Services list. There have been some changes to this lately.

QuickBooks Online Inventory

This article is part of my series on QuickBooks Online inventory:

Note that the ability to track inventory quantities is only found in QuickBooks Online Plus.

Note: Intuit updates QuickBooks Online just about every month, so operational details can change quickly. Look at the tag #QuickBooks Online Inventory to see the latest information about the inventory control features of this program.

Importing Item Records

QuickBooks Online has a list import feature that can be used to add records to the Products and Services list. This is a function that many businesses find useful, particularly when initially setting up a QuickBooks Online company.

Note that I have the Track quantity on hand feature enabled, as was discussed in the first article in this series.

To import item records, select Import Data in the “Gear” icon menu.

QuickBooks Online Inventory Import

This gives you the option to import customers, vendors, the chart of accounts and products and services. We’ll pick products and services. This will open a wizard that will take you through the process step-by-step.

The first step is to select a CSV or Excel file. “CSV” is a commonly used text format where the fields are separated by commas. I prefer to use Excel files rather than CSV because there is always some confusion about how to format a CSV file if you have things like an item description where you may have characters like quotes or commas. Don’t fool around with this – set your file up in Excel and you avoid a lot of hassles.

Import CSV or Excel

Here’s my sample Excel file. It is important to have unique names for each of the columns that you are working with. You don’t have to match the spelling of the column headings that I’m showing here, and you don’t have to include every column shown. As we’ll see later, the import function has a lot of flexibility in this regard. Note that I have a couple of missing fields in some records, to test how it works.

Excel import file

After you select your import file, QuickBooks Online tries to match up your column names with the ones that they want. The column on the left shows you what fields you can import into QuickBooks Online. The “Your Field” column has a dropdown field that will list the column headings from your import file.

Import mapping

If there is a match between the QuickBooks Online field and one of the column headings in your import file, a green check is placed by it. Each value has a dropdown list that includes the column headings from your import file. It is odd, but QuickBooks Online won’t make a match for all fields, even if my column names match perfectly. And it will make matches with some columns that aren’t spelled exactly. “SKU” and “Type” never match even though they are identical in my tests. “Sales Price/Rate” was always matched with “Price.” In any case, changing the mapping is very easy. Just select your import field from the dropdown list.

As you continue, QuickBooks Online will give you a preview look at the records in your import file, before the actual import takes place.

Import preview

Some interesting things to note here:

  • The Sell, Buy and Track check boxes control how this record will be recorded. The import always defaults to having all three checked, and that will make these items import as “Inventory” records (more on that later). You can control this by checking/unchecking the boxes for each individual item, or for all items by checking/unchecking the box at the top of the column.
  • Any field that has a box around the value is one that you can edit. Sales Price/Rate, for instance, can be changed just by entering a new value in that field on this screen. Some fields are dropdown lists where you can only select an option that is found in your QuickBooks Online file, such as Income Acct.
  • If you are tracking quantity for a record, you will always have a Qty As-of Date value set up for the record, even if you don’t import a quantity. This is very important, as has been discussed in earlier articles in this series. Remember that you cannot enter any transaction for this item that is dated before this date, after the import.
  • Dates can be a tricky thing to import. You must have them formatted in mm/dd/yy or mm/dd/yyyy format. I found that if I used some common alternate formats I would run into trouble – yyyy/mm/dd and dd/mm/yyyy won’t work, for example. This is, again, very important, because you cannot change the as-of date for an item after it is imported.
  • There is an Overwrite checkbox at the bottom. Use this with care, as there is no way to reverse out a bad import. You can cause severe headaches for yourself if you do an incorrect import and overwrite existing data in the item fields.
  • There are three options in the Type column: Inventory, Service and Noninventory. The “Noninventory” nomenclature was introduced to editing items in the October 2015 update, but it has been available in the import function since at least the August 2015 update, if not earlier. The distinction between Inventory and Noninventory is that both are Item records, with Inventory being one where the Track quantity on hand feature is enabled.

Once everything is set here, continue with the import. Here’s a QuickReport for one of the items I imported. You can see that the import created an Inventory Qty Adjustment for the imported quantity, just as if I had added this item through the add item window.

QuickReport for item import

The only column you are required to have is the Item or name column. Every record needs a name! The QuickBooks Online import function will fill in a suggested value for each of the columns that it requires. For example, here is the import preview for an import file that only had a “Name” column mapped. You can see that QuickBooks Online has filled in suggested values for multiple columns. You can change these on a line-by-line basis for each imported record in the import preview, if you wish.

Simple import

I mentioned it earlier, but it is important enough for me to highlight again: Pay particular attention to the QTY AS-OF DATE column. As you can see, the import function has entered a date in the preview even though I did not specify one in the import file. The date defaults to the current date. This is very important, because you will not be able to enter any transactions for this item that have a date prior to this as-of date. Rather than leaving the column blank, I highly recommend that you provide an appropriate as-of date that you know will be older than any transaction you will want to enter later.

Note also that there is no PURCHASE COST column in this import, since I didn’t map one out. This means that there won’t be any change to the inventory asset value due to this particular import. That is critical, because if you import a file that has no PURCHASE COST value, but does have a QTY value, the import function will post that quantity to the file with a zero value. As mentioned in earlier articles, QuickBooks Online doesn’t support a value only inventory adjustment, so it is vey difficult to correct this error later.

Prior to the import, if there is an incorrect value in one of the records, it will be highlighted in red.

Import preview with error

You have the option of correcting this before proceeding. Unfortunately, if you have a very long import list and just a few records have errors, there is no way to filter the list to show those that need attention, prior to attempting the import. That would be a really nice feature for Intuit to add.

If you continue with the import, those records that are valid will be imported, those that have errors will not be imported. Now you will see a list of those that did not get imported, and you can correct them at this time.

image

At this point I would like a way to print a report of these records so that I can deal with them later. You don’t have any way to easily list records that were not imported. You can generate a list of items that were created on a certain date, which is a way to see what was imported.

Note that if you try to import the list again, perhaps after making some corrections, the QuickBooks Online import function will highlight those items that are duplicate and won’t import them if you don’t check the “overwrite” box.

Cannot import duplicate items

The overwrite feature will not overwrite the quantity or as-of date values.

An important issue to point out – you cannot reverse an import. There isn’t a “grace period” where you can decide that the import didn’t work correctly and then back out the import (Zoho Books has this feature!). This is particularly important in cases where you might not have set the As-of date. If you don’t set it, the import defaults to today’s date. And once the record has been added with that date, you cannot add any transactions using this item that are dated before this date. This can be a problem if you import some records and then want to enter any outstanding purchase orders or bills for the items.

Note also that if this were QuickBooks desktop, the first step I would recommend would be to make a backup copy of your file. Then if you don’t like the import, you can restore the backup and start over. You cannot do this with QuickBooks Online, so make sure that you are very careful with your import values.

All in all, the QuickBooks Online import function works pretty smoothly. I would like to see some improvements, such as a report of items that didn’t get imported, the ability to filter the preview list for items with errors, and of course the ability to roll back an import.


Save pagePDF pageEmail pagePrint page

About the author

Charlie Russell

Charlie Russell has been involved with the small business software industry since the mid 70's, and remembers releasing his first commercial accounting software product when you had an 8-bit microcomputer with one 8 inch floppy disk drive. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor with additional certifications for QuickBooks Online and QuickBooks Enterprise, as well as being a Xero Certified Partner. Charlie started blogging about QuickBooks in 2008 (Practical QuickBooks) and has been writing for the Accountex Report (formerly the Sleeter Report) since 2011.

Visit his CCRSoftware web site for information about his QuickBooks add-on products. He is also the author of the California Wildflower Hikes blog.

5 Comments

  • Charlie,

    Awesome article!

    You can also import items directly from the Products and Services List page (“New” blue button, arrow down > Import).

    Inventory import mapping fields in QBO is pretty impressive. Xero does not have it yet. In Xero, you end up getting error messages only which is hard to figure it out later. But, XERO has few better inventory features in other areas than QBO.

    Just thought to mention few import limitations in QBO:
    1) No “Taxable” mapping field to import. You will need to manually edit after import. Time-consuming exercise.
    2) Sub-items cannot import.
    3) Limited to 1,000 items per import.
    4) New import “Types” not importing non-item and service. The default is inventory type now. Just may be the temporary glitch.

    As always, I enjoy reading your articles!

    • Thank you, Paul. I hate how they hide things in the dropdown buttons sometimes, you have to dig around.

      Types worked the last time I tried an import (when I wrote this article, which actually was around a month ago). I’ll have to go back and look at it again. It is a moving target, with monthly updates (or even more often sometimes).

  • No valuation adjustment, and no way to enter transactions on an item prior to Qty As Of Date. We’ll need another article just about how to get around these issues. These are major issues!

Leave a Comment