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

Generally you simply want a fast technique of changing a spreadsheet to a comma-separated values file. When you do not have a GUI to deal with the duty, Jack Wallen has the right command for the job.

videogameprogrammer.jpg

Picture: GettyImages/Edwin Tan

I am unable to let you know what number of instances I’ve needed to work to add knowledge to a brand new system (be it a CMS, CRM, HRM … you title it), solely to search out out the platform would not settle for the file format I had obtainable. I may need a spreadsheet or JSON file with tons of knowledge, however the system would solely settle for a CSV file.

CSV.

You recognize what it’s: Comma-Separated Worth. It is a flat file of knowledge, every entry separated by a comma. Now, some functions make it attainable to export a file to CSV format. Even Google Docs permits for such an motion with Sheets. However not each software can deal with that export. And also you may need a file that was created by one other software (resembling an app or service that generates JSON recordsdata) and also you want that file transformed to CSV format.

SEE: 40+ open supply and Linux phrases it’s good to know (TechRepublic Premium)

What do you do?

Since now we have Linux obtainable to us, you flip to a easy, open-source instrument known as csvkit. This helpful app has saved me a couple of hours price of labor on a variety of events. What csvkit can do is convert to and work with CSV. In the event you’ve ever accomplished any improvement work or deployed sufficient networked platforms (resembling CMS instruments), you have more than likely run right into a state of affairs the place you wanted to add knowledge in CSV format.

And all you’ve gotten is a spreadsheet file. 

In the event you’re on a desktop, you might at all times open the file in your workplace suite of selection and do a Save As. However what in the event you’re on a headless server and it’s good to work shortly? You do not wish to should SCP the file to your desktop, load the file, put it aside within the requisite format, after which SCP the file again to the server.

You wish to be environment friendly. Therefore, csvkit.

Let’s get it put in and see the way it works.

What you will want

I will be demonstrating csvkit on Ubuntu Server 21.10. As a way to make this work, you will want a Ubuntu-based distribution and a person with sudo privileges. Lastly, you will want a spreadsheet file to transform.

The way to set up csvkit

Csvkit is out there in the usual repositories, so the set up could be very easy. Log into your Ubuntu server (or desktop), open a terminal window, and concern the command:

sudo apt-get set up csvkit -y

That is it for the set up.

The way to convert an XLS file to CSV

To illustrate you’ve gotten the file shoppers.xlsx and also you want it transformed to shoppers.csv to be then uploaded as a shopper record in your new CRM instrument. The spreadsheet is already specified by the precise format wanted on your CMS, so all it’s a must to do is convert it. 

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

in2csv shoppers.xlsx > shoppers.csv

In case you have unnamed columns or rows in your supply file, you may see some output from the command warning that alphanumeric values had been used rather than the lacking entries. The conversion ought to take only a few seconds (relying on how massive the file is). As soon as it is full, you need to have a file named shoppers.csv in the identical folder. You possibly can verify that file with:

much less shoppers.csv

You must see a perfectly-formatted CSV file, able to be uploaded (Determine A).

Determine A

csvkita.jpg

Our CSV file, exported from xlsx, utilizing in2csv.

The identical holds true for the conversion of a JSON file, which is dealt with like so:

in2csv shoppers.json > shoppers.csv

If it’s good to convert that file from CSV to JSON, you are able to do that with the csvjson command like so:

csvjson shoppers.csv > shoppers.json

Csvkit has a few different helpful tips up its sleeve. You possibly can record out the column names in a file with:

cvscut -n shoppers.csv

Now that the column names out of your newly transformed CSV supply, you might convert solely sure columns from the shoppers.csv file to a different CSV file. Say you’ve gotten columns First Identify and Final Identify and also you solely need these columns transformed to the brand new file. That may be accomplished with:

csvcut -c 'First Identify','Final Identify' shoppers.csv > names.csv

And that is how we will manipulate XLS, JSON, and CSV recordsdata with csvkit. This instrument won’t be used fairly often, however while you want it, you will be glad it is round.

Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the most recent tech recommendation for enterprise execs from Jack Wallen.

Additionally see

Recent Articles

spot_img

Related Stories

Leave A Reply

Please enter your comment!
Please enter your name here

Stay on op - Ge the daily news in your inbox