TT Lab
Get started
Learn Learning paths Courses

Working With Customer Data

The Row Count Is Off: Count Records, Not Lines

Continue in TT Lab

Goal

You build csvgate.py, a gatekeeper that counts a customer CSV by records, not lines. You measure with your own data how far the answers of a hand-cut parser and the standard parser diverge, detect the delimiter, the line ending, and the presence of a header, and set aside lines that cannot be used into a reject file.

Why it matters

In CSV, a line and a record are different things. If an order memo has a line break, one record occupies two lines in the file, and if a company name has a comma, a column is added when you cut that line at commas. In both cases the parser raises no error and produces a plausible number. That is why this mistake survives until the customer looks at the number and says it is strange. RFC 4180 sets the quoting rules and the line ending, but files that follow the specification perfectly are not the only ones that come. Files come with a semicolon or tab delimiter, line endings mixed within one file, and no header. Whether there is a header is not written in the file, so you have to detect it. And what remains is how to handle the lines that cannot be used. If you quietly skip them, sales vanish by that many cases and there are no grounds to explain with. For a bank receiving file, even one mismatched line sends the file back in its entirety, but 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. The grader does not trust your wording. It sets up a feed it made in a temporary directory, actually runs your gatekeeper, and checks the record count, the amount total, and the reject reasons against the values it counted itself. The company names and amounts change on every run.

Steps

  1. Create and run /root/csv/gen_feed.py to produce seven files under /root/csv/feed. The same 20 orders come out in seven shapes.
  2. Build naive in /root/csv/csvgate.py so that it outputs the answer (rows, amount_total, bad_field_count) of a parser that believes one line is one record.
  3. Add parse so that it outputs the answer counted correctly with the csv module (records, amount_total), and write the difference between the two answers in /root/csv/gap.json.
  4. Add sniff so that it detects the line ending as CRLF, LF, or MIXED.
  5. Make sniff detect the delimiter from among the candidates (comma, semicolon, tab, pipe).
  6. Make sniff detect whether there is a header (header) and the number of columns (fields).
  7. Add check so that it counts the unusable lines by reason and sets them aside into a reject file. The reject file is written under rejects/ in the directory where the input file is, with the same name.
  8. Process all seven feed files in one go to produce /root/csv/feed_report.json and /root/csv/feed_report.md.

Notes

Build the same feed in seven shapes

Create and run /root/csv/gen_feed.py to produce seven files under /root/csv/feed. The same 20 orders come out differing only in delimiter, line ending, header, and whether they are damaged.

If you write with the writer of Python's csv module, the quoting is taken care of. Change lineterminator to choose the line ending and delimiter to choose the delimiter. For the files you deliberately wreck (a line with a different column count, an unclosed quote), do not use the writer; write them directly as strings.

Produce the answer of a hand-cut parser

Build naive <파일> (the placeholder is the file) in /root/csv/csvgate.py so that it outputs the answer of a parser that believes one line is one record, as JSON. It has three values: rows, amount_total, and bad_field_count.

This step builds a deliberately wrong parser. Read the whole file, split it on newlines, skip the first line and the empty lines, and cut each line at commas. If the columns are not 5, do not add the amount but count it only in bad_field_count. You will put it side by side with the correctly counted answer later, so follow the rules exactly.

Read commas and line breaks inside quotes correctly

Add parse <파일> (the placeholder is the file) so that it outputs the answer counted with the csv module (records, amount_total), and write the difference between the two answers for orders_rfc.csv in /root/csv/gap.json as file, naive_rows, csv_records, naive_amount, and csv_amount.

The key is to give newline="" when opening the file. csv.reader handles delimiters and line breaks inside quotes and quotation marks written as two. What remains after removing the header line is the records, and the amount is added only from records whose column count is right.

Pick out a file with mixed line endings

Add sniff <파일> (the placeholder is the file) so that it detects the line ending. If it is only CRLF it is CRLF, if only LF it is LF, and if the two are mixed it is MIXED. The response must have path, delimiter, and newline.

Count line endings by bytes. Read the file in binary, count the CRLFs, and subtract that many from the total number of LFs to get the number of stand-alone LFs. If neither is 0, they are mixed. In this step you may leave the delimiter as a comma.

Choose the delimiter from among the candidates

Make sniff detect the delimiter from among the four candidates: comma, semicolon, tab, and pipe. For each candidate, parse the whole file and choose the one whose column count is the most evenly wide.

If you count by looking only at the first line, a delimiter inside quotes fools you. For each candidate, parse the whole thing with csv.reader, find the mode of the column count and the proportion of lines that have that mode, and give a score of 0 to a candidate that yields only one column. This method holds up even on a file where the column count differs line by line.

Detect whether there is a header

Add fields (the number of columns) and header (whether there is a header) to the sniff response. If the first line has no cell that reads as a number and the three lines that follow do, treat it as a header.

Whether there is a header is not written in the file. RFC 4180 too only says to signal it with a parameter of the media type. So it is an estimate, not a detection, and an estimate needs a yardstick. If you write the yardstick in the code, then when it is wrong in the next delivery you can see what to fix.

Set aside only the unusable lines

Add check <파일> (the placeholder is the file) so that it checks each record, counts the empty lines, column-count mismatches, and unclosed quotes by reason, and sets them aside into a reject file. The reject file is written under rejects/ in the directory where the input file is, with the same name. Add unterminated to the sniff response.

An unclosed quote raises no error. From that point to the end of the file becomes one field and just looks like 'one last case'. You can tell for sure by scanning the file once to see whether it ended in a quoted state — quotes written as two must be skipped. On each rejected line, also write which record it originally was.

Report on one sheet for the feed

Process all seven feed files in one go and write files, accepted, rejected, and amount_total in /root/csv/feed_report.json, and write /root/csv/feed_report.md in four sections: ## 무엇을 받았나 ## 줄과 레코드는 다르다 ## 떼어 낸 줄 ## 보내는 쪽에 요청할 것 (the Korean headings mean "What we received", "A line and a record are different", "The lines set aside", and "What to request from the sender").

files is an object keyed by file name that holds delimiter, newline, header, records, accepted, rejected, and amount_total. In the report, write the number of set-aside lines as a number — the conversation goes faster if the customer can open that line in their own file. You can simply call the functions you built in the earlier steps.