Sheetstack is a command line utility that merges multiple XLS/X sheets into a single CSV.
It's a simple extension of J useful for processing files with the exact same layout split into several worksheets.
Sheetstack adds a grouping column to the resulting CSV. By default this is the name of the sheet, but it could be anything.
npm install sheetstack
Let's say we have an xls file with two sheets, "dogs" and "cats":
name,best friend Pluto,Mickey Santa's Little Helper,Bart Scooby Doo,Shaggy
name,best friend Cat in the Hat,the fish Garfield,Jon Hello Kitty,you
The simplest use will combine all the files and output the result to stdout.
$ sheetstack file.xls sheet,name,best friend dogs,Pluto,Mickey dogs,Santa's Little Helper,Bart dogs,Scooby Doo,Shaggy cats,Cat in the Hat,the fish cats,Garfield,Jon cats,Hello Kitty,you
--sheets option controls which sheets are included, and in what order.
$ sheetstack --sheets dog,cat file.xls sheet,name,best friend dogs,Pluto,Mickey dogs,Santa's Little Helper,Bart dogs,Scooby Doo,Shaggy
--groups option specifies custom values for the grouping column,
--group-name sets the value for the top of the column.
$ sheetstack --groups canis,felis --group-name genus species,name,best friend canis,Pluto,Mickey canis,Santa's Little Helper,Bart canis,Scooby Doo,Shaggy felis,Cat in the Hat,the fish felis,Garfield,Jon felis,Hello Kitty,you
Removing leading lines
By default, sheetstack removes the first line from all sheets except for the first one. The can be changed with the
--rm-lines setting. If
--rm-lines is set to 0, no lines will be removed. Higher values will remove more lines, but no lines will be removed from the first sheet.
You can also specify row-separator and field-separator options, which are passed through to J:
$ sheetstack --row-sep '\r\n' --field-sep ;