How to clean a Shopify or WooCommerce product or order export
Updated
Store exports are CSV files you can fix in bulk and import again, or use for reports. They have a few rules that generic clean-up advice ignores, and breaking them can create duplicate products or lose variants on re-import.
Know how the export is structured
In a Shopify product export, one product often spans several rows: each variant and extra image gets its own row with the same Handle. Those rows are not duplicates, so never remove duplicates by Handle alone.
Order exports usually have one row per line item, so an order with three products appears on three rows with the same order number.
WooCommerce's built-in product exporter has its own columns, but the same idea applies: check how variations are represented before deleting rows.
Safe things to clean
- Trim spaces at the start and end of titles, SKUs, tags and vendor names.
- Make capitalisation consistent in fields like vendor or product type.
- Find and replace old values (a renamed collection or supplier).
- Find empty values in required columns such as price or SKU before importing.
- Remove true duplicates only on a column that should be unique, such as the variant SKU.
Keep the header row and column names exactly as exported, keep the file as CSV, and test the re-import with a few products before the whole catalogue.
The private way: in BenchCove
Free, no sign-up. Uses the File & Data Workbench. Nothing is uploaded.
- Import the export in the File & Data Workbench. It is read in your browser and not uploaded.
- Check the import summary for empty values, duplicate rows and each column's type.
- In Clean, trim spaces, change case, find and replace, and fill or remove empty values on the columns you choose, with a preview of every change. Remove duplicates only on a unique column such as the SKU.
- Use Compare to check exactly what changed against the original export, then export the CSV with the same columns.
Questions
Will cleaning change my column names?
Not unless you rename columns yourself. Keep the original names if you plan to import the file back into your store.
Why do I see the same product on several rows?
Each variant or extra image has its own row with the same handle. That's how the format works; those rows belong together.