CSV surgery, without opening a spreadsheet SPLIT

What a spreadsheet does to your data without telling you

Open a CSV in a spreadsheet and it starts guessing. An identifier like 007 becomes the number 7, because a leading zero is meaningless in a number and it decided this was a number. A postcode, a part code, a bank sort code: same. 3-4 becomes the third of April. SEPT1 becomes a date. A sixteen-digit account number becomes 1.23457E+15, and the last digits are not hidden by the display; they are gone, because it stored a float.

None of this is announced. You open the file, do one thing, save it, and hand on a file that is quietly different from the one you received. It is the single most common way data gets damaged in ordinary offices, and the damage is usually discovered by somebody else, later.

So nothing here converts anything. The operations run on text, and a value that went in as 007 comes out as 007. There is no type inference because there is no reason to have any: none of these jobs needs to know what a field means.

A real parser, because the naive one ruins real files

Splitting a line on commas works until the data arrives, and then it fails on three things that are in more or less every export: a quoted field containing a comma ("Smith, John"), a quoted field containing a newline (any address, any free-text comment), and a doubled quote inside a quoted field, which is how a quotation mark is escaped.

A naive parser does not report an error on any of them. It shifts the columns from that row onwards, or ends the record in the middle of an address, and the result is a file that still opens and is wrong in the middle. The parser here handles all three, which is not clever; it is the baseline, and it is worth saying because so many tools skip it.

Every operation says what it did

Take out the duplicates, keep only the rows where a column says something, drop the columns nobody needs, break one file into one file per value of a column. Each reports its count: how many rows went in, how many came out, how many were removed.

That number is the check. “Removed 1,204 duplicates” against an expectation of about forty is a finding about your data: most likely you deduplicated on the wrong column, or an export ran twice. A filter that keeps zero rows means the value you matched on is not spelled the way you think it is, which you would otherwise discover after sending an empty file to somebody. Without the counts you find out later, or never.

The splitting is the one people come for: one file per region, per client, per month, without writing a script or a macro. The files are written here and downloaded; nothing is uploaded, which matters because the CSVs people need to cut up are customer lists, payroll extracts and patient exports.

Nearby: DATASCAN finds the personal data hiding in a spreadsheet before it goes out, CLEANSHEET tidies the values themselves, RECONCILE matches two files against each other, and BASE is the answer when the file has outgrown being a file.