Fractional stock quantities in a CSV: find and fix

A stock column should hold whole units: 4 bottles, 12 mugs. Sometimes a cell holds a fraction such as 2.5 or 0,5. Depending on the program, such a row may be rejected or rounded without a warning, and a comma that is not inside quotes can be read as a column break. The stock you see after import can then differ from the stock you meant.

Why it happens. A spreadsheet formula divided the stock, for example by a pack size. A weight or length was copied into the stock column (2.5 kg instead of 2 items). A decimal comma was typed by habit. A unit conversion was done halfway.

This post covers fractions only. For a stock of 0 or below, see "Zero or negative CSV price and stock: find and fix". For words such as N/A in the stock column, see "Text in a CSV price or stock column: find and fix". If a comma may be splitting a row into an extra column, see "Decimal comma or column separator in CSV prices?".

Find them. 1. Work on a copy of the file and select the stock column. 2. Add a helper column next to it with a formula that gives the remainder after dividing the stock cell by 1, such as =MOD(C2,1) if stock is in column C, and fill it down. Any result other than 0 is a fraction: 2.5 gives 0.5, 12 gives 0. A cell stored as text may show an error instead of a number (verify). (Not run.) 3. Filter the helper column to show everything that is not 0, including errors. (Not run.) 4. Also filter or search the stock column for cells that contain a comma or a point, since text cells can hide fractions from the formula. The formula is an example; adapt the column letter, the function name and the argument separator (some programs use ; instead of ,) to your spreadsheet program.

In the synthetic example below, DEMO-001 has 12, DEMO-002 has 2.5 and DEMO-003 has 0,5. Rows DEMO-002 and DEMO-003 are the ones to fix.

Fix them. 1. Decide what one unit means for that product: whatever you sell one of is your unit. 2. If the fraction came from a pack calculation, work out whole packs or whole items and enter that number. 3. If the fraction is a weight or length, move it to a description or attribute column, and put the count of sellable items in the stock column. 4. If the value is a rounding leftover, round down. Rounding up promises stock you do not have. 5. Replace a decimal comma with the whole number you decided on, so no stray comma splits the row. 6. Keep the stock column formatted as a plain number, not text or currency. 7. Write each change (row, old value, new value) in a note so you can undo it; "CSV correction worksheet: log each fix, recheck" shows one way.

If both values in the example were rounding leftovers, DEMO-002 becomes 2 and DEMO-003 becomes 0. A stock of 0 is right only if the item is really out of stock.

Recheck. 1. Repeat the helper-column filter. It should show no rows other than 0 and no errors. (Not run.) 2. Confirm no stock cell contains a comma or a point. 3. Compare the row count with the original file. Fixing stock should not add or remove rows. 4. Delete the helper column, save as CSV, and reopen the saved file, not the spreadsheet version, to confirm the values survived saving.

The free CSV check on this site does not look at the stock column, so it will not report fractional stock. Find and fix fractions with the steps above. You can still run the free check for the other problems it looks at, such as price, SKU and barcode. A clean report means only that its checked rules passed; it does not mean your platform will accept the file.