DEV Community

Steven Keil
Steven Keil

Posted on AI-assisted

A Shopify Product CSV Before and After: What Gets Fixed Automatically

#shopify #ecommerce #csv #tutorial

Shopify's product CSV import is strict. A price written the European way, a handle with a space, or a "yes" where it wants TRUE is enough to make a row fail or import wrong. Here is a small file with typical mistakes, and what a rule-based check does with it.

The input 

Handle,Title,Body (HTML),Vendor,Variant Price,Variant Inventory Qty,Published
Blue T-Shirt,Blue T-Shirt,<p>Soft cotton</p>,Bothot,"1.299,00",5,TRUE
blue tshirt,Blue T-Shirt Duplicate,<p>Copy</p>,Bothot,$12.50,3,yes
red-mug,Red Mug,<p>Ceramic mug</p>,Bothot,"12,5",abc,TRUE
,No Handle Item,<p>Missing handle</p>,Bothot,9.90,2,FALSE
Enter fullscreen mode Exit fullscreen mode

Four rows, and almost every row has a problem:

  • Row 1: the handle has capital letters and a space, and the price uses a dot as thousands separator and a comma as decimal.
  • Row 2: another handle with a space, a price with a dollar sign, and yes instead of TRUE.
  • Row 3: the price 12,5 has a decimal comma, and the stock quantity is the text abc.
  • Row 4: no handle at all.

What can be fixed automatically

These are mechanical fixes where there is only one sensible result:

line 2: Handle 'Blue T-Shirt' -> 'blue-t-shirt'
line 2: Variant Price '1.299,00' -> '1299.00'
line 3: Handle 'blue tshirt' -> 'blue-tshirt'
line 3: Variant Price '$12.50' -> '12.50'
line 3: Published 'yes' -> 'TRUE'
line 4: Variant Price '12,5' -> '12.50'
Enter fullscreen mode Exit fullscreen mode

Six fixes, no judgment calls.

What must stay a human decision 

line 4: Variant Inventory Qty 'abc' is not a whole number
Enter fullscreen mode Exit fullscreen mode

A script cannot know whether abc should be 0, 10 or an empty field, so it should not guess. A good check reports it and leaves the cell alone. The same goes for missing prices, duplicate SKUs and broken image URLs.

This split is the important part: fix only what is unambiguous, and list everything else. Silent guessing is how a wrong price ends up in a live store.

Doing it yourself

You can do all of this in a spreadsheet with find-and-replace and a few formulas, or with a short script. The things worth checking every time:

  1. Handles: lowercase, hyphens instead of spaces, unique per product (variants of one product share the handle on purpose).
  2. Prices: plain number with a dot as decimal separator, no currency symbols or thousands separators.
  3. Boolean columns such as Published: exactly TRUE or FALSE.
  4. Numeric columns such as inventory: whole numbers only.
  5. Required columns present and header names spelled exactly as Shopify expects.

If you would rather not

I turned these rules into a small service. You email your product CSV and get back a cleaned file plus a short report, usually within minutes, no access to your store needed (up to 2,000 rows). It is my own paid service, 15 EUR per file, with a refund if nothing needed fixing. A free short summary of what it finds is available before you buy; the address is on the product page.

https://skeilster.gumroad.com/l/shopify-csv-check

Top comments (0)