Typically you simply want a fast technique of changing a spreadsheet to a comma-separated values file. When you do not have a GUI to maintain the duty, Jack Wallen has the proper command for the job.
I can not 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 seek out out the platform would not settle for the file format I had out there. I may need a spreadsheet or JSON file with tons of knowledge, however the system would solely settle for a CSV file.
You understand what it’s: Comma-Separated Worth. It is a flat file of knowledge, every entry separated by a comma. Now, some purposes make it attainable to export a file to CSV format. Even Google Docs permits for such an motion with Sheets. However not each utility can maintain that export. And also you may need a file that was created by one other utility (equivalent to 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 essential to know (TechRepublic Premium)
What do you do?
Since we’ve Linux out there to us, you flip to a easy, open-source instrument known as csvkit. This helpful app has saved me just a few hours value of labor on quite a lot of events. What csvkit can do is convert to and work with CSV. For those who’ve ever performed any improvement work or deployed sufficient networked platforms (equivalent to CMS instruments), you’ve got almost definitely run right into a state of affairs the place you wanted to add knowledge in CSV format.
And all you might have is a spreadsheet file.
For those who’re on a desktop, you possibly can all the time open the file in your workplace suite of alternative and do a Save As. However what in case you’re on a headless server and it’s essential to work shortly? You do not need to must 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 need 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. In an effort 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.
Tips on how to set up csvkit
Csvkit is offered in the usual repositories, so the set up may be very easy. Log into your Ubuntu server (or desktop), open a terminal window, and problem the command:
sudo apt-get set up csvkit -y
That is it for the set up.
Tips on how to convert an XLS file to CSV
As an example you might have the file purchasers.xlsx and also you want it transformed to purchasers.csv to be then uploaded as a consumer 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 purchasers.xlsx > purchasers.csv
When you’ve got unnamed columns or rows in your supply file, you may see some output from the command warning that alphanumeric values had been used instead of the lacking entries. The conversion ought to take just some seconds (relying on how giant the file is). As soon as it is full, it is best to have a file named purchasers.csv in the identical folder. You may verify that file with:
much less purchasers.csv
It is best to see a perfectly-formatted CSV file, able to be uploaded (Determine A).
The identical holds true for the conversion of a JSON file, which is dealt with like so:
in2csv purchasers.json > purchasers.csv
If it’s essential to convert that file from CSV to JSON, you are able to do that with the csvjson command like so:
csvjson purchasers.csv > purchasers.json
Csvkit has a few different helpful tips up its sleeve. You may record out the column names in a file with:
cvscut -n purchasers.csv
Now that you already know the column names out of your newly transformed CSV supply, you possibly can convert solely sure columns from the purchasers.csv file to a different CSV file. Say you might have columns First Identify and Final Identify and also you solely need these columns transformed to the brand new file. That will be performed with:
csvcut -c 'First Identify','Final Identify' purchasers.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 if you want it, you will be completely satisfied it is round.
Subscribe to TechRepublic’s How To Make Tech Work on YouTube for all the most recent tech recommendation for enterprise professionals from Jack Wallen.