Hidden spaces in a product CSV: find and fix them

Two lists that look identical refuse to match. A SKU you can see in both files is reported as missing. A price that reads 9.99 is rejected as not a number, or a lookup returns nothing for a product you know exists. Nothing looks wrong on screen, because a space at the edge of a cell is invisible.

Why it happens. A CSV is plain text. Between two separators, every character belongs to the value, including spaces. So "SKU-1002" and "SKU-1002 " (with a trailing space) are two different values, and a matching step that compares them exactly will treat them as different products.

The same goes for prices. " 9.99" with a leading space is not always read as the number 9.99. Some readers trim it, some keep it as text. Because that varies from reader to reader, a file can seem fine in one place and fail in another. Common sources are copy-and-paste from a web page or a document, manual typing, and exports from systems that pad fields to a fixed width.

How to find it in your own file. The synthetic example below has 5 data rows, not counting the header; the marker [_] stands for one hidden space. In it, 4 of the 5 rows have a problem and only row 1 is clean.

To look for the same thing in your file: compare the length of each cell with the length of the same cell with its edges trimmed (any difference means hidden edge spaces); sort the SKU column, because values with a leading space often group at the top; look for SKUs that appear to be duplicated but differ only by a trailing space; and check price cells that fail to be read as numbers.

Step-by-step fix. 1. Keep the original file untouched and work on a copy. 2. Trim the edges of every cell in the SKU and price columns; remove only leading and trailing spaces, not the spaces inside a value. 3. Check for non-breaking or other unusual space characters that a normal trim may miss, and remove them at the edges. 4. Save the copy as CSV with the same separator and encoding as before. 5. Write down each change: which row, which column, what was removed. The correction worksheet post gives a copyable template for this log.

Recheck after the fix. Run the same checks again on the saved copy. In the synthetic example, all 5 rows should now show equal lengths before and after trimming, and rows 2 to 5 should match their clean SKUs and prices. Then confirm the row count did not change, and that no SKU lost characters inside it. This is the check, fix, recheck loop described in the check-fix-recheck post. If the recheck finds new problems, add them to the log and repeat.

The free CSV check trims edge spaces before it checks a value, so it will not flag them itself; use the length comparison above for that. It does report two SKUs that differ only by edge spaces as a duplicate SKU, and it checks the same file for other problems such as empty titles or prices that are not plain numbers.