How to Clean Product Catalog CSVs for Shopify/WooCommerce Imports

You've spent weeks preparing your 5,000-product catalog for migration to Shopify.

You click "Import CSV." Progress bar fills. Then: "Illegal quoting on line 847."

Back to the file. Hours debugging. Find the error. Re-upload. "Validation failed: Options are not unique."

Another error. Different line. Same frustration.

TL;DR: Product CSV imports fail when files contain formatting errors that platforms reject—wrong encoding (Windows-1252 instead of UTF-8), missing required columns (Handle/Title for Shopify, SKU/ID for WooCommerce), duplicate identifiers, unescaped commas/quotes in descriptions, invalid variant relationships, or broken image URLs. Clean systematically before uploading using browser-based tools that process 5,000+ products in 10-15 minutes—without exposing pricing strategy or inventory levels to third-party servers. Validate encoding → check required columns → fix formatting → validate SKUs/Handles → clean variants → test with 10 products → import full catalog.

---

This guide shows how to clean product catalog CSVs correctly before uploading, using privacy-first tools that process data client-side without uploading sensitive pricing or inventory to third-party servers.

Who this guide is for: Ecommerce managers, store owners, and Shopify/WooCommerce admins migrating product catalogs or performing bulk updates.

---

Table of Contents

• Why Product CSV Imports Fail
• Shopify vs WooCommerce Requirements
• Step 1: Check File Encoding
• Step 2: Validate Required Columns
• Step 3: Clean Formatting Issues
• Step 4: Fix SKU and Handle Problems
• Step 5: Handle Product Variants
• Step 6: Validate Image URLs
• Step 7: Check File Size Limits
• Complete Pre-Import Checklist
• FAQ

---

Why Product CSV Imports Fail

Product CSV imports fail for predictable reasons—understanding these patterns prevents hours of debugging.

Most Common Import Errors

Encoding issues (45% of failures): CSV edited in Excel on Windows defaults to Windows-1252 encoding instead of UTF-8. Special characters—accented letters, currency symbols, smart quotes—display as � or corrupt after import.

Missing required fields (30% of failures): Shopify requires Handle and Title. WooCommerce requires SKU or ID. Blank cells in required columns cause silent failures where products get skipped without clear error messages.

Duplicate values (15% of failures): Two products share the same Handle (Shopify) or SKU (WooCommerce). The second product overwrites the first during import, causing inventory discrepancies.

Malformed data (10% of failures): Product descriptions contain unescaped commas. Platform interprets comma as column separator, shifting all data to wrong fields. Prices display in descriptions. SKUs appear as product names. For cleaning HTML and formatting from product descriptions, see our guide to removing HTML tags from CSV files.

Platform-Specific Gotchas

Shopify:

• 15 MB file size limit (~50,000 simple products)
• Case-sensitive column headers: Handle works, handle fails
• Handles must be URL-safe (no spaces, special characters)
• Image URLs must start with http:// or https://

WooCommerce:

• UTF-8 encoding required (no exceptions)
• Boolean values must be 1 or 0 (not true/false)
• Category hierarchy uses > separator: Clothing > Shirts > T-Shirts
• Stock quantities must be numeric (no units like "100 pcs")

---

Shopify vs WooCommerce Requirements

Both platforms use CSV imports but enforce different rules—preparing files for one doesn't guarantee success with the other.

Key Differences

Shopify Essentials

Required columns: Handle (unique identifier, URL-safe), Title (product name)

For variants: Option1 Name and Option1 Value (e.g., "Size" / "Medium"), up to 3 option levels maximum

Rules: Maximum 3 variant options per product, column headers case-sensitive, Handles unique across entire catalog

WooCommerce Essentials

Required columns: ID OR SKU (at least one), Type (simple, variable, grouped, external), Name (product title)

For variants: Parent with Type: variable, children with Type: variation and Parent field linking to parent SKU/ID

Rules: Boolean values must be 1/0, multiple values comma-separated and quoted, categories use hierarchy notation

---

Step 1: Check File Encoding

Wrong encoding is the #1 reason clean-looking CSVs fail after import—both platforms require UTF-8 encoding.

What is CSV Encoding?

Encoding determines how characters are stored as bytes. Your CSV might display correctly in Excel but contain wrong byte representation—causing import failures or corrupted text.

UTF-8: Universal encoding supporting all languages and special characters. Required by Shopify and WooCommerce.

Windows-1252 (ANSI): Excel's default on Windows. Works for basic ASCII but corrupts international characters, currency symbols, smart quotes.

How to Detect Encoding Issues

Visual indicators:

• Product names: Café Latte instead of Café Latte
• Currency: £19.99 instead of £19.99
• Quotes: “Specialâ€\x9d instead of "Special"

How to Fix Encoding

Google Sheets (Recommended): Always saves in UTF-8 automatically. Open CSV → File → Download → Comma Separated Values.

Excel: File → Save As → Select "CSV UTF-8 (Comma delimited)" (NOT regular CSV—saves as Windows-1252).

Use Data Cleaner to validate encoding without uploading data—client-side tool detects encoding automatically, shows character type, identifies problematic lines.

---

Step 2: Validate Required Columns

Missing or misnamed required columns cause immediate import failures—both platforms are unforgiving.

Shopify Required Columns

For new products: Handle (must be present on first row), Title (product name)

For variants: Handle, Title, Option1 Name and Option1 Value

Common mistakes: Using handle instead of Handle (case matters), using Product Name instead of Title, leaving Handle blank, using spaces/special characters in Handles

WooCommerce Required Columns

Minimum: ID OR SKU (at least one), Type (simple, variable, grouped, external), Name (product title)

For variants: Parent needs Type: variable, children need Type: variation and Parent field

Common mistakes: Missing Type column entirely, using true/false instead of 1/0, leaving Stock blank when stock management enabled, not setting Parent for variant children

Fixing Missing Columns

Add missing columns: Open CSV in spreadsheet editor, insert column with exact required name, fill with appropriate values.

Example Handle generation from Title:

• Title: Men's Blue T-Shirt (Large)
• Handle: mens-blue-t-shirt-large (lowercase, spaces → hyphens, remove special chars)

Column order doesn't matter—as long as headers are correct, columns can be in any sequence.

---

Step 3: Clean Formatting Issues

Formatting issues are subtle errors that break CSV structure—hard to spot visually but cause catastrophic import failures.

The Comma Problem

CSVs use commas to separate columns. If product data contains commas, CSV structure breaks.

Wrong:

Title,Description,Price
Blue Shirt,Made from cotton, polyester blend,29.99

Parser sees 5 columns instead of 3—description splits across multiple fields.

Right (wrap in quotes):

Title,Description,Price
Blue Shirt,"Made from cotton, polyester blend",29.99

Quote Escaping

If description contains quotes, escape them properly.

Wrong: "The "Best" Coffee Mug" (breaks parsing)

Right: "The ""Best"" Coffee Mug" (double the quotes) or use single quotes: 'The "Best" Coffee Mug'

Smart Quotes vs Straight Quotes

Microsoft Word converts straight quotes (") to curly quotes (" "). Smart quotes break CSV parsing.

Find and replace:

• Search: " and " (curly quotes) → Replace: " (straight quote)
• Search: ' and ' (curly apostrophes) → Replace: ' (straight apostrophe)

Trailing Spaces & Empty Rows

Invisible spaces at end of values cause comparison failures. Blank rows in middle of CSV cause import to stop early.

Use Data Cleaner to automatically remove leading/trailing spaces, extra spaces between words, non-printable characters, and empty rows.

---

Step 4: Fix SKU and Handle Problems

SKUs (WooCommerce) and Handles (Shopify) are unique identifiers—duplicates, blanks, or invalid formats cause import errors.

Shopify Handles

What Handles do: Create product URL (yourstore.com/products/{handle}), group variants under one listing, serve as unique identifier for updates.

Rules: Must be unique across catalog, only lowercase letters/numbers/hyphens, no spaces/special characters, changing Handle changes URL (bad for SEO).

Common problems:

Duplicate Handles: Second product overwrites first during import. Find duplicates using =COUNTIF($A$2:$A$5000, A2) > 1 formula or use Remove Duplicates tool. Fix by appending variant detail: blue-shirt-medium instead of blue-shirt.

Invalid characters: Men's Shirt (Large) contains apostrophe, spaces, parentheses. Auto-fix: Convert to lowercase, replace spaces with hyphens, remove special characters.

WooCommerce SKUs

What SKUs do: Identify unique products for inventory tracking, link parent products to variants, can be alphanumeric.

Rules: Must be unique across catalog, can contain letters/numbers/hyphens/underscores, cannot be blank if no ID column exists.

Common problems:

Blank SKUs: If CSV has ID column, blank SKUs OK. Without IDs, every product needs SKU. Auto-generate: Use vendor prefix + number (VENDOR-0001), product type + attributes (SHIRT-BLUE-M), or barcode if available.

Duplicate SKUs: Cause inventory conflicts. Append distinguishing data: ABC123-V1, ABC123-V2 for variants.

---

Step 5: Handle Product Variants

Variants are the most complex part of product CSV imports—Shopify and WooCommerce handle them differently.

Shopify Variant Structure

How it works: One product can have up to 3 variant options (Size, Color, Material). Each combination creates unique variant. All variants share same Handle.

Example: T-shirt with Size and Color

Handle,Title,Option1 Name,Option1 Value,Option2 Name,Option2 Value,Variant SKU,Variant Price
blue-shirt,Blue T-Shirt,Size,Small,Color,Blue,SHIRT-S-BLUE,19.99
blue-shirt,,Size,Medium,Color,Blue,SHIRT-M-BLUE,19.99
blue-shirt,,Size,Small,Color,Red,SHIRT-S-RED,19.99

Key points: Same Handle groups all as one product, first row has Title (subsequent rows can leave blank), every variant row needs Option1 Name/Value filled, each variant gets unique SKU.

Common mistakes: Missing Option values, non-unique options (two variants with identical Size + Color), missing variant SKUs, changing Option names mid-product.

WooCommerce Variant Structure

How it works: Parent product with Type: variable, child products with Type: variation and Parent field linking to parent SKU/ID.

Example structure:

• Parent: Type = variable, defines available attributes
• Children: Type = variation, Parent = parent SKU, specific attribute values

Common mistakes: Missing Parent, wrong Type (parent set as simple), attribute mismatch (parent defines "Size", child uses "Sizes"), missing variation ID/SKU.

Cleaning Variant Data

Duplicate variant combinations: Use Remove Duplicates on Handle + Option1 Value + Option2 Value columns.

Missing option values: Filter rows where Handle matches product, verify every row has Option1 Name and Value filled.

Inconsistent option names: Use Find & Replace to standardize (find: size, replace: Size, apply to Option1 Name column only).

---

Step 6: Validate Image URLs

Image import failures are frustrating—product imports successfully but appears without images in store.

Image URL Requirements

Both platforms require:

• Full URL with protocol: https://example.com/image.jpg
• Publicly accessible (not behind login)
• Valid format: JPG, PNG, GIF, WebP
• Working link (200 status, not 404)

Shopify: Max 20 megapixels per image, max 20 MB file size, multiple images require separate rows with same Handle.

WooCommerce: Multiple images comma-separated in Images column, first image becomes featured image.

Common Problems & Fixes

Missing protocol: URL is www.example.com/product.jpg instead of https://www.example.com/product.jpg. Fix with Find & Replace: Find www., Replace https://www..

URLs with spaces: https://example.com/blue shirt.jpg breaks URL. URL-encode spaces to %20: Find (space), Replace %20.

Dead links (404 errors): Image deleted or moved. Check URLs manually by copying to browser, or fix/remove broken links.

Multiple Images

Shopify: Separate rows for each image with same Handle.

WooCommerce: Comma-separate URLs in Images column (wrap in quotes because commas separate URLs).

Don't use Dropbox/Google Drive/OneDrive links (require authentication). Use your web server, CDN, or platform media libraries.

---

Step 7: Check File Size Limits

Large CSV files fail to upload or cause timeout errors during import.

Platform Limits

Shopify: 15 MB maximum (~50,000 simple products, ~10,000-15,000 with variants).

WooCommerce: Server-dependent (PHP upload_max_filesize usually 2MB-128MB, max_execution_time usually 30-300 seconds).

If File Exceeds Limits

Split the file: Use CSV Splitter to divide by row count (10,000 rows per file, leaves headroom under 15MB). Import each separately.

Remove unnecessary columns: Keep only essential columns (Handle, Title, Options, SKU, Price, Image). Drop all optional columns using Column Operations. Can reduce file size by 50-70%.

Increase WooCommerce limits: If you control hosting, edit php.ini:

upload_max_filesize = 256M
post_max_size = 256M
max_execution_time = 600

---

Complete Pre-Import Checklist

Before clicking "Import," validate your CSV passes all requirements:

File-Level Checks

✅ Encoding: File is UTF-8 (not Windows-1252)
✅ File size: Under 15 MB (Shopify) or hosting limit (WooCommerce)
✅ Format: Comma-separated values (.csv extension)
✅ Structure: First row = headers, data starts row 2

Column & Data Checks

✅ Required columns present: Handle + Title (Shopify) or ID/SKU + Name (WooCommerce)
✅ Case sensitivity correct: Handle not handle
✅ No duplicate Handles/SKUs: Each product unique identifier
✅ Prices numeric: No currency symbols, units, or text
✅ Boolean values correct: WooCommerce uses 1/0, not true/false

Formatting Checks

✅ Descriptions wrapped in quotes: If they contain commas/quotes
✅ No smart quotes: All quotes straight " not curly " "
✅ No trailing spaces: Values don't have invisible spaces

Variant & Image Checks

✅ Option names consistent: "Size" not "Sizes" across variants
✅ All variants have option values: No blank Option1 Value cells
✅ URLs include protocol: https:// at start of every image URL
✅ Multiple images formatted correctly: Shopify = separate rows, WooCommerce = comma-separated

Pre-Import Testing

✅ Test with 10 products first: Import subset to validate format
✅ Check variant grouping: Verify variants appear under correct parent
✅ Validate inventory quantities: Stock levels match source data

---

Privacy-First CSV Cleaning

Professional ecommerce stores protect pricing and inventory data. Uploading product catalogs to third-party CSV tools exposes pricing strategy (profit margins visible), inventory levels (competitive intelligence), vendor information, and unreleased SKUs. For a complete privacy-first CSV workflow, see our data privacy checklist.

Client-side processing alternative: Browser-based tools that process data entirely in your browser. No uploads. No servers. No data exposure.

Core workflow:

1. Validate format: Upload to Format Checker (detects encoding, delimiter, structure)
2. Clean data: Use Data Cleaner (removes formatting issues, spaces, encoding problems)
3. Check uniqueness: Run Remove Duplicates on Handle/SKU column
4. Standardize text: Use Find & Replace (fix option names, URLs)
5. Re-validate: Final check confirms all issues resolved

Total time: 10-15 minutes for 5,000-product catalog. All tools process locally—no data ever leaves your browser.

---

FAQ

Conclusion

Product CSV imports fail for predictable, fixable reasons: wrong encoding (UTF-8 required), missing required columns, duplicate Handles/SKUs, formatting issues (commas, quotes, spaces), invalid variant relationships, broken image URLs.

The mistake most store owners make: Uploading messy CSVs and hoping the importer will figure it out. Platforms enforce strict rules. One error aborts entire import.

The systematic approach: Clean and validate before uploading using privacy-first tools that never expose pricing strategy or inventory levels to third parties. For comprehensive CSV troubleshooting across all platforms, see our CSV troubleshooting guide.

SplitForge's tools handle every common cleaning task—format validation, duplicate removal, text replacement, column operations—entirely in your browser. No uploads. No servers. No data exposure.

Import confidently. First time. Every time.

Fix product CSV errors with Data Cleaner for privacy-first validation and cleaning.

---

Sources:

• Shopify Help Center - CSV import documentation
• WooCommerce Documentation - Product CSV importer guide
• W3C UTF-8 Standards - Character encoding specifications

---

Managing ecommerce product imports? Connect on LinkedIn or share your workflow at @splitforge.