A comma in a product description pushes the price into the wrong column: read quoted fields properly

Why only some rows break

The rows that scramble are the ones with punctuation inside a field. RFC 4180, a description of common practice rather than a standard, says a field that contains a line break, a double quote or a comma should be wrapped in double quotes, and a double quote inside such a field is written twice. A reader that follows this treats Hinge, brass in quotes as one name. A reader that splits at every comma sees two fields, and everything after it moves one column to the right. The ordinary rows that have no punctuation import correctly, which is why the fault hides.

Line breaks are the harder case. A description with a line break inside quotes is one record spread over two lines. A tool that treats each line as a record reports two broken products, and a reader that counts lines will disagree with the supplier's stated number of products. Python's documentation makes the same point: reader.line_num counts lines read, which differs from the number of records because a record can span lines, and files should be opened with newline='' so embedded newlines are read correctly.

• Symptom: a price, stock figure or code appears in a description for a few products only.
• Symptom: the importer's product count is higher or lower than the supplier's count.

The delimiter is not always a comma

Many suppliers export semicolons or tabs, often because a regional setting treats the comma as the decimal mark. A file with a semicolon delimiter read with a comma reader comes out as one wide column; worse, a decimal comma inside a price splits that price in two. The example below, invented and computed with Python's csv module, shows the same line read both ways.

Detection is not a safe fix. When more than one delimiter fits the sample equally well, Python's Sniffer picks by a documented preferred list, in that order, rather than by what the supplier meant, and the csv documentation calls its has_header() method a rough heuristic that may be wrong either way. The W3C tabular vocabulary takes the opposite approach: the dialect, with its delimiter and quote character, is declared. A supplier layout should declare it too.

If the matrix is wider than the box, scroll horizontally to read every column. Keyboard: focus the matrix and use Left/Right.

line: A200;Hinge, brass;4,20
read with comma     -> ['A200;Hinge', ' brass;4', '20']      (3 fields, wrong)
read with semicolon -> ['A200', 'Hinge, brass', '4,20']   (3 fields, right)
(synthetic line; computed, not from a supplier file)

Other diagnoses to rule out

A new, renamed or reordered column is a different fault: the header no longer matches what the importer expects. The existing guide on header changes covers it. A wrong character set shows as odd letters, not shifted fields. A supplier that does not quote fields that contain the delimiter at all produces a file that no reader can separate reliably, and the only fix is the supplier's export. Finally, an unclosed quote makes a reader treat everything from the opening quote to the next quote, or to the end of the file, as one field, so the products after it vanish into one huge value and the file can look mostly empty. Nothing after an unclosed quote can be trusted, which is why the paid job rejects such a file as a whole.

A safe first investigation

Take five to ten rows, including one that goes wrong, and replace real prices with invented ones. Count the fields on each line and, separately, on each record as a proper reader sees it. Lines whose count differs from the header are the places to look. Then read the file with the delimiter and quote character set explicitly, and check that records read, records accepted and records rejected add up. Do not edit the only copy by hand.

• Ask the supplier which delimiter and quote character the file uses, and whether line breaks can occur inside fields.
• Keep the reply with the layout description.

What fixes it, and what does not fit

The fix is a real CSV reader with the supplier's delimiter, quote character and doubled-quote rule declared for that layout, line breaks inside quotes read as data, and a header and field-count check against the agreed layout, and a reject list: each row with the wrong number of fields is written out with its record number, its line number, its original text and the reason, instead of being stored shifted or dropped. A file with an unclosed quote is different: the reader cannot say where the field ends, so the whole file is read before anything is stored and the file is rejected as a whole, naming the line where the unreadable record starts. A regression test uses invented rows with a comma, a doubled quote and a line break, and a file with an unclosed quote followed by good rows, none of which may be stored.

Not a fit: guessing a delimiter for unknown files, repairing rows already stored in the wrong fields, or accepting a file whose quoting is missing altogether.

How the paid job is accepted

The fixed job csv-delimiter-quoting-embedded-newlines-import is £195 for one importer path and one named supplier layout. A synthetic file with a comma, a doubled quote mark and a line break in description fields must import at the agreed column count with every field as expected; a file in the agreed delimiter must give the same values while a file in a different delimiter, whose header or field count does not match the agreed layout, is rejected; rows with too few or too many fields must go to a reject list and rows read must equal rows accepted plus rows rejected; and a file with one unclosed quote followed by good rows must be rejected as a whole, with no row stored and the line where the unreadable record starts named. Prices are untested proposals, and payment follows the agreed checks and your sign-off. Nothing is booked or charged by an enquiry.