How to combine multiple csv files into a single excel sheet using unix shell scripting?

Question

E.g. we have 3 csv files 1.csv, 2.csv,3.csv. I want the output as All.xls

containing

the 3 csv files 1.csv, 2.csv and 3.csv in their respective tab.

Could you help me? Thank you in advance.

Answer 1

Yes the are multiple ways to do what you want. Perl, Python and Ruby have the appropriate modules. Probably other scripting languages also. Depends on which scripting language you are comfortable with.

Here is a pointer to one way of doing what you want using Python: Python script to convert CSV files to Excel

Answer 2

The command to cat files together to produce a new file is cat. However, if you simply did a

 cat *csv >All.xls

you would also have header lines in the middle of the resulting files. There are two ways to work around this problem:

The first involves that you create temporary files out of each csv file, where the header line is missing, before putting together the pieces. This can be done using the tail command, for example

tail -n +2 2.csv >2_without_header.csv

The second possibility may or may not be applicable in your case. If - as it is often the case with CSV files - the order of the lines doesn't matter and duplicate lines can be ignored and - as it is likely in your case - the headers are identical, you could simply do a

sort -u *csv >All.xls

Answer 3

You can try awk '!a[$0]++' ./*.csv > ./all.xls This command will combine all the csv files in current folder and create a new file: all.xls with single header row.

Answer 4

You can use this well maintained Perl script:

https://metacpan.org/release/Text-CSV_XS

which exists in most Linux distributions (although with different names):

https://repology.org/project/perl:text-csv-xs/versions

Here is the syntax

csv2xlsx -o ~/All.xlsx 1.csv 2.csv 3.csv