IMPORTANT:

First, back up your QB Company File.

How to Export all Items from QB into Excel

  1. Go to the Item List
  2. In the bottom toolbar row click on Excel -> Export All Items
  3. Follow the prompt to create a new Excel doc.

How to Import an Excel File into QB and Batch Update Items

These instructions assume you have exported your Items to Excel by following the above instructions.

Important - Part 1

Back up your QB Company File before proceeding.

Important - Part 2

In the steps below you are required to map the Excel columns to your QB Item data fields. NOT all columns need to be mapped. The Item (aka the Name) column is required to be mapped - this is how QB finds the record to update. Additionally, the Type column needs to be mapped.

The only additional columns (fields) that need to be mapped are those fields that need to be updated in QB - meaning the Excel data changed. In other words, if the Income Account or the Cost column data never changed then there is no need to map those columns as no data needs to be updated.

In most cases you will only be updating a couple of columns meaning you only need to map those columns.

 

Pro-Tip

Copy the Excel file you're about to import and remove all but 1-3 rows. Then follow the steps below to import the file and validate you did it correctly (perform the import; then manually check the Items). When you feel comfortable about the process go back and do the full import.

 

Steps

  1. Open the Item List.
  2. In the bottom toolbar choose Excel -> Import Items ...
  3. In the pop-up, on the right, click on the button for Advanced Import
  4. Click on the Browse button and choose your Excel document.
  5. In the Select a Sheet dropdown choose Sheet1. QB has automatically created a worksheet called Quickbooks Export Tips, so you want the first valid sheet which contains the data.
  6. Click on the checkbox This data has header rows
  7. In the Choose a mapping dropdown, choose Add New

  8. You are now in the mapping editor.

    • Enter Excel Items as the mapping name
    • Choose Item from the Import Type dropdown
    • You should have two columns: Quickbooks and Import Data. You need to map the fields from the QB Item to the Excel column of the same. This import flow requires that at a minimum you select Type and Name (also called Item) which is the unique identifier for the Item and QB uses to find the Item to update.

      Map: Type -> Type

      Map: Name -> Item

        Map each Custom field column by pulling the dropdown on the right-hand side until its name matches the correct value from the left-hand side.

  9. Click on Save to save your mappings.Click on the Preview button.
  10. In the Data Preview section ensure that the first couple of rows have an OK value in the first column and are in green. If there any rows with ERROR then STOP - and investigate the error.
  11. If there are no errors, in the Error Handling section choose Do not import rows with errors.
  12. Click on the Import button.
  13. If you see a Duplicate Record Found pop-up, choose the 2nd option: Replace existing data with import data, ignoring blank fields. Then click on Apply to All
  14. After the import completes you should see a message like Import is finished. X records have been imported and Y records had errors/warnings. If there any error records than click on the Save button and QB will prompt you to save a CSV file containing further information on the errors. If there were no errors then just click on Don't Save