unix 1 liner to process a CSV
Recently I needed to inspect a CSV. I could open it in a spreadsheet and try hiding columns, or write a quick unix 1 liner. The one-liner is shown below in a code block.
cat out.csv | tac | cut -d , -f 3,5 | less
This one-liner uses four different commands, cat, tac, cut, and less, which each modify the data in a specific way. Lets go through them one by one.
cat out.csv reads the file out.csv. The real file was larger, but here’s a small example
A,B,C,D,E,F
197,198,X,45.07,5418377,0.0006699515234523945
196,197,Y,45.07,8791092,0.0010869685660872541
198,199,Z,42.99,30896590,0.001540929478413
194,195,A1,52.5,598682,0.0016263458559409028
195,196,B1,47.72,18092026,0.006921275128414672
193,194,C1,52.68,2773168,0.008094877815466361
178,179,D1,63.42,103698,0.017121157657426167
192,193,E1,52.69,7046310,0.0206503955280557
175,176,F1,66.03,73040,0.029825968985690565
tac is the opposite of cat, it prints the input in reverse order line by line.
175,176,F1,66.03,73040,0.029825968985690565
192,193,E1,52.69,7046310,0.0206503955280557
178,179,D1,63.42,103698,0.017121157657426167
193,194,C1,52.68,2773168,0.008094877815466361
195,196,B1,47.72,18092026,0.006921275128414672
194,195,A1,52.5,598682,0.0016263458559409028
198,199,Z,42.99,30896590,0.001540929478413
196,197,Y,45.07,8791092,0.0010869685660872541
197,198,X,45.07,5418377,0.0006699515234523945
A,B,C,D,E,F
cut -d , -f 3,5 uses comma as a delimiter and selects the third and fifth columns.
F1,73040
E1,7046310
D1,103698
C1,2773168
B1,18092026
A1,598682
Z,30896590
Y,8791092
X,5418377
C,E
less displays the output page by page on the terminal, helpful for big files as it allows browsing through the output with arrow keys. Since less is typically an interactive shell command primarily intended for wired terminal use, there is no way to represent its output in static text form. However, this command is useful when you’re processing a large amount of data and wish to peruse it at a pace suited to you.