csv

Comma-Separated Values, RFC 4180 — machines/csv.shoddy

Summary

csv reads and writes the spreadsheet format: rows of cells with commas between them. Each line of the file is one record — one row of the table. That sounds like a job for str's Split, and for a file you wrote yourself it very nearly is. This machine exists for everything Split cannot do. A cell may legally contain a comma, a quotation mark, or even a line break. All three appear the moment a file comes out of Excel or off the internet. Any one of them turns a hand-rolled splitter into a program that quietly reads the wrong data.

A document here is the plain thing it looks like: a List Of List Of String. So seq's Map, Filter and Fold work on it with nothing new to learn. Two more layers sit above that, and you can take or leave them. A sheet treats the first record as a header — the row of column names — and lets you name columns instead of counting them. A type goes further: you hand over a function from cells to your own record, and you get a list of your own records back.

A Brief History of the Comma

CSV is unusual among file formats: it was in constant use for about thirty years before anyone wrote down what it was. Comma-separated input was already in IBM's Fortran compilers by 1972, as the easy way to type numbers into a program. When the microcomputer spreadsheets arrived, they adopted the same habit for getting tables in and out. Every program implemented what it guessed the rules were. That is why the world is full of files that almost read correctly. The quoting rules were folklore, passed from implementation to implementation: a cell with a comma in it goes in quotes, a quote inside a quoted cell is doubled, and a quoted cell may contain a line break. In 2005, RFC 4180 finally wrote the folklore down. (An RFC is one of the numbered documents that record internet standards.) It invented nothing. It just described, at last, what the survivors had converged on. That is the spec this machine reads and writes, and the reason it exists at all: the rules are simple, but they are precisely the rules a hand-rolled Split does not know.

Why It's Useful

CSV is the format everything can already read and write: every spreadsheet, every database export, every statistics package. That makes it the way a Shoddy program joins a workflow it did not start. Reading is the common half — somebody sends you a file and you want the third column as numbers. Writing matters just as much, and it is the half people get wrong. Put a name like ADA, COUNTESS OF LOVELACE into a file without quoting it, and you have silently added a column.

The escaping rules — the rules for writing a special character so it reads as data, not as punctuation — are small enough to state completely. A quoted cell may hold anything. A doubled "" inside one is a single literal quote. That is all of it: CSV has no backslash anywhere. But these are not rules you want to rediscover in the middle of a program about something else. That is the trade this machine makes on your behalf: fifteen lines of scanner, so that the twenty places you touch a data file are one word each.

It sits beside json and xml rather than under them. Those two carry trees; this one carries a table, and a table is what a measurement usually is. json's objects are dict.shoddy association lists — lists of name-and-value pairs. A csv row zipped with its header is the same association list. So DictGet works on both, and dict is the seam they share.

User's Guide

The plainest use is the whole file as rows of strings:

Include "str.shoddy"
Include "csv.shoddy"

Def Main()
    Let rows = CsvLoad("grades.csv")
    Each(rows, Fn(r) => Print(Join(r, " | ")))
    CsvSave("reversed.csv", Map(rows, Reverse))

With a header line, stop counting columns and name them. A CsvSheet is the header and the records under it:

Let sh = CsvSheetLoad("grades.csv")
Print(Join(CsvHeads(sh), ", "))                  ' NAME, HOURS, SCORE
Each(CsvBody(sh), Fn(r) =>
    Print(CsvGet(sh, r, "NAME") & " scored " & CsvGet(sh, r, "SCORE")))

Print(Str(Sum(CsvColumnNums(sh, "SCORE"))))      ' needs stats.shoddy
Each(CsvWhere(sh, "GRADE", "A"), Print)          ' the WHERE clause, near enough

With a Type, the machine never learns what your record is. You pass the two functions that know, and everything else is Map over one of them:

Type Student
    Name  As String
    Score As Number

Def StudentOf(cells As List Of String) As Student
    Student(Nth(cells, 1), Val(Nth(cells, 2)))

Def StudentCells(s As Student) As List Of String
    { Name(s), Str(Score(s)) }

Let class = CsvLoadRecs("class.csv", StudentOf)
CsvSaveRecs("class.csv", class, StudentCells)

A few things worth knowing before you point it at somebody else's file:

The reader is total; the parser aborts. Total means it always returns an answer instead of stopping the program. CsvRead returns CsvOk or CsvErr, and the error carries the reason and the offset it gave up at — the position in the text, counted from 1. CsvParse is the same thing with the failure turned into an Error. Shoddy has no catchable errors, so reach for CsvRead the moment the input stops being yours. This is the same guard-pair convention as Val/ValOr and json's reader.
Ragged rows are not an error. Ragged means the rows are not all the same length. Files in the wild are ragged, and a reader that refused them would be useless for the job you actually have. CsvIsRect asks, CsvWidth measures, and CsvRect pads every row out to the widest.
Dialects, not flags. A dialect is a small value that says how a file is punctuated. Every reader and writer comes in two spellings — CsvParse and CsvParseAs, CsvText and CsvTextAs — and the plain one is the -As one applied to CsvComma(). Tabs, semicolons, CRLF records (CRLF is the two-character Windows line ending), trimming and # comment lines are all dialects. The two combinators CsvTrimming and CsvCommented each wrap a dialect to make a new one, and they stack.
Compare with CsvEqual, not with =. A List is a quotation and compares by identity: = asks whether two lists are the same object, not whether they hold the same cells. So two structurally identical documents are never = each other. This is the same trap json's JEqual exists for, and the same answer.
Quoting on the way out is minimal, plus one rule of our own. A cell is quoted when it holds the separator, a quote, a CR or an LF (the two line-ending characters). It is also quoted when it has a leading or trailing space, which RFC 4180 does not ask for. That extra rule keeps a round trip exact under CsvTrimming as well. Without it the trimmer would eat spaces the writer meant to keep.
A blank line is a record. RFC 4180 says so, and this reader agrees: a blank line is one empty cell. The single line ending at the very end of a file is the last record's terminator and does not start an empty one. CsvDropBlanks is there when you would rather they were gone.

Word Reference

Every word this machine exports

Reading

Word	Description
CsvRead(s)	The total reader: `CsvOk(rows)` or `CsvErr(why, at)`, the offset being 1-based into the text. The one to use on input you did not write.
CsvReadAs(s, d)	The same, in dialect `d`.
CsvParse(s)	The rows, aborting with `Error` on a malformed document. The strict half of the pair.
CsvParseAs(s, d)	The same, in dialect `d`.
CsvLoad(path)	`CsvParse` of a file's whole text.
CsvLoadAs(path, d)	The same, in dialect `d`.
CsvNoBom(s)	Text with a leading byte-order mark removed — the invisible marker character some programs put at the start of a file. It strips both the three-character UTF-8 spelling and a bare U+FEFF. The readers call it for you. It is exported because a header that silently begins with an invisible character is worth being able to strip anywhere.

Dialects

Word	Description
CsvComma()	The default: comma separated, quote character `"`, LF between records, no trimming, no comments.
CsvRfc()	RFC 4180 to the letter — the same, but the writer puts CRLF between records.
CsvTabs()	Tab separated: TSV.
CsvSemis()	Semicolon separated — the European spreadsheet spelling, where the comma is the decimal point.
CsvSepBy(c)	Any single character as the separator. Aborts on a separator of the wrong length, or on one the grammar already spends (a quote, CR or LF).
CsvTrimming(d)	Dialect `d`, with spaces around unquoted cells dropped. Quoted cells are never touched — quoting is how a file says "these spaces are mine".
CsvCommented(d, prefix)	Dialect `d`, with whole records beginning `prefix` skipped. Not part of CSV; part of every hand-kept data file there has ever been.
CsvNumeric()	The dialect the repo's own `.dat` files are in: comma separated, spaces ignored, `#` for a comment.
CsvQt() CsvNl() CsvCr() CsvTab()	The quote, line feed, carriage return and tab characters, as zero-argument words — a machine cannot carry a top-level `Let`.

Writing

Word	Description
CsvText(rows)	The whole document as text, every record followed by the line ending, the last one included.
CsvTextAs(rows, d)	The same, in dialect `d`.
CsvSave(path, rows) / CsvSaveAs(path, rows, d)	Write it to a file.
CsvAppend(path, rows) / CsvAppendAs(path, rows, d)	Add records to the end of an existing file.
CsvLine(cells) / CsvLineAs(cells, d)	One record, without its line ending — for printing, or for appending by hand.
CsvCellOf(t) / CsvCellAs(t, d)	One cell, quoted only if it has to be.
CsvNeedsQuote(t, d)	Whether it has to be: the separator, a quote, a CR, an LF, or an outer space.
CsvQuoteCell(t, d)	Quoted whether it needs it or not, inner quotes doubled.

Shape

Word	Description
CsvWidth(rows)	The width of the widest row.
CsvIsRect(rows)	Whether every row is that wide.
CsvRect(rows)	Every row padded with empty cells until it is.
CsvPadRow(row, n)	One row padded to `n` cells.
CsvDropBlanks(rows)	Rows with nothing in them removed — the blank lines RFC 4180 says are records.
CsvFlip(rows)	The transpose: columns become rows.
CsvEqual(a, b) / CsvRowEqual(a, b)	Deep comparison. Use these and not `=`: a List compares by identity, so `=` on two identical documents is always False.

Sheets — a header and the records under it

Word	Description
CsvSheetOf(rows)	First record as the header, the rest as the body. An empty document gives an empty sheet.
CsvSheetRead(s) / CsvSheetReadAs(s, d)	Parse text straight into a sheet.
CsvSheetLoad(path) / CsvSheetLoadAs(path, d)	The same, from a file.
CsvHeads(sh) / CsvBody(sh)	The column names, and the records.
CsvSheetRows(sh)	Back to rows, header first — the inverse of `CsvSheetOf`.
CsvSheetText(sh) / CsvSheetTextAs(sh, d)	The sheet as text, header included.
CsvSheetSave(path, sh) / CsvSheetSaveAs(path, sh, d)	Write it out.
CsvIndexOf(sh, head)	Which column that is, 1-based; 0 if there is no such column.
CsvHas(sh, head)	Whether there is.
CsvCount(sh)	How many records, not counting the header.
CsvGet(sh, row, head)	The cell under a named column. Aborts on a column that is not there, or a row too short to reach it — a file whose header promises SCORE and whose rows do not have one is a file you want to hear about.
CsvGetOr(sh, row, head, dflt)	The total half: the default instead of the abort.
CsvNum / CsvNumOr / CsvBool / CsvBoolOr	The same cell read as a number or a boolean, strict and total. The boolean forms take the whole prompt vocabulary — TRUE/T/YES/Y/1 and their opposites — through str's `ToBoolOr`.
CsvColumn(sh, head)	One whole column, top to bottom.
CsvColumnNums(sh, head)	The same, as numbers.
CsvWhere(sh, head, v)	The records whose named column holds `v`.
CsvPairs(sh, row)	The header zipped with a row — an association list, so every dict word works on it unchanged.
CsvDicts(sh)	One of those per record.
CsvRowOfDict(heads, d)	And back again: a dictionary laid out under a header, missing keys empty.
CsvSheetOfDicts(heads, ds)	A whole sheet from a list of them.

Types — a file of records, and records back to a file

Word	Description
CsvRecs(s, rowOf) / CsvRecsAs(s, d, rowOf)	Parse, then turn each row into your record with the function you supply. The type is yours; this machine never sees it.
CsvLoadRecs(path, rowOf) / CsvLoadRecsAs(path, d, rowOf)	The same, from a file.
CsvSheetRecs(sh, rowOf)	The same over a sheet's body, so the header is dropped. Pass a closure over the sheet to name columns instead of counting them.
CsvFromRecs(xs, cellsOf) / CsvFromRecsAs(xs, d, cellsOf)	The inverse: your records to text.
CsvSaveRecs(path, xs, cellsOf) / CsvSaveRecsAs(...)	Straight to a file.
CsvSheetOfRecs(heads, xs, cellsOf)	Your records as a sheet under a header you name.
CsvSaveSheetRecs(path, heads, xs, cellsOf)	And that written out, header line included — the spelling you want for a file anything else is going to read.

Numbers

Word	Description
CsvNums(rows)	Every cell read as a number.
CsvLoadNums(path)	A file of numbers in `CsvNumeric()` — comma separated, `#` comments, spaces ignored, blank lines dropped — as a list of lists of numbers. The column-picking loader every mill was growing for itself.
CsvFromNums(rows) / CsvSaveNums(path, rows)	And numbers back out again.

Who Uses It

	User	How
	tally	`CsvSheetOf` and `CsvParseAs` read the data file its spec names, and the whole pipeline — derive, filter, report — is `CsvSheet` in and `CsvSheet` out.

The Machines It Uses

	Machine	Why
	dict	A row zipped with its header IS an association list, so `CsvPairs` hands one over and every dict word works on it unchanged — the same arrangement json makes for object members.
	seq	`Zip` pairs a row with its header, `IndexOf` finds a named column, and `All` and `Append` carry the scanner.
	str	`Join`, `Replace`, `Trim` and `StartsWith` under the scanner and the writer; `ToBool` and `ToBoolOr` under the sheet's boolean accessors.