How to clean a supplier product CSV before importing to Shopify

By Zijian Xiao

A supplier spreadsheet can look perfectly readable and still need work before you import it. Product names sit under unfamiliar headers, a price uses a decimal comma, and an SKU has lost its leading zeros. The difficult part is deciding which changes are safe to automate and which require a decision about the underlying product.

Here is a spreadsheet workflow first, followed by a small, reproducible conversion example. The example uses synthetic supplier data that I actually ran through Rowmoss's local Python conversion code. It is a file-conversion test; I did not test an import into a live Shopify store.

Start with a copy and a narrow import goal

Keep the supplier's original file unchanged. Work on a copy, and decide what one row means before changing headers. For this walkthrough, each row represents one new product with one default variant. A table containing sizes, colors, or updates to existing products needs a different plan.

Write down which source columns contain the title, SKU, selling price, and optional barcode. Supplier terminology is inconsistent: Item Name, Item No., Retail Price, and EAN are the four headers in this example. Also identify columns you will intentionally leave out. A supplier category is not automatically a destination category mapping.

Clean it manually in Excel or another spreadsheet

Use the spreadsheet's text/CSV import controls rather than immediately opening and saving the file. Select the delimiter deliberately and set SKU and barcode columns to text before loading them. An identifier such as 00412 is a string, even though every character is a digit. Once a spreadsheet has saved it as 412, a converter cannot know how many zeros disappeared.

Next, check duplicate SKUs. A helper column using COUNTIF can flag identifiers appearing more than once. Inspect the flagged rows together: are they accidental duplicates, separate products assigned the same code, or variants? Do not delete every repeated code automatically. Reconcile them against the supplier's product records. Check case separately if your spreadsheet comparison treats uppercase and lowercase as equivalent.

For prices, establish the source format and currency before replacing punctuation. In a European-formatted column, 1.234,56 means 1234.56. Removing every comma would change the amount. Use locale-aware number parsing where available, then inspect the exported text. Currency labels need an explicit decision too: removing EUR does not convert euros into your store currency.

Trim unnecessary title whitespace, inspect missing values, and keep genuinely missing barcodes blank. Do not invent barcode numbers. Build a separate output sheet with the destination columns, export a CSV, then inspect the raw text in a text editor. Reopening that CSV in a spreadsheet can trigger the same identifier conversions again.

This is manageable for an occasional small file. The repetitive work is checking the same rules and rebuilding the same output layout each time.

A real local run with a messy supplier file

I made a four-product, semicolon-delimited CSV. These are the actual input cells; inline code preserves the intentional spaces:

I ran this file through Rowmoss's actual conversion code on my own machine (the same functions the site uses) and copied the results below verbatim.

Delimiter detection selected semicolon. Header suggestions mapped Item Name to title, Item No. to SKU, Retail Price to price, and EAN to barcode. I explicitly selected European pricing. Supplier Category was not mapped.

The first run did not produce an export:

The summary was four total rows, one valid row, three invalid rows, and three errors. The export function raised: “Fix every error. No products will be silently dropped.” Duplicate SKU matching in this converter is case-sensitive. It flags both occurrences rather than choosing a winner.

Fix source facts, then convert in one pass

Two corrections were deliberate human decisions. In this synthetic fixture, the board's correct supplier code is SB-021, so I changed its SKU. I also established that all prices already use the intended store currency and changed the hook's price to 12,00. Neither decision was inferred by the converter.

Those were the only source edits. I reran the same mapping and European price mode. All four rows passed with zero errors. This table contains the exact fields returned by the actual export function:

The output was a 412-byte UTF-8 CSV. The run checked that all four rows survived, identifier zeros remained, blank barcodes stayed blank, and every status was draft. Title whitespace collapsed and prices gained two decimal places. The unmapped supplier category was omitted.

Use the preview as a decision point

The tool workflow is: upload the CSV, confirm the column mapping and ignored columns, explicitly select the price format, and inspect the preview. Fix source errors and upload again when necessary. One conversion pass handles the supported formatting after the source facts are correct.

Rowmoss is scoped to new, single-variant draft products. It does not handle images, inventory, variant groups, synchronization, or existing-product updates. Files are limited to 1 MiB and 2,000 data rows. Preview is free; complete downloads use file credits. Review the destination import preview before proceeding, and check a small batch first. Passing this local validator does not establish that a live store import will succeed.

Disclosure: I built this. I'm Zijian Xiao, the developer of Rowmoss, and this is the first post I've written about it. If this narrow workflow matches your supplier file, try Rowmoss's preview. Start with a small file and check the transformed values before paying for an export.