How to Import Items From Excel Into QuickBooks Desktop

Before you start

• Back up the company file without overwriting earlier backups. Intuit says an import can't be undone.
• Sign in as Admin and switch to single-user mode with File > Switch to Single-user Mode.
• Turn on inventory: Edit > Preferences > Items & Inventory > Company Preferences, then Inventory and Purchase Orders are Active.
• Set up the income, cost of goods sold and inventory asset accounts first. Account names in your sheet must match the Chart of Accounts exactly, capitalization included.
• With Advanced Inventory and multiple sites on, Intuit says to turn multiple sites off before importing quantities or values.
• Mind the limits. Item names can be up to 31 characters, and Pro and Premier hold up to 14,500 items, active and inactive combined.

Before you turn sites off, read How to track inventory by site.

Pick a method

Step 1: Build the spreadsheet

• Item Name: the name for your Item List. For a sub-item, write the parent, a colon and the sub-item, such as Widgets:Widget001. The parent must already exist or sit above it in the sheet.
• Item Type: spelled exactly as QuickBooks shows it, such as Inventory Part or Service.
• Description: appears on the Item List, sales orders, sales receipts and invoices.
• Income Account, Inventory Asset Account and COGS Account: names exactly as in your Chart of Accounts.
• On Hand Quantity, Total Value and As Of Date: for new inventory items only.
• Cost and Sales Price.
• Is Passed Thru: enter Y so QuickBooks tracks both cost and price.
• Optional: Manufacturer's Part Number or SKU, and Reorder Point.

Step 2: Import with Advanced Import

1. 1Go to File > Utilities > Import > Excel Files.
2. 2When QuickBooks offers Add/Edit Multiple List Entries, select No.
3. 3Select Advanced Import.
4. 4Select Browse, choose your file, then pick the sheet that holds your items.
5. 5Select This data file has header rows.
6. 6In Choose a mapping, select Add New. Name the mapping and set the Import type for items.
7. 7Match each QuickBooks field to a column in your file. Leave a field blank if your file has nothing for it, then select Save.
8. 8Select Preview to review the mapping, then Import.

Step 3: Handle duplicates and errors

If an item already exists, QuickBooks shows Duplicate Record Found with three choices. Pick one, then select Apply or Apply to all.

• Keep the existing data and discard the import data.
• Replace the existing data with import data, ignoring blank fields.
• Replace the existing data with import data, including blank fields. Empty cells in your file then replace what's in QuickBooks.

QuickBooks then tells you how many rows went in and how many failed. Save the error log, fix the rows it names and import again. Any duplicates that slip through can be merged later. See How to merge customers, vendors and items.

Step 4: Add or edit items on one screen

1. 1Go to Lists > Add/Edit Multiple List Entries.
2. 2In List, choose the item list you want to work with.
3. 3Select Customize Columns to show the columns you need. Select Default to go back to the preset columns.
4. 4Copy rows in Excel with Ctrl+C and paste them into the window with Ctrl+V, or type into the fields.
5. 5Highlight a field and select Copy Down to repeat it, or Duplicate Row to copy a row.
6. 6In Enterprise, Intuit notes that Copy Down overwrites data already in those fields, and that a duplicated row starts with DUP.
7. 7Select Save Changes. QuickBooks tells you how many records it saved and flags any errors.
8. 8Hover over a flagged field to see the error, fix it and save again. Rows with errors aren't saved.

This window is for list information such as names and descriptions, not transactions. In Enterprise it also has a category column. See How to use item categories in QuickBooks Desktop Enterprise.

What an import can't do

• Change quantity on hand, value or average cost on items that already exist.
• Bring in Subtotal, Payment or sales tax items through Advanced Import. Those need an IIF file.
• Import quantities or values while multiple inventory sites are on.
• Enter transactions from Add/Edit Multiple List Entries. It handles list data only.
• Be undone. Restore your backup if an import goes wrong.

For a new inventory item, QuickBooks records the imported quantity and value as an Adjust Quantity/Value on Hand. It debits the item's asset account and credits Opening Balance Equity. For changes after that, see How to adjust inventory quantity and value.

Using CSV or IIF files instead

• CSV: to import lists from CSV, first install Intuit's QuickBooks Import Excel and CSV toolkit. It has sample files and a guide to the fields you can import.
• To start from your current list, go to Lists > Item List > Excel > Export all Items. Choose a new worksheet, or a comma separated values (.csv) file.
• IIF: go to File > Utilities > Import > IIF Files, select Import IIF and choose the file. Intuit doesn't offer technical support for IIF files.
• In QuickBooks 2019 and later, the IIF tool scans the file first. Failed rows go into a new IIF file with the errors marked, which you fix and import again.
• Intuit doesn't recommend the option Import it for me. I'll fix it later, because it skips the data review.
• IIF limits: an assembly item's custom field data can't be imported, and price levels set per item can't be imported or exported.
• Excel won't keep leading zeros in numbers when you build an IIF file. Format those columns as Custom first.
• For bulk edits, export with File > Utilities > Export > Lists to IIF Files, edit the file in Excel and import it back.

Where QuickBooks Desktop leaves the work to you

• Supplier spreadsheets rarely match QuickBooks columns. Someone has to rename, split and clean them before mapping.
• Account names, item types and parent items have to match exactly, or rows fail.
• Near-duplicates from different suppliers have to be found by a person, before or after the import.
• Every failed row in the error log needs someone to fix it and import again.
• Opening quantities go in once, on new items. Every correction after that is an adjustment.

How Logistify helps

Logistify doesn't import items into your company file. Product Catalog Cleanup turns supplier spreadsheets into clean SKUs, and your team still maps and imports the result. Once the items exist, the Sales Order Service enters customer orders for them into QuickBooks Desktop as sales orders, with a person reviewing exceptions.