Quick Answer
A product CSV can pass a platform's upload check and still produce a broken catalog. "Imported successfully" means the file parsed — it does not mean the data is correct. The fix is a ten-step validation sequence run before upload: encoding and delimiter checks first (encoding errors corrupt delimiter detection, making all subsequent checks unreliable), then structural checks on headers and Handle uniqueness, then business-rule checks on price fields and required values. Data Validator's built-in E-commerce Orders preset covers most of the data-rule checks out of the box; Format Checker covers the file-format layer.
TL;DR: The product import said "completed successfully." The store showed 800 products. What it didn't show: 73 products had Compare-At prices lower than their regular prices (Shopify silently dropped the Compare-At value), 12 products had image URLs that failed to load because the URL format was invalid, and 6 SKUs were blank because Excel had stripped their leading zeros. No error. No warning. A customer found the first pricing inconsistency.
The validation sequence below catches all of these before the file reaches the platform.
The Pre-Import Validation Checklist
Run these ten checks in order. Order matters: encoding errors corrupt delimiter detection, so checks 1–3 must complete cleanly before checks 4–10 are reliable.
| # | Check | Tool | What to look for |
|---|---|---|---|
| 1 | Encoding is UTF-8, no stray BOM | Format Checker | Re-encode if Windows-1252 / ANSI detected; remove BOM if present |
| 2 | Delimiter is comma (not semicolon or tab) | Format Checker | European-locale Excel exports default to semicolon |
| 3 | Consistent column count across all rows | Format Checker | Inconsistent count = structural damage or extra comma in a field |
| 4 | Required headers present, exact case, no trailing spaces | Manual or Data Validator required rule | Handle ≠ handle; Variant SKU (trailing space) silently breaks import |
| 5 | Handles and SKUs are unique | Data Validator uniqueness + Remove Duplicates | Duplicate Handles produce "handle already exists" errors or silent merges |
| 6 | Data types correct — prices numeric, quantities integer | Data Validator dataType rules | A price column containing $12.99 fails Shopify's numeric-only requirement |
| 7 | Compare-At Price exceeds Variant Price (if both are set) | Data Validator range / regex rule | Shopify drops the Compare-At silently if it violates the rule |
| 8 | Image URL format is valid | Data Validator url rule (format only — not reachability) | Validates URL syntax; does not confirm the image is publicly accessible |
| 9 | No leading zeros stripped from SKU or barcode | Visual inspection / Data Validator regex | 012345 → 12345 is unrecoverable from the CSV if the source is gone |
| 10 | No empty rows or columns | Data Cleaner | Trailing empty rows cause row-count mismatches; empty columns waste column mapping |
Table of Contents
- Why Encoding Validation Comes First
- Structural Checks: Headers, Handles, and Column Count
- Data-Rule Checks: Prices, Types, and Required Fields
- Validate Before Upload, Not After Rejection
- FAQ
Why Encoding Validation Comes First
Encoding errors do not just garble text — they corrupt the file's structure in a way that makes every subsequent check unreliable. A Windows-1252 file opened by a comma-delimiter detector may produce incorrect field counts because the detector misinterprets multi-byte sequences as delimiter or quote characters. A BOM-prefixed file may cause the first column header to appear with a three-character prefix, breaking header matching for the entire import.
Check 1 — Encoding: UTF-8, no BOM
Run Format Checker first. It detects encoding using byte-frequency analysis and reports the result before any other check. If the result is Windows-1252 (ANSI / CP1252) — the default for Excel on Windows — re-encode to UTF-8 before continuing. Format Checker performs this re-encoding in your browser; the original file is not uploaded anywhere.
BOM (Byte Order Mark): a EF BB BF prefix on UTF-8 files is injected by some tools as an encoding signal. Most Shopify import flows handle BOM correctly, but a malformed BOM can cause the first header cell to appear as Handle instead of Handle, breaking the import mapping silently. Format Checker detects and reports BOM presence.
Check 2 — Delimiter: comma
Shopify and WooCommerce both require comma-delimited product CSVs. Excel on European-locale systems defaults to semicolon as the list separator, producing a semicolon-delimited file when saved as CSV. Format Checker detects the actual delimiter character; if it reports semicolon or tab, the file must be converted to comma-delimited before any row-level validation is meaningful.
Amazon Seller Central flat files use tab-delimited format — that is intentional and correct for Amazon. For Amazon-specific validation, see Fix Invalid SKU Format in Amazon Seller Central CSV.
Check 3 — Column count consistency
Every row in a valid product CSV must have the same number of fields as the header row. Format Checker checks the first 10,000 rows for column count consistency and reports any row where the count differs. A count mismatch on an interior row indicates one of three causes: an unescaped comma inside a field (a product title or description containing a comma that was not properly quoted), a missing field (a row was edited and a column deleted), or structural damage from spreadsheet sorting.
A file with column-count inconsistencies cannot be reliably validated at the data-rule level — fix structural errors before running checks 4–10.
Structural Checks: Headers, Handles, and Column Count
Check 4 — Required headers present, exact case, no trailing spaces
Shopify's product CSV requires specific column headers with exact case and no whitespace variation. Handle works; handle fails. Variant SKU works; Variant SKU (trailing space) causes that column to fail import mapping silently — the column simply does not import. These failures produce no error; the field values are ignored.
Format Checker does not validate header names against a platform template — that check is a schema conformance check, not a file-format check. Run it manually: download the official Shopify product CSV template from your store's Products → Import → download template. Compare your column headers character-for-character against the template. For a programmatic check, configure a Data Validator required rule on each of your required headers.
Use Data Cleaner trimWhitespace to remove leading and trailing spaces from all cells — including header cells — before validating. A header cell that appears correct visually may still carry a non-printing space character.
Check 5 — Handle and SKU uniqueness
Each product Handle must be unique in the import file (one parent row per Handle, unless you are intentionally adding variant rows under an existing Handle). Duplicate Handles produce either "handle already exists" errors or silent product-data merges — the import processes without an error banner, but the resulting product may have combined variant lists from two different products.
Run Data Validator with a uniqueness rule on the Handle column. For near-duplicate detection — shirt-red vs shirt_red, blue-tshirt vs blue-t-shirt — use Remove Duplicates with fuzzy matching (Jaro-Winkler or token-based) to surface variants that exact-match deduplication misses. For the full deduplication workflow, see How to Clean a Product Catalog CSV for Shopify and WooCommerce.
Data-Rule Checks: Prices, Types, and Required Fields
Loading the E-commerce Orders preset
Data Validator includes a built-in E-commerce Orders preset. Load it first: it covers required fields, numeric type checks, and common e-commerce data rules out of the box. Extend it for your catalog's specific requirements rather than configuring rules from scratch.
Check 6 — Data types: prices numeric, quantities integer
Shopify requires Variant Price and Variant Compare At Price to be numeric values with no currency symbols — 12.99 not $12.99, 12.99 not 12,99 (European decimal format). A price column containing currency symbols or non-numeric characters fails validation at the row level. Configure a dataType: decimal rule on price columns in Data Validator. For Variant Inventory Qty, use dataType: integer.
WooCommerce uses regular_price and sale_price with the same numeric-only requirement.
Check 7 — Compare-At Price rule
Shopify requires Variant Compare At Price to exceed Variant Price when both are set. If Compare-At Price is less than or equal to Variant Price, Shopify silently drops the Compare-At value on import — the sale pricing disappears from the product without an error. Configure a range or custom regex rule in Data Validator to flag rows where Compare-At ≤ Price.
Check 8 — Image URL format
Data Validator's url dataType validates URL format — that the value is a well-formed URL (scheme present, no illegal characters, valid domain structure). It does not confirm that the image is publicly accessible or that Shopify can fetch it at import time.
A URL that passes format validation may still fail at import if the image is behind authentication, returns a non-200 HTTP response, or is a private URL. To verify image reachability, open a sample of image URLs in a browser before importing. Do not claim that format validation confirms reachability — it does not.
Check 9 — Leading zeros in SKU and barcode fields
Excel auto-converts columns that look like numbers to numeric format when it opens a CSV, stripping leading zeros from SKU and barcode values. A Variant SKU of 012345 becomes 12345. This conversion happens silently at the file-open step — the original values are lost in the CSV.
Configure a regex rule in Data Validator matching your expected SKU format (for example, ^\d{6}$ for a fixed-length 6-digit SKU). This catches truncated values that no longer match the expected length or pattern. Prevention is the correct fix: open CSVs via Excel's Data → From Text/CSV import wizard, setting numeric-looking columns to Text format before loading. Recovery from the CSV alone is often not possible if the source system is unavailable — see Recover a Corrupted or Broken Shopify Product CSV for the options.
Check 10 — No empty rows or columns
Trailing empty rows at the end of a product CSV produce row-count mismatches in some import systems and make format checks less precise. Empty columns (a column with a header but no values in any row) can break import mapping in edge cases. Use Data Cleaner with removeEmptyRows and removeEmptyColumns operations before import.
Validate Before Upload, Not After Rejection
The platform error loop — upload, read the error, fix one field, re-upload — is slow and often misleading. A single error message may correspond to multiple root causes: "error importing your file" maps to encoding, quoting, delimiter, and header issues simultaneously. The platform reports the first parse failure and stops — it does not surface all errors in one pass.
Running the ten-step sequence before upload surfaces all errors in one pass, against a readable validation report rather than a platform error screen. The workflow:
- Run Format Checker (checks 1–3). Fix any reported encoding, delimiter, or column-count issues.
- Run Data Validator with the E-commerce Orders preset extended for your catalog (checks 4–9). Fix all flagged rows.
- Run Data Cleaner
trimWhitespace+removeEmptyRows(checks 4, 10). Re-run Data Validator to confirm zero errors. - Upload to the platform.
A file that clears all ten checks imports without row-level errors. Silent failures from price-rule violations and image-URL issues are also prevented — those are the failures that produce a success banner but a broken catalog.
For the split-and-batch workflow needed for large catalogs (15MB+ or 1,000+ new variants), see How to Split a Large Shopify Product CSV Without Breaking Variants. For the full e-commerce CSV error taxonomy and platform format comparison, see Shopify & WooCommerce Product CSV: Fixes, Imports & Migrations.
Additional Resources
Methodology: Validation rules verified against Shopify and WooCommerce product CSV documentation, May 2026. Platform behavior on rule violations (silent drop vs. error) may vary by plan version — verify against your store's current import error output.
Platform documentation:
- Shopify: Product CSV file format — Column definitions, required fields, and Handle/variant structure
- Shopify: Import a CSV file — Import workflow and error reporting
- WooCommerce: Product CSV importer and exporter — Required fields and column format for WooCommerce imports
Standards:
- RFC 4180: Common Format and MIME Type for CSV Files — The CSV quoting and delimiter standard; the reference for column-count consistency and quote compliance
- Unicode FAQ: UTF-8, UTF-16, UTF-32 & BOM — Encoding reference; explains BOM, byte-order marks, and UTF-8 vs. Windows-1252 differences
- MDN Web Docs: TextDecoder — How browsers handle encoding detection in client-side file processing