Product CSV files look simple until one bad upload breaks hundreds of listings. A single trailing space in a SKU, a duplicate variant row, or a category value that does not match the marketplace taxonomy can create hours of manual cleanup.
This guide shows a practical pre-upload workflow for cleaning product CSV files before they go into Shopify, Amazon, an ERP, a PIM, or a marketplace feed.
Start with a product data audit
Before changing anything, create a backup copy and scan for the fields that usually cause upload failures.
| Field | Common problem | Fix |
|---|---|---|
| SKU | Spaces, duplicates, mixed casing | Trim, uppercase, check unique count |
| Product title | Too long, repeated brand names | Standardize title pattern |
| Category | Free-text values | Map to approved taxonomy |
| Price | Text values, currency symbols | Convert to numeric format |
| Variant | Missing color or size rows | Validate parent-child grouping |
Step 1 — Standardize SKUs first
SKUs are the key that connects product data to inventory, orders, purchasing, and reporting. Clean them before you clean anything else.
After trimming and uppercasing, create a duplicate check column:
Any value greater than 1 should be reviewed before upload.
Step 2 — Normalize categories with a mapping table
A common mistake is trying to fix categories row by row. Instead, create a mapping table with two columns: current category and approved category.
| Current category | Approved category |
|---|---|
| Phone Acc. | Electronics Accessories |
| phone accessory | Electronics Accessories |
| Parts - Mobile | Electronics Accessories |
Then use XLOOKUP or Power Query Merge to apply the approved value across the full file.
Step 3 — Validate required fields
Most upload failures come from blank required columns. Create helper columns that flag missing values before you upload.
Step 4 — Clean prices and quantities
Remove currency symbols, commas, and hidden text formatting. In Power Query, use Replace Values to remove symbols and then set the data type to Decimal Number or Whole Number.
Step 5 — Create an upload-ready validation tab
Before exporting, summarize errors by type:
- Duplicate SKUs
- Missing required fields
- Invalid prices
- Blank image URLs
- Unknown categories
- Variant groups with missing parent rows
This turns the CSV from a risky upload into a controlled workflow.
Need a product CSV cleaned?
ExcelOps can clean, validate, and format product spreadsheets for ecommerce, ERP, and marketplace uploads.
View CSV Cleanup Service →