Category or brand names in a CSV: find and fix

A store import can read "Shoes", "shoes" and "Shoes " as three categories. Brands split the same way: "Acme", "ACME" and "Acme Inc". (Synthetic example data: these names are made up.)

Why it happens. The usual causes: capital letters differ between rows. Spelling varies or contains typos. Extra spaces sit at the start or end of a cell. Rows were pasted together from several supplier lists.

How to spot it. Sort the category column and read down it. Near-duplicates often land next to each other. Build a list of the unique values in the category column, then in the brand column. If you see more values than you expect, some are variants. Look for values that read the same but appear as separate groups. In the synthetic example below, three rows hold one category and one brand, each written three ways.

Step-by-step fix. 1. Save a copy of the file. Keep the original untouched. 2. List every unique value in the category column and the brand column. 3. For each group of variants, choose one accepted spelling, including capital letters. Write these choices in a short list; "CSV correction worksheet: log each fix, recheck" shows one way to log each change. 4. Remove spaces at the start and end of every cell in both columns (see "Hidden spaces in a product CSV: find and fix them"). 5. Replace each variant with its accepted spelling. Match the whole cell only, so a short name is not changed inside a longer one. 6. Fix typos by hand. If unsure, check the product name or your supplier's own list. 7. Save as CSV and keep the column headers exactly as they were.

Check again after fixing. 1. Build the unique list for both columns again. 2. Confirm each accepted spelling appears once, and the number of values matches what you expected. 3. Open five or six rows from different categories and brands to confirm they read correctly. 4. Re-check the corrected file.

The free CSV check on this site looks at the brand column only for empty cells: it reports a row whose brand cell is blank, in the first 20 data rows, when the column is headed brand, vendor or marka. It does not compare brand or category spellings, and it has no category column, so it will not find the variants above; use the unique-value lists for that. It also checks the same file for other problems, such as empty titles, prices that are not plain numbers and duplicate SKUs.