I needed to convert a Microsoft Excel XLSX file containing an ID column into a comma-separated list of those IDs for passing to a command line tool.
In the end it was quite straightforward with the help of csvkit and a sprinkle of UNIX.
in2csv reports.xlsx | csvcut -c 1 | sed '1d' | paste -sd , -
To convert from XLSX to CSV format you can use the handy
in2csv utility from csvkit, which automatically detects the type of input file.
The ID column is in the first column of the CSV. The
csvcut utility from csvkit can be used to extract one or more columns from a CSV. You can also pass the column header name to the
-c argument, instead of a number.
csvcut -c 1
The output now contains a single column of data from the CSV, but it still has the header. That can be removed with the help of
sed’s delete function.
The IDs are now one-per-line, so the final step is to join them together onto a single line with
paste -sd , -