Five Commands to Inspect CSV Files

CSV files have a funny reputation.

They look like spreadsheets, so we often reach for a spreadsheet application when we encounter one. But underneath the rows and columns is something much simpler: text.

And once you remember that, the Unix toolbox becomes surprisingly useful.

Suppose we have a file called people.csv:

name,department,city,salary
Anotida,Engineering,Harare,85000
Bekithemba,Marketing,Bulawayo,62000
Carol,Engineering,Gweru,79000
Dadirai,Sales,Harare,71000
Eve,Marketing,Mutare,65000

We could open this in a spreadsheet. But if our goal is simply to inspect the data, there is often no need.

Here are five commands worth having in your Textsmith toolbox.

1. cut — Pick the columns you need

The cut command is excellent when you want to extract particular fields.

If our CSV has five columns and we want just the names:

cut -d, -f1 people.csv

The -d, tells cut that the delimiter is a comma, while -f1 selects the first field.

Want the names and cities?

cut -d, -f1,3 people.csv

Or perhaps just the department:

cut -d, -f2 people.csv

This is one of those commands where the Unix philosophy becomes obvious: don’t process what you don’t need.

If all you need is one column, extract one column.

A useful variation

If the file uses tabs rather than commas, you can simply change the delimiter:

cut -d$'\t' -f1 data.tsv

The command doesn’t particularly care whether you call the file a spreadsheet, database export, or report. It sees fields separated by a character.

2. column — Make text look like a table

Sometimes you don’t want to extract anything. You simply want to see the structure.

That’s where column shines.

column -s, -t people.csv

The -s, specifies the comma as the separator, while -t formats the input into aligned columns.

The result becomes much easier to inspect:

name   department   city      salary
Anotida  Engineering  Harare    85000
Bekithemba    Marketing    Bulawayo  62000
Carol  Engineering  Gweru     79000
Dadirai  Sales        Harare    71000
Eve    Marketing    Mutare    65000

There is something satisfying about this command.

The data remains plain text. We haven’t converted it into a proprietary spreadsheet format. We have simply changed the presentation.

That is very much the Textsmith way:

Transform the representation without imprisoning the information.

3. paste — Put columns back together

cut takes columns apart. paste can put them together again.

Suppose we have two files:

names.txt

Anotida
Bekithemba
Carol
Dadirai
Eve

and:

cities.txt

Harare
Bulawayo
Gweru
Harare
Mutare

We can combine them into two columns:

paste -d, names.txt cities.txt

giving:

Anotida,Harare
Bekithemba,Bulawayo
Carol,Gweru
Dadirai,Harare
Eve,Mutare

This becomes particularly interesting when combined with other commands.

For example:

cut -d, -f1 people.csv > names.txt
cut -d, -f3 people.csv > cities.txt
paste -d, names.txt cities.txt

We have decomposed a file into streams and then recombined those streams.

That is classic Unix thinking.

4. sort — Put the data in an order

Once we have extracted a field, sort can help us understand it.

For example, to see the cities alphabetically:

cut -d, -f3 people.csv | sort

Result:

Bulawayo
Gweru
Harare
Harare
Mutare

Or sort the complete CSV by its first field:

sort -t, -k1 people.csv

The -t, tells sort that the fields are comma-separated, while -k1 tells it to sort using the first field.

For numerical values, use numeric sorting:

sort -t, -k4,4n people.csv

The n means numerical rather than lexicographical sorting.

This distinction matters.

Text sorting sees:

100
20
3

as text.

Numeric sorting understands them as numbers:

3
20
100

The difference is small, but it is exactly the kind of detail that makes command-line text processing powerful.

5. uniq — Find repeated values

Finally, uniq is useful when we want to discover duplicates.

For example, which cities occur in our file?

cut -d, -f3 people.csv | sort | uniq

We get:

Bulawayo
Gweru
Harare
Mutare

Notice the sort before uniq.

This is important because uniq removes adjacent duplicate lines. It isn’t a general-purpose duplicate detector.

So:

sort | uniq

is one of those classic Unix combinations worth remembering.

And there is an even more useful variation:

cut -d, -f3 people.csv | sort | uniq -c

Now we can count occurrences:

      1 Bulawayo
      1 Gweru
      2 Harare
      1 Mutare

Suddenly we have performed a tiny bit of data analysis without opening a spreadsheet.

Bonus: awk — When the Pipeline Becomes a Program

And then there is awk.

If you have ever looked at these examples and thought:

“Couldn’t I just do all of that with awk?”

Yes.

That’s precisely why awk deserves the bonus section.

For example, to print the name and city:

awk -F, '{print $1, $3}' people.csv

Here -F, specifies the field separator.

We can filter rows too:

awk -F, '$2 == "Engineering" {print $1, $3}' people.csv

Now we are asking for the names and cities of people in Engineering.

We can even perform calculations:

awk -F, 'NR > 1 {sum += $4; count++} END {print sum / count}' people.csv

That calculates the average salary while skipping the header row.

At this point, awk is no longer merely helping us inspect the CSV. We are writing a small data-processing program.

And that illustrates an important distinction.

The Unix toolbox gives us many small commands:

cut
column
paste
sort
uniq

Each does one particular thing.

awk, on the other hand, is a language for describing transformations over structured text.

So there is no competition between the approaches.

Sometimes this is beautifully readable:

cut -d, -f3 people.csv | sort | uniq -c

And sometimes a single awk program is the better tool.

The Textsmith learns to recognise both.

One Important CSV Warning

There is one caveat worth putting in bold whenever we talk about CSV.

Not every CSV file is as simple as the example above.

Consider:

name,description,city
Anotida,"Engineer, Platform Team",Harare

The comma inside "Engineer, Platform Team" is part of the field. A simple:

cut -d, -f2

doesn’t understand CSV quoting rules.

This is where the difference between comma-separated text and proper CSV parsing becomes important.

For simple exports, these Unix tools are wonderfully useful.

For complicated CSV files containing quoted fields, embedded commas, escaped quotes, or multiline fields, use a proper CSV-aware tool or library.

Knowing when not to use a tool is part of mastering it.

The Textsmith’s CSV Toolbox

So the next time somebody hands you a CSV file, don’t automatically reach for a spreadsheet.

First ask:

What am I actually trying to discover?

And remember that these commands become even more powerful when connected with pipes:

cut -d, -f3 people.csv | sort | uniq -c

That little pipeline is more than a CSV trick.

It is an example of a larger Unix idea:

Take simple transformations and compose them into useful ones.

A CSV file may look like a spreadsheet.

A Textsmith sees a stream of structured text.

• Weekly Nugget

• Text Records
• Delimited Fields
• Plain Text
• Fields