Here’s how to import products and services from an Excel file into QuickBooks Online.
IMPORTANT: When importing Products and Services from Excel, an error will occur if the list exceeds 1000 records. You may get the message Something's Not Quite Right - Cannot Exceed 1,000 records. Lists larger than 1000 records can be split into multiple lists and imported individually using the steps below.
- Select the Gear icon at the top, then Import Data.
- From the list, select Products and Services.
- In the Import Products and Services page, select the Browse button.
OPTIONAL: Select the Download a sample file link if you need to see how headers must appear in your spreadsheet
- Find and select the Excel file then click Open.
- Select Next to continue.
- Map your data.
- The QuickBooks Online fields and your fields will be shown. Select the drop-down lists to change how your information is mapped.
- You can also check the box under I sell this or I buy this but be aware that inventory won't be imported.
- If you’re missing critical data, we’ll alert you with a warning icon.
- Select Next to continue.
- Adjust your data if necessary then select Import.
NOTE: If we perceive an error, such as a duplicate item already in QuickBooks Online, or an invalid field, we'll give you an opportunity to correct the data and re-import the modified records; you can also choose to ignore the records.
- You cannot undo this import.
- The Product/Service Name field can only have up to 100 characters and does not allow tabs and some other special characters.
- The Quantity as-of Date field does NOT allow date format that starts with the year (Example: 2018/06/30 or 2018-06-30).
- You'll have to select a parent income and expense account for each line item (sub-accounts can't be used). Make sure you select the proper accounts here or it will affect your reporting.
- Importing a Products and Services list is normally additive. In other words, it amends the existing list by adding the new items, not erasing the ones that are already there. So if you've already got a list with Boots, Sales and Service on it, and you import a list from Excel with Shoes, the new Products and Services list will have all four items: Boots, Sales, Service and Shoes.
- If you are importing categories, make sure the Product/Service Name column in the import file follows this format - Category:Name (Flowers:Rose; Flowers:Daisy; Flowers:Carnarion). When the import completes, QuickBooks will create a category named Flowers and items called Rose, Daisy and Carnation.
- If you'd like to replace your existing list with your excel import, check the box that says Overwrite existing records in QuickBooks Online with the same Product/Service name.
- If you're seeing the product/service reports populate but the lists are empty, download the QuickBooks Online sample file (see Step 3) and don't change the headers at all. The headers are as follows:
- Product/Service Name
- Sales Description
- Sales Price / Rate
- Income Account
- Purchase Description
- Purchase Cost
- Expense Account
- Quantity on Hand
- Inventory Asset Account
- Quantity as-of Date