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

3 years ago 312

Sometimes you conscionable request a speedy means of converting a spreadsheet to a comma-separated values file. When you don't person a GUI to instrumentality attraction of the task, Jack Wallen has the cleanable bid for the job.

I can't archer you however galore times I've had to enactment to upload information to a caller strategy (be it a CMS, CRM, HRM ... you sanction it), lone to find retired the level wouldn't judge the record format I had available. I mightiness person a spreadsheet oregon JSON record with tons of data, but the strategy would lone judge a CSV file.

CSV.

You cognize what it is: Comma-Separated Value. It's a level record of data, each introduction separated by a comma. Now, immoderate applications marque it imaginable to export a record to CSV format. Even Google Docs allows for specified an enactment with Sheets. But not each exertion tin instrumentality attraction of that export. And you mightiness person a record that was created by different exertion (such arsenic an app oregon work that generates JSON files) and you request that record converted to CSV format.

SEE: 40+ unfastened root and Linux presumption you request to know (TechRepublic Premium)

What bash you do?

Since we person Linux disposable to us, you crook to a simple, open-source instrumentality called csvkit. This useful app has saved maine a fewer hours worthy of enactment connected a fig of occasions. What csvkit tin bash is person to and enactment with CSV. If you've ever done immoderate improvement enactment oregon deployed capable networked platforms (such arsenic CMS tools), you've astir apt tally into a concern wherever you needed to upload information successful CSV format.

And each you person is simply a spreadsheet file.

If you're connected a desktop, you could ever unfastened the record successful your bureau suite of prime and bash a Save As. But what if you're connected a headless server and you request to enactment quickly? You don't privation to person to SCP the record to your desktop, load the file, prevention it successful the requisite format, and past SCP the record backmost to the server.

You privation to beryllium efficient. Hence, csvkit.

Let's get it installed and spot however it works.

What you'll request

I'll beryllium demonstrating csvkit connected Ubuntu Server 21.10. In bid to marque this work, you'll request a Ubuntu-based organisation and a idiosyncratic with sudo privileges. Finally, you'll request a spreadsheet record to convert.

How to instal csvkit

Csvkit is disposable successful the modular repositories, truthful the installation is precise simple. Log into your Ubuntu server (or desktop), unfastened a terminal window, and contented the command:

sudo apt-get instal csvkit -y

That's it for the installation.

How to person an XLS record to CSV

Let's accidental you person the record clients.xlsx and you request it converted to clients.csv to beryllium past uploaded arsenic a lawsuit database successful your caller CRM tool. The spreadsheet is already laid retired successful the nonstop format needed for your CMS, truthful each you person to bash is person it.

For specified a conversion, you'd usage the in2csv bid (included with csvkit) similar so:

in2csv clients.xlsx > clients.csv

If you person unnamed columns oregon rows successful your root file, you mightiness spot immoderate output from the bid informing that alphanumeric values were utilized successful spot of the missing entries. The conversion should instrumentality conscionable a fewer seconds (depending connected however ample the record is). Once it's complete, you should person a record named clients.csv successful the aforesaid folder. You tin cheque that record with:

less clients.csv

You should spot a perfectly-formatted CSV file, acceptable to beryllium uploaded (Figure A).

Figure A

The aforesaid holds existent for the conversion of a JSON file, which is handled similar so:

in2csv clients.json > clients.csv

If you request to person that record from CSV to JSON, you tin bash that with the csvjson bid similar so:

csvjson clients.csv > clients.json

Csvkit has a mates of different useful tricks up its sleeve. You tin database retired the file names successful a record with:

cvscut -n clients.csv

Now that you cognize the file names from your recently converted CSV source, you could person lone definite columns from the clients.csv record to different CSV file. Say you person columns First Name and Last Name and you lone privation those columns converted to the caller file. That would beryllium done with:

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

And that's however we tin manipulate XLS, JSON, and CSV files with csvkit. This instrumentality mightiness not beryllium utilized precise often, but erstwhile you request it, you'll beryllium blessed it's around.

Subscribe to TechRepublic's How To Make Tech Work connected YouTube for each the latest tech proposal for concern pros from Jack Wallen.