Bulk Product Import from Excel: Clean the File First

Why a bulk product import from Excel goes wrong

A bulk product import from Excel almost never fails because the platform is broken. It fails because Excel is a forgiving workspace and an importer is a strict parser. Excel will happily hold a barcode as a number, a curly quote, a trailing space, or a size spelled three different ways. The importer reads every one of those literally.

Short answer: save the sheet as a UTF-8 CSV, match the target platform's column headers exactly, clean the identifier and variant columns, and import a 5 to 10 row sample before the full file. The rest of this guide shows what that means in practice, using Shopify's product CSV import guide and WooCommerce's product CSV importer documentation as the reference for the rules.

Both built-in importers take CSV files, not .xlsx workbooks. So every Excel import is really an Excel-to-CSV conversion with a data-quality problem attached. Marketplaces and other store platforms each have their own templates; the cleanup principles carry over, the column names do not.

How to turn an Excel product file into a CSV that imports

1. Keep the Excel workbook as the master and generate the import CSV from it. Never hand-edit the CSV afterwards, or the two files drift apart.
2. Use File, Save As, CSV UTF-8 (Comma delimited). Plain CSV (Comma delimited) can use a legacy encoding, and both Shopify and WooCommerce require UTF-8. Google Sheets saves UTF-8 by default, which is why it is a safe intermediate step.
3. Check the delimiter. Excel follows the operating system's regional list separator, and where the decimal mark is a comma, the separator is often a semicolon. A semicolon-separated file looks like one giant column to an importer that expects commas.
4. Open the saved file in a plain-text editor, not Excel. Look at the header row, the delimiter, and a few rows that contain commas or quotes.

Most corruption comes from a short list of Excel habits:

The product data cleanup checklist before you upload

Cleanup is the unglamorous 80 percent of a bulk import job, and it is where the platform rules above turn into concrete checks on your columns.

Two Excel formulas cover most of the mechanical work. The first strips non-printing characters, non-breaking spaces and double spaces; the second flags duplicate SKUs in column A (TRUE means the SKU appears more than once):

=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160)," ")))
=COUNTIF($A:$A,A2)>1

Image URLs deserve their own pass. Before importing, request every URL and confirm it returns an image. A single dead link is cheap to fix in the sheet and expensive to fix across hundreds of imported products.

Importing products to Shopify from an Excel-built CSV

Shopify's importer is built around the Handle column. Rows that share a handle belong to one product, which is how variants and extra images are expressed. Header names are case-sensitive: Handle works, handle does not. This excerpt shows the shape; download Shopify's current sample CSV and keep its full header row, because templates change over time.

Handle,Title,Option1 Name,Option1 Value,Variant SKU,Variant Price,Image Src
ceramic-mug,Ceramic Mug,Size,Small,MUG-S,12.00,https://example.com/mug.jpg
ceramic-mug,,,Large,MUG-L,15.00,

The behaviors that cause the most damage are the ones Shopify documents but people skip:

• Overwrite is column-wide. With Overwrite products with matching handles selected, every column present in the file replaces the store value. Without it, products that match an existing handle are ignored.
• Blank means blank. If a column is in the file but a cell is empty, the existing value is overwritten with nothing. A blank Vendor cell erases the vendor.
• Missing columns are safe. A column that is not in the file leaves the store value untouched.
• Variant IDs are fragile. Changing an Option value deletes the existing variant ID and creates a new one, which can break apps and integrations that stored the old ID.
• No undo. A CSV import cannot be canceled once it starts and there is no import history; changes show up in the store activity log. Export a backup first.
• 15 MB per file. If the upload errors or times out, split the CSV and upload each part.

When an import fails, the error text usually points at the file, not the store:

Importing products to WooCommerce: SKU is the key

WooCommerce does not use a handle. It matches rows to existing products by ID or SKU, and it cannot assign a chosen ID to a new product: new products always get the next available ID. If your source system's IDs matter, carry them in a custom column and treat the SKU as the cross-system key.

A variable product is one parent row plus one row per variation, with the parent above its children:

Type,SKU,Name,Parent,Attribute 1 name,Attribute 1 value(s),Attribute 1 visible,Attribute 1 global
variable,mug,Ceramic Mug,,Size,"Small, Large",1,0
variation,mug-s,Ceramic Mug - Small,mug,Size,Small,1,0
variation,mug-l,Ceramic Mug - Large,mug,Size,Large,1,0

The update flag explains the most confusing WooCommerce result, an import that finishes and seems to do nothing. With Update existing products selected, rows that match an ID or SKU are updated and rows that do not match are skipped. With it unselected, rows whose ID or SKU already exists are skipped. Re-running a full file against a store that already has those SKUs therefore changes nothing unless you pick the right mode. The importer's skipped-row log is the first place to look.

How to import variants without breaking them

Variants are where the one-row-per-product mental model of a spreadsheet stops working, and where cleanup pays off the most.

• Shopify: every variant row repeats the product's handle, and option names must stay identical. To rename an option or move it to another position, import once with a temporary option name (Shopify's own example adds a period, Size.), then import again with the final name. The importer checks for duplicate option names before it writes new values, which is why a direct swap fails.
• WooCommerce: attribute names and value spelling must be identical in the parent and variation rows. From WooCommerce 11.1, a single update import can create new variations when the parent row comes before them and each variation has a unique SKU. On earlier versions, or if you cannot guarantee row order, run two imports: parent first, variations second.
• Both: check that no two variants of one product share the same option combination. A COUNTIFS on the product key plus each option column finds the clashes in seconds.

How to update existing products in bulk without wiping data

1. Export the current catalog first and edit that export, so identifiers and headers already match the store. It doubles as your backup.
2. Delete every column you are not deliberately changing. On Shopify, an absent column is safe and a blank one is destructive.
3. Remember what CSV cannot do on Shopify: it cannot bulk-delete products, and it cannot bulk-change availability on other sales channels.
4. Test the update on one or two products. Open them and confirm the fields you changed, and the ones you did not.

Large catalogs and supplier files: split, test, then import

Shopify caps a product CSV at 15 MB. WooCommerce has no fixed number: the limit is whatever your server's upload size, memory and processing time allow, and the upload screen shows the maximum your host set. In both cases the advice is the same: split the file into batches so a failure is small and findable, and back up before you start. Shopify's guidance for partners running large imports is to test a small subset on a development store; on WooCommerce the equivalent is a staging site.

A useful test batch is one parent product with several variations, a product whose category name contains a comma, a title with accented characters, and a product with several image URLs. After the test import, open each product and check variants, attributes, prices, stock and images before running the rest. Then reconcile at the end: rows in the file against products and variants in the store.

When the source is a supplier file with thousands of rows, different column names per supplier and inconsistent option values, the cleanup becomes the project and the upload is the smallest part of it. That is usually the point where handing the file to a product data import and migration service saves more time than another round of failed uploads.

What a product data cleanup service should cover

If you are weighing whether to do this yourself, here is what a proper cleanup and import job includes, in order:

1. Audit the source file: row counts, duplicate SKUs, missing required fields, encoding problems, dead image URLs.
2. Normalize the data: trimmed whitespace, one spelling per option value, consistent categories, prices as plain numbers, identifiers kept as text.
3. Map to the target schema: Shopify handle and option columns, WooCommerce Type, Parent and attribute columns, or the template of whichever platform or marketplace you are moving to.
4. Run a test batch on a development store or staging site and inspect variants, images and stock.
5. Import in batches under the platform's size limits, after taking a backup.
6. Reconcile and report: counts in against counts out, plus a change log listing every value that was altered, so nothing is changed silently.

What to send: the source file or files, the target platform, and the current store export if you are updating existing products. Cost is priced to scope, driven mainly by row count, the number of source files and how messy they are.

A few hundred clean rows are a job you can do yourself with the checklist above. A service earns its cost on large, messy or recurring supplier data. If that sounds like your file, send us the file and the target platform for a free quote.