Extracting a list of IDs from an XLSX file

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 , -

Steps

To convert from XLSX to CSV format you can use the handy in2csv utility from csvkit, which automatically detects the type of input file.

in2csv reports.xlsx

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.

sed '1d'

The IDs are now one-per-line, so the final step is to join them together onto a single line with paste.

paste -sd , -