banner

For a full list of BASHing data blog posts see the index page.  RSS

CSV viewers for CSV haters

There are quite a few programs that let us CLI people view a CSV file as a simple table. I doubt if there any CSV parsers that work perfectly with all variations in the wild of the horrible, awful CSV format, but the two parsers shown here are usually reliable.

The first CSV example, "frodo1.csv", is based on the following address:

Frodo, the Ringbearer   "Bag End"   Bagshot Row   Hobbiton   The Shire

There are three different ways to "validly" format this address as a CSV:

Name,House,Street,Town,Region
"Frodo, the Ringbearer","""Bag End""",Bagshot Row,Hobbiton,The Shire
"Frodo, the Ringbearer","""Bag End""","Bagshot Row",Hobbiton,"The Shire"
"Frodo, the Ringbearer","""Bag End""","Bagshot Row","Hobbiton","The Shire"

In all three versions the Name entry has to be quoted, because it contains a comma. The House entry "Bag End" has to be quoted twice, because the entry would look like a double-quoted field to a CSV parser, and RFC4180 says If double-quotes are used to enclose fields, then a double-quote appearing inside a field must be escaped by preceding it with another double quote.

Address version 2 also quotes entries containing spaces, and version 3 quotes all entries.

Here "frodo1.csv" is viewed using csvlook from the csvkit of utilities [Python], and csview [Rust] with and without "sharp" border styling:

csv1

The file "frodo2.csv" uses semicolons instead of commas as field separators. Notice that this time the first address version doesn't need the Name entry to be quoted:

Name;House;Street;Town;Region
Frodo, the Ringbearer;"""Bag End""";Bagshot Row;Hobbiton;The Shire
"Frodo, the Ringbearer";"""Bag End""";"Bagshot Row";Hobbiton;"The Shire"
"Frodo, the Ringbearer";"""Bag End""";"Bagshot Row";"Hobbiton";"The Shire"

csvlook correctly recognises the semicolons as field separators, while csview needs the -d ";" option:

csv2

Both csvlook and csview will wrap long lines in the terminal. The outputs from both can be piped to less -S, and the full, non-wrapped line can then be viewed with the right and left arrow keys. A better terminal pager for this job is most, which moves right and left with the arrow keys and shows a "$" at the right-hand side to indicate that the line is truncated in the viewer. Here the middle of "wide.csv" is being viewed from csvlook wide.csv | most after some right-arrowing (in a terminal window made narrow for the screenshots):

csv3

If you don't mind leaving the terminal briefly to view a CSV, I recommend tad. The tad GUI can be invoked from the command line with tad [filename] and the program is something of a work-in-progress, but like csvlook it automatically recognises field separators other than commas. tad also has a very handy feature: click on the gear icon at the left of the main screen and you can select/deselect the individual CSV fields you want to view:

csv4
csv5

tad has some other interesting features, but if you want to edit a CSV in a GUI program (rather than just view the CSV), you should probably open the CSV in a good text editor or in Modern CSV.

And xsv? The command-line tool xsv is nice for operating on CSVs, but it doesn't display them as neatly as csvlook and csview do:

csv6

Since my operating recipes are mainly based on TSVs, I normally convert CSVs to TSVs with csvformat -T. In horrible, awful cases I try to open and repair the CSV in Gnumeric spreadsheet or LibreOffice Calc, then copy/paste the cells as a TSV.

Last update: 2021-08-18
The blog posts on this website are licensed under a
Creative Commons Attribution-NonCommercial 4.0 International License