More Fun With CSV Files in Linux: Sorting, Swapping, and Appending with `csvtool`

More Fun With CSV Files in Linux: Sorting, Swapping, and Appending with csvtool

Introduction

CSV files are a flexible and widely-used format, and Linux provides some excellent tools for working with them. One tool that often flies under the radar is csvtool, a lightweight utility that lets you perform basic operations like sorting, swapping rows and columns, and even appending multiple files.

In this article, we'll explore these features and work through a few examples using csvtool to manipulate CSV files on Linux. 

Installing csvtool

Before we dive into the commands, let’s ensure csvtool is installed on your system.

$ sudo apt update <ENTER>
$ sudo apt install cvstool <ENTER>

This will install csvtool and allow you to start working with CSV files right away.

Sorting CSV Files

Sorting CSV files is a helpful way to organize your data. Let's start with a simple CSV file called data.csv:

$ cat data.csv <ENTER>

Name,Age,Occupation
Alice,30,Engineer
Bob,24,Designer
Charlie,35,Teacher

Sorting by Age

To sort the file by the "Age" column (the second column), use the sort command:

$ sort -t, -k2 -n data.csv <ENTER>

Name,Age,Occupation
Bob,24,Designer
Alice,30,Engineer
Charlie,35,Teacher

Here, the -t option specifies the comma as the delimiter, -k2 by the second column (Age), and -n ensures numeric sorting.

Sorting Alphabetically by Name

If you want to sort the CSV file alphabetically by the "Name" column (the first column):

$ sort -t, -k1 data.csv <ENTER>

Name,Age,Occupation
Alice,30,Engineer
Bob,24,Designer
Charlie,35,Teacher

This command sorts the data alphabetically based on the "Name" column.

Swapping Rows and Columns

Sometimes, you might need to transpose your data, swapping rows and columns to get a different view. Let’s transpose the data in data.csv:

$ csvtool transpose data.csv <ENTER>

Name,Alice,Bob,Charlie
Age,30,24,35
Occupation,Engineer,Designer,Teacher

This flips the rows and columns, which can be helpful for displaying or analyzing data from another perspective.

Appending CSV Files

Appending CSV files is a simple process, but care needs to be taken to handle the header rows. When appending files, we assume that each file has a header row in line 1, and we want to exclude the header from any file being appended.

Example

Let's say you have two CSV files:

file1.csv:

Name,Age,Occupation
Alice,30,Engineer
Bob,24,Designer

file2.csv:

Name,Age,Occupation
Charlie,35,Teacher
David,28,Scientist

To append file2.csv to file1.csv without duplicating the headers, we remove the first row of file2.csv using the tail command:

$ tail -n +2 file2.csv >> file1.csv <ENTER>

Now, when you check file1.csv it will contain:

$ cat file1.csv <ENTER>

Name,Age,Occupation
Alice,30,Engineer
Bob,24,Designer
Charlie,35,Teacher
David,28,Scientist

The command tail -n +2 file2.csv >> file1.csv reads file2.csv starting from line 2 (skipping the header) and appends the output to file1.csv.  This approach appends the data from file2.csv to file1.csv without repeating the header row.

Conclusion

csvtool is a powerful and flexible tool for working with CSV files in Linux. Whether you're sorting data, swapping rows and columns, or appending files, csvtool makes it easy to manage and manipulate your data in a lightweight, system-wide way. This tool is perfect for quick and efficient CSV tasks without needing the overhead of additional installations or virtual environments.

Image:  Seppe Machielsen from Pexels