Weight and size units in a product CSV: find and fix

You import your product file and it finishes. Then a 250 g candle shows as 250 kg, or a box 30 cm wide shows as 30 mm. Some rows are rejected. Others are accepted with a quietly wrong number, so shipping prices and size filters use the wrong value.

Why it happens. Mixed units in one column: some rows use g, others kg, lb or oz, and nothing says which is which. Unit stuck to the number: "2.5kg" or "12in" is text, not a number, and many shops reject it or read only part of the cell. Decimal comma: "1,5" may be read as 1.5, as 15, or as text, depending on the shop's settings. Unit and value disagree: the value says 0.5 and the unit says g, but the product is meant to weigh 0.5 kg. Mixed size units: length, width and height appear in cm, mm and in within one file.

Step-by-step fix. 1. Check your shop's import guide and pick one unit per measurement for the whole file, for example grams for weight and cm for size. 2. Separate the unit from the number. Keep only the number in the value cell. Put the unit in a unit column or in the header (weight_g), and remove units from the cells. 3. Change decimal commas to decimal points: "1,5" becomes "1.5". Do this after separating the unit and before any conversion. If you drop the comma first, 1,5 becomes 15 and 15 x 1000 = 15000. 4. Convert the rows that use other units: 1 kg = 1000 g; 1 lb = 453.6 g; 1 oz = 28.35 g; 1 in = 2.54 cm; 1 cm = 10 mm. 5. Sort the weight column from smallest to largest and look at the top and bottom rows. Values far outside the rest usually have the wrong unit. Sorting is unreliable on text cells such as "2.5kg", so do it only after steps 2 and 3. 6. Compare each unit with its value and ask whether the number is plausible for that product. A phone case is not 2 kg. 7. Save as CSV with UTF-8 encoding. Reopen the file in a plain text editor to confirm nothing changed. Try 3 to 5 rows first, then the rest.

In the synthetic example below (made up for this post: not real customers, shops or SKUs), SYN-001 has its units stuck to the numbers. SYN-002 has a decimal comma, and its width is in inches. In SYN-003 the value and unit disagree: the row says 0.5 g, but this product is meant to weigh 0.5 kg, which is 500 g; its width is also in mm while other rows use cm. In SYN-004 the weight unit is inside the cell, and the width has no unit.

Two rows in the right order. SYN-002 weight: step 3 turns 1,5 into 1.5 (kg), then step 4 gives 1.5 kg x 1000 = 1500 g. SYN-003 weight: step 6 flags 0.5 g as implausible for this product; after the seller confirms the intent (kg), step 4 gives 0.5 kg x 1000 = 500 g.

Confirm two things before you keep the result. For SYN-003, confirm that 0.5 was meant as kg, not g. For SYN-004, confirm that the width 8 was meant in cm. A file cannot tell you what you meant, so only you can confirm these.

The free CSV check on this site does not read weight, size or measurement columns at all. It reads only columns whose header it recognises: SKU, title, price, brand and barcode, plus date columns. Headers such as weight, size, Gewicht or ağırlık have no match, so unit text and mixed units in those columns (as in SYN-001, SYN-002 and SYN-004 above) pass with no warning. It catches unit text only when it lands in the price column itself (in the first 20 data rows), where it is reported as a price that is not a plain number, not as a unit problem. It never catches a unit and value that disagree, such as SYN-003: there is no check for that. So a clean result says nothing about your weight and size data; check those columns yourself with the steps above.

What we're not sure about. The unit each shop expects is not stated here: check your own import guide.