How to convert XLS and JSON files to CSV in Linux with csvkit

I can’t tell you how many times I’ve had to work to upload data to a new system (be it a CMS, CRM, HRM … you name it), only to find out the platform wouldn’t accept the file format I had available. I might have a spreadsheet or JSON file with tons of data, but the system would only accept a CSV file.

CSV.

You know what it is: Comma-Separated Value. It’s a flat file of data, each entry separated by a comma. Now, some applications make it possible to export a file to CSV format. Even Google Docs allows for such an action with Sheets. But not every application can take care of that export. And you might have a file that was created by another application (such as an app or service that generates JSON files) and you need that file converted to CSV format.

SEE: 40+ open source and Linux terms you need to know (TechRepublic Premium)

What do you do?

Since we have Linux available to us, you turn to a simple, open-source tool called csvkit. This handy app has saved me a few hours worth of work on a number of occasions. What csvkit can do is convert to and work with CSV. If you’ve ever done any development work or deployed enough networked platforms (such as CMS tools), you’ve most likely run into a situation where you needed to upload data in CSV format.

And all you have is a spreadsheet file. 

If you’re on a desktop, you could always open the file in your office suite of choice and do a Save As. But what if you’re on a headless server and you need to work quickly? You don’t want to have to SCP the file to your desktop, load the file, save it in the requisite format, and then SCP the file back to the server.

You want to be efficient. Hence, csvkit.

Let’s get it installed and see how it works.

What you’ll need

I’ll be demonstrating csvkit on Ubuntu Server 21.10. In order to make this work, you’ll need a Ubuntu-based distribution and a user with sudo privileges. Finally, you’ll need a spreadsheet file to convert.

How to install csvkit

Csvkit is available in the standard repositories, so the installation is very simple. Log into your Ubuntu server (or desktop), open a terminal window, and issue the command:

sudo apt-get install csvkit -y

That’s it for the installation.

How to convert an XLS file to CSV

Let’s say you have the file clients.xlsx and you need it converted to clients.csv to be then uploaded as a client list in your new CRM tool. The spreadsheet is already laid out in the exact format needed for your CMS, so all you have to do is convert it. 

For such a conversion, you’d use the in2csv command (included with csvkit) like so:

in2csv clients.xlsx > clients.csv

If you have unnamed columns or rows in your source file, you might see some output from the command warning that alphanumeric values were used in place of the missing entries. The conversion should take just a few seconds (depending on how large the file is). Once it’s complete, you should have a file named clients.csv in the same folder. You can check that file with:

less clients.csv

You should see a perfectly-formatted CSV file, ready to be uploaded (Figure A).

Figure A

The same holds true for the conversion of a JSON file, which is handled like so:

in2csv clients.json > clients.csv

If you need to convert that file from CSV to JSON, you can do that with the csvjson command like so:

csvjson clients.csv > clients.json

Csvkit has a couple of other handy tricks up its sleeve. You can list out the column names in a file with:

cvscut -n clients.csv

Now that you know the column names from your newly converted CSV source, you could convert only certain columns from the clients.csv file to another CSV file. Say you have columns First Name and Last Name and you only want those columns converted to the new file. That would be done with:

csvcut -c 'First Name','Last Name' clients.csv > names.csv

And that’s how we can manipulate XLS, JSON, and CSV files with csvkit. This tool might not be used very often, but when you need it, you’ll be happy it’s around.

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the latest tech advice for business pros from Jack Wallen.

Open Source Weekly Newsletter

You don’t want to miss our tips, tutorials, and commentary on the Linux OS and open source applications.
Delivered Tuesdays

Sign up today

Also see