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:
- QuickBooks Online Inventory basics
- QuickBooks Online Inventory initial setup
- QuickBooks Online Inventory adjustments
- QuickBooks Online Inventory FIFO costing
- QuickBooks Online Inventory reports
- Importing inventory records to QuickBooks Online (this article)
- Converting QuickBooks desktop inventory to QuickBooks Online
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.