collate / concatenate / aggregate sets of files with identical fields

[avilella]

Prerequisites

• make sure you're are using the latest version by csvtk version
• read the usage

Describe your issue

I have a collection of worksheets in an xlsx file, each worksheet should be of the same type. I want to collate them together (or concatenate or aggregate, not sure what the best word is), so that the order in which I concatenate them is preserved, but the duplicated headers disappear. I am currently doing this with datamash rmdup 1 below, but I rather have a pure csvtk recipe for this:

csvtk xlsx2csv -n LISTOFWORKSHEETS $meta | csvtk cut -f1 | while read ws; do csvtk xslx2csv -n $ws $meta; done | datamash rmdup 1 -t ',' > final_collated_file.csv

What would be the recipe for this collate operation in csvtk? Thanks in advance.

[shenwei356]

I'll add a concat command.

for f in *.xlsx; do csvtk xlsx2csv $f > $f.csv; done

csvtk concat *.xlsx.csv > final.csv

[shenwei356]

csvtk concat is available in v0.13.0-dev or later versions.

You can use command:

csvtk xlsx2csv -n LISTOFWORKSHEETS $meta \
    | csvtk cut -f1 \
    | while read ws; do \
        csvtk xslx2csv -n $ws $meta > $meta.$ws.csv ; \
      done;
csvtk concat *.csv > final_collated_file.csv

[avilella]

Fantastic! Thank you.

[shenwei356]

You're welcome. Please share with others if you find csvtk useful. 😄