An Ecommerce Export With Commas Inside Product Names

A shop exports its catalog to CSV, and the product names contain commas: Cable, USB-C, 2 m, Stand, aluminium, adjustable. The export did not quote them. A line meant to have four fields now has six, every column after the name is shifted, and the loader either rejects the line or loads the price into the stock column. This is the unquoted-delimiter problem, one of the commonest damage patterns, and it is fixable when the damaged column is the only free-text one. This article diagnoses it, repairs it with a short script and shows where the repair stops being safe. It is one of the problems in worked CSV problems and the patterns behind them, and the quoting rules it breaks are explained in how CSV quoting works.

Diagnose with field counts

Here is the file. Look at lines 2 and 4:

sku,name,price,stock
A100,Cable, USB-C, 2 m,9.90,120
A101,Mouse,14.50,35
A102,Stand, aluminium, adjustable,39.00,8
A103,"Hub, 4 port",24.00,60

The header has four fields. The first thing to check is how many each line has, through a CSV parser, not by counting commas yourself:

import csv
print([len(r) for r in csv.reader(open("products.csv"))])

[4, 6, 4, 6, 4]

The header, A101 and the quoted A103 have four fields. A100 has six, and so does A102: two and two extra commas. The extra fields equal the extra commas in the name, which is what unquoted commas look like. Line 5 shows the file is not uniformly broken: the exporter quoted "Hub, 4 port" correctly, perhaps because that name came through a different code path or a newer version. A file that mixes quoted and unquoted commas is common, and it means a fix that assumes every comma is unquoted would be wrong, so the script has to handle the already-quoted rows without damaging them.

The rule: anchor on the ends

The structure is known even though the commas are ambiguous. The first field is a SKU, a short code with no commas. The last two fields are a price and a stock count, numbers with no commas. Everything between them is the name, however many pieces it was split into. That rule is stated in one sentence, and it is the specification of the repair: take row[0] as the SKU, row[-2] and row[-1] as price and stock, and join row[1:-2] with commas as the name.

import csv, sys

EXPECTED = 4   # sku, name, price, stock
with open(sys.argv[1], newline="", encoding="utf-8") as f, open(sys.argv[2], "w", newline="", encoding="utf-8") as out:
    reader = csv.reader(f)
    writer = csv.writer(out)
    header = next(reader)
    writer.writerow(header)
    for line_no, row in enumerate(reader, start=2):
        extra = len(row) - EXPECTED
        if extra < 0:
            sys.exit(f"line {line_no}: only {len(row)} fields")
        # The first field and the last two are well behaved; everything between belongs to the name.
        sku, middle, price, stock = row[0], row[1:len(row) - 2], row[-2], row[-1]
        name = ",".join(part for part in middle).replace(" ,", ",")
        writer.writerow([sku, name, price, stock])
        if extra:
            print(f"line {line_no}: rejoined {extra + 1} pieces into {name!r}")

line 2: rejoined 3 pieces into 'Cable, USB-C, 2 m'
line 4: rejoined 3 pieces into 'Stand, aluminium, adjustable'

The script refuses a line with fewer than the expected fields, which would be a different kind of damage, and prints every repaired line with the name it produced, so a person can read the result. It writes through csv.writer, which adds the quotes the exporter should have added. The output file:

sku,name,price,stock
A100,"Cable, USB-C, 2 m",9.90,120
A101,Mouse,14.50,35
A102,"Stand, aluminium, adjustable",39.00,8
A103,"Hub, 4 port",24.00,60

Every row now has four fields. The already-quoted A103 went through unchanged: the parser had read it as four fields, so extra was zero and the name was Hub, 4 port as before. The rows that were wrong now have the names they should.

Proof

Run the field-count check on the output: all lines should be 4. Then check the values against what you know about each column. The prices should all parse as decimals and the stock as integers, which is a stronger test than the field count, because an off-by-one repair would put a name fragment where a number belongs and the cast would fail. In SQL, TRY_CAST(price AS DECIMAL(10,2)) IS NULL should return no rows. Compare the row count with the input's, and if the shop can tell you the number of products, compare with that. And read a sample of the names: the repaired ones are the ones to eyeball, and the printed lines are the list.

One detail in the script deserves comment. The line .replace(" ,", ",") normalizes a space before a comma. It was added for names where an exporter had padded; leave out any such cosmetic step unless you know the data needs it, since every extra rule is something you have to justify. The version in the repository should show only the rejoin and say what it assumes.

Where the repair stops being safe

The anchor rule works only if the commas can appear in exactly one column and the columns around it have a recognizable shape. Three situations break it.

Two free-text columns: if the file also had a description column with commas, you could not tell where the name ends and the description begins. A100,Cable, USB-C,Short cable, cheap,9.90,120 could split four ways, and no program can choose. The only fixes are a convention you can verify (names never contain more than two commas) or a re-export with quotes.

A free-text column at an end: if the name were the last column, there would be no right-hand anchor, and the number of pieces to join would be unknown. A left anchor of known width only works if there is a fixed count of columns after the name.

A name that ends in something that looks like the anchor shape: Cable, 9.90 m has a piece that parses as a number, and the right-anchored rule still works because it counts from the right by position and does not look at what the pieces contain. A rule that scans from the right until it finds "something numeric" would break here, and positions are safer than patterns.

For files where the repair is ambiguous, quarantine the rows: write the lines that cannot be repaired confidently to a rejects file with their line numbers and let a person decide (logging rejected rows from a CSV import for review).

Fix it at the source

The repair is a patch for an exporter that does not follow RFC 4180, which asks for fields containing commas to be quoted. Tell whoever owns the export, with the line numbers and the field counts, and ask for quoting. Most platforms have a setting or a newer export format that does it correctly, and sometimes the problem is that someone saved the export from a spreadsheet that dropped the quotes. Until then, put the field-count check at the door of your pipeline so the next change, a different delimiter in a product name, a second free-text column, is caught as an error and not loaded as shifted data (checking row and column counts in a CSV). The Python module used here is described in the csv reference.