CSV Files

Introduction to working with CSV files.

Part of Python · page 4 of 4

CSV Tutorial

A table is two-dimensional. It has rows going down and columns going across, and we find a value by picking one of each ie - row 2, column3.

A computer file is one-dimensional. It is a single long sequence of characters, one after another, with no built-in notion of what "across" or "down" means. If you could see a file as it truly is, it would look like a very long ribbon of text.

So, how do we fit a two-dimensional table onto a one-dimensional ribbon?

Separators

We agree on two special caracters, where one means "this column has ended, the next one starts here.". The other means "This row has ended, the next one starts here now".

For CSV, the choices are a comma for columns, and a line break for rows. Apply that to the table above and you get this:

order_id,region,rep,units
1001,North,A. Patel,12
1002,South,J. Weber,4
1003,East,M. Oda,31

That's it. CSV means Comma Separated Values.

Delimiter

The character chosen to mark the boundary between one value and the next. In a CSV file. the delimiter is normally a comma.

"Delimit" just means "to mark the limits of". A delimiter marks where a value just stops.

Notice what the file does not contain. There are no column widths, no borders, bold text, cell colours, formulas.... The grid is drawn by a browser based on where the commas fall. The file itself holds only the values and the separators.

The Header Row

The first line of the file is different in kind from the others. The header row is an optional first line of a CSV file that names each column rather than holding data. Almost every CSV we meet is gonna have one, but nothing in the file marks it as special so just know lol.

The Comma Problem

What happens when a value contains a comma? The solution is to wrap any value containing a delimiter in double quotes:

"Wellington, New Zealand",4,24.50

A comma inside quotes is part of the value; a comma outside the quotes is a delimiter.

What happens when a value contains an actual double quote?

Double it up:

"Customer said ""urgent""",4

This looks weird af but the rule underneat is simple.

The quoted value can contain anything, it can contain a line break too. This is legal and it happens whenever someone types a multi-line note into a spreadsheet cell:

1005,South,"customer asked:
call back Monday",7

Now this is one row occupying two lines of the file.

The consequence of this is that we cannot count the rows in a CSV file by counting the lines. A file with 500 lines might hold only 480 records. Any tool that counts lines including the standard CLI ones will give us the wrong answer on files like this.

What Is Parsing?

To read a file correctly, something has to track whether it's currently inside quotes, treat commas differently depending on the answer, recognise double quotes and understand that line breaks might or might not end the row.

That work - reading a stream of characters and working out its structure is called parsing

Parsing

Reading text and working out what it means structurally: which characters are data, and which are punctions doing a job.

A parser is a piece of code that does this. Parsing a CSV file turns a ribbon of charaters into a proper list of rows and values.

Python includes a parser, and it is fast af. It handles every case above correctly.

Difference between CSV and Excel

People often treat CSV and Excel as interchangeable, because Excel opens CSV files happily. They are not the same thing at all. Saving an Excel spreadsheet as a CSV discards most of what a spreadsheet is:

CSV is poor at expressing anything beyond a plain grid of values, and outstanding at surviving. An Excel file needs software that understands a complex versioned format. A CSV file needs something that can read text, which is everything.

"No column data types"

In a spreadsheet, a cell knows whast it holds. A number cell holds a date nwhich is why you can sort it chronologically and add seven days to it.

A CSV file has none of that. Every value in a CSV file is text. Always. The characters 12 are stored the same way as the characters North - as characters. The file does not record that one is a quantity and the other is a name.

So a program reading a CCSV gets text, and if it needs a number it has to convert. Skip that step and you get results like this:

Is the text "12" greater than the text "9"? No Is the number 12 greater than the number 9? Yes

Compared as text, "12" comes before "9: for the same reason apple comes before bananaL A comparison starts with the first character, and 1 sorts before 9. It doesn't look any furtuer. This is behind a surprising share of "Why is my data sorted wrong".

RFC 4180

This is Request for Comments and its a numbered technical document published bty bodies that maintain internet stadnards. 4180 was published in 2005, is the one that attempted to write down the riles of CSV - Delimiter, quoting, double quote marks.

CSV had been in use every day since 1970s. By the time anyone wrote the rules down, 30 years of files and software already existed, all doing slightly different things. RFC 4180 therefore describes common practice instead of commanding rules. Nothing can oblige a program to follow it.

This means that I should treat a CSV file as a format to check rather than to assume. It probably ujses commas, quotes the way we described, but it also might not do any of this things.

Recap

• A file is a one-dimensional ribbon of characters. CSV fits a two-dimensional table onto it using two separators: a delimiter between columns, a line break between rows.

*The delimiter is usually a comma, but semicolons, tabs and pipes are all common.

*The first line is usually a header row naming the columns — but nothing in the file says so.

• Values containing the delimiter are wrapped in double quotes. Quote characters inside those are doubled up. Quoted values may contain line breaks, so one row can span several lines.

• Parsing is working out structure from text. Splitting on commas is not parsing and will corrupt real data. CSV stores values and nothing else — no formulas, no formatting, no types. Every value is text.

• RFC 4180 describes the usual conventions but doesn't enforce them.