How to prepare for a bulk product import into QuickBooks Desktop?

Before you run a bulk product import into QuickBooks Desktop (QBD), there are a few things you need to set up first. Skipping any of these will cause the import to fail or leave your products in a messy state. Work through these in order.

» Step 1: Back up your QuickBooks file

Always take a backup before making any major changes to your QuickBooks data.

1. Click File → Back Up Company → Create Local Backup.
2. Choose Local Backup and click Next.
3. Save the backup file somewhere easy to find — not inside your QBD company folder.
4. Wait for it to complete and confirm the file is there before moving on.

» Step 2: Make sure the required accounts exist

QuickBooks Desktop needs three accounts to be set up before it can create inventory products. If any of these are missing, your import will fail.

1. Click Lists → Chart of Accounts.
2. Check that all three of these account types exist:
   
   

   Account	What it's for
   Sales (or Product Sales)	Where your product revenue is recorded
   Cost of Goods Sold	Where the cost of a product goes when it's sold
   Inventory Asset	Where the value of your stock sits on your books

    

3. If any are missing, click the Account button at the bottom-left → New, set the correct type, name it, and save.

» Step 3: Set up your product categories

If your products are organised into categories (for example, Apparel > Men's > T-Shirts), you need to create those categories in QBD before importing. This applies to QBD 2024 and newer — on older versions, skip this step.

1. Click Lists → Item Categories.
2. Click the Category dropdown at the bottom-left → New Category.
3. Add your top-level categories first (e.g., Apparel), then add the sub-categories under them using the Sub category of field.
4. Keep going until your full category structure is built out.

» Step 4: Create your custom fields

QuickBooks Desktop doesn't have built-in fields for things like Color, Size, or Fabric. If your products have these attributes, you need to create them as custom fields in QBD before the import, otherwise that data will be silently skipped during import and lost.

How many custom fields can you have?

• Pro / Premier: Up to 5 custom fields per product
• Enterprise: Up to 15 custom fields per product

If you are on Pro or Premier and have more than 5 attributes, you will either need to upgrade to Enterprise or combine some attributes into one field before importing.

Which attributes need a custom field?

Anything that doesn't map to a built-in QBD field needs its own custom field. Common examples include Color, Size, Dimension, Material, Fabric, Style Number, Collection, and Brand.

Fields like Sales Price, Description, and Category are already built into QBD. You don't need to create custom fields for those.

How to create custom fields:

1. Click Lists → Item List.
2. Double-click any existing product to open it. If your list is empty, click Item → New to open a blank form. You can cancel it once you are done.
3. Click the Custom Fields button on the right side of the window.
4. Click Define Fields.
5. For each attribute you need, type the label (e.g., Color) and tick the checkbox under Use for: Items.
6. Click OK twice to save.

Verify your custom fields are working:

1. Click Lists → Item List.
2. Double-click any product → Custom Fields.
3. Confirm every field you created appears with an input box next to it.
4. If a field is missing, go back and make sure the Use for: Items checkbox was ticked.

» Step 5: Prepare your import file

Rather than importing your product file as-is, the cleanest approach is to export a template directly from QBD and paste your data into it. This ensures the column names already match what QBD expects, which makes the import much smoother.

Export the template from QBD:

1. Click Lists → Item List.
2. Click the Excel button at the top of the window.
3. Choose Export all Items → Create new worksheet → in new workbook → click Export.
4. Save the file with a clear name like QBD_Import_Template.xlsx.

Add your custom field columns:

The exported template won't include your custom field columns automatically. Add them manually at the end of the template. Use the exact same spelling and capitalisation as the labels you created in Step 4.

Paste your product data into the template:

1. Open your product file alongside the template.
2. Filter your product file to only show active products — exclude any rows marked as inactive or flagged for deletion.
3. Copy and paste each column from your product file into the matching column in the template. Use Paste Values (not a regular paste) to avoid bringing over unwanted formatting.
4. After each paste, check a few random rows to make sure the data landed in the right column.

Fill in the required fields QBD needs:

Some fields aren't in your product file and need to be added manually. Add these as full columns and fill them on every row:

Clean and validate before saving:

• Make sure the Sales Price column is formatted as a Number — not text
• Check that every product name is unique
• On Pro/Premier, make sure no product name exceeds 31 characters
• Remove any blank descriptions
• Check that values are in the right columns (colors in the Color column, sizes in the Size column, etc.)
• Remove any helper columns you used for checking before saving

Save the final file:

Save it with a clear name and date. For example, QBD_Import_2026-05-29.xlsx. Note down how many rows are in the file. Cross-check this number against the import results later.