Counting Lines Gives the Wrong Answer

In one line

In CSV, a line and a record are different things, and the moment you believe the two are the same, both the row count and the amount go quietly wrong.

Why this was needed

You opened the sales file the customer gave you and typed wc -l, and 431 came out. But the customer says there are 402 cases. You spend half a day finding the cause of the difference of 29.

The difference usually comes from two things. One is a line break inside quotes. If an order memo is written on split lines, that record occupies two lines in the file. The other is a delimiter inside quotes. If the company name is "Moonshop, Inc.", a column is added when you cut that line at the comma. Because the amount cell shifts by one, the amount of that line drops out of the total or a wrong value gets added.

What is frightening is that no error occurs. The parser produces a number without saying a word, and that number looks plausible. This kind of mistake survives until the customer says "our sales are more than this."

How it works

RFC 4180 sets down the common rules of CSV. The core is four lines.

Records are separated by CRLF. The CRLF of the last record may or may not be present.
If a field contains a delimiter, a double quote, or a line break, that field is enclosed in double quotes.
A double quote inside an enclosed field is written as two ("").
Whether there is a header line is signaled by the header parameter of text/csv — the file itself has no such marker.

Three problems that practice runs into follow right from here.

First, this specification is a recommendation, and the world does not all follow it. RFC 4180 itself says at the start that CSV has long been used without a formal specification and that this document records the commonly used form. The files that actually arrive have a semicolon or tab delimiter, line endings that are LF or mixed within one file, and no header.

Second, whether there is a header cannot be known from the file alone. So you have to estimate. A usable yardstick is that there are no numbers in the first line but there are in the lines that follow. But this is a yardstick, not a guarantee — it is wrong for data in which the first data line is entirely strings.

Third, you must not cut by hand. line.split(",") does not know about quoting. Python's csv module handles delimiters and line breaks inside quotes and quotation marks written as two. And you must give newline="" when opening the file — it is a requirement the documentation states explicitly, and if you leave it out, a line break inside quotes cuts the record.

원본 한 레코드
  A-1002,"달빛상사","고객 요청:
  오전 배송",1,12000

split(",") 의 눈에는          csv 모듈의 눈에는
  줄 2개 · 필드 3개와 3개       레코드 1개 · 필드 5개

What it looks like in the field

First, an unclosed quote. If you leave out one quote in some record, from that point to the end of the file becomes one field. The parser raises no error, and the result looks like "just one last case". You can tell this for sure by scanning once to see whether the file ended in a quoted state — count the quotes but skip the ones written as two ("").

Second, Excel changes it. If a person opens it in Excel and saves, the delimiter becomes a semicolon according to the regional settings, long numbers become exponent notation, and leading zeros vanish. So the "original file" must be the one from before a person opened it.

Third, you just throw away the broken lines. If you quietly skip a line whose column count does not match, sales vanish by that many cases, and later you have no grounds to explain. Lines that cannot be used should be set aside into a reject file with the original line number written alongside. That way the customer can open that line in their own file.

Fourth, reject the whole file or only drop the line. In a place like a bank receiving file, where the counterparty's total and our ledger must match, even one mismatched line sends the entire file back. Conversely, analysis data the customer gave you has nowhere to be sent back — you use the lines that can be used and set aside only those that cannot. Which is right is decided not by the data but by the business.

What really matters in practice

Report the line count and the record count together. If the two differ, the difference itself is information.
Do not cut by hand. Use a standard parser, and give newline="" when opening the file.
Record the result of detecting the delimiter, the line ending, and the header. If it changes in the next delivery, that record becomes the grounds.
Set aside rather than discard. Leave the reject file, the reasons, and the original line numbers.

What you will do in the next lab

You build a feed yourself that exports the same 20 orders in seven shapes, and then grow a gatekeeper, csvgate.py, one step at a time. First you produce the answer of a parser that cuts by hand, and put it side by side with the answer of the csv module to see in numbers how far they diverge. Then you detect the line ending, the delimiter, and the presence of a header, and set aside into a reject file the lines with a mismatched column count, the empty lines, and the unclosed quotes. The grader makes its own feed with different company names and amounts each time, actually runs your gatekeeper, and checks the answers.