Combine multiple csv files into one workbook separate sheets in UNIX

It is very easy to merge multiple CSV files in Python using Pandas library. This blog will explain to you all the parts of concatenating multiple CSV files and export them as one excel sheet.

Prerequisites for merging multiple CSV files using Python

  1. Python 3.4 or higher
  2. Pandas
  3. Glob

Install Pandas

pip install pandas

Install Glob

pip install glob3

Now, simply make sure all the files that you are going to merge have the same number of columns and they fall in the same order. If not, use pandas to slice and rearrange columns before merging.

Here's a perl script that reads in each line of each file specified on the command line and appends it to elements in the array (@csv). When there's no more input, it prints out each element of @csv.

The .csv files will be appended in the order that they are listed on the command line.

WARNING: This script assumes that all input files have the same number of lines. Output will likely be unusable if any file has a different number of lines from any of the others.


use strict;

my @csv=();

foreach (@ARGV) {
  my $linenum=0;

  open(F,"<",$_) or die "couldn't open $_ for read: $!\n";

  while () {
    $csv[$linenum++] .= "," . $_;


foreach (@csv) {
  s/^,//;   # strip leading comma from line
  print $_,"\n";

Given the following input files:

==> 1.csv <==

==> 2.csv <==

==> 3.csv <==

it will produce the following output:

$ ./ *.csv

OK, now that you've read this far it's time to admit that this doesn't do anything that paste -d, *.csv doesn't also do. So why bother with perl? paste is quite inflexible. If your data is exactly right for what paste does, you're good - it's perfect for the job and very fast. If not, it's completely useless to you.

There are any number of ways a perl script like this could be improved (e.g. handling files of different lengths by counting the number of fields for each file and adding the correct number of empty fields to @csv for each of the file(s) which are missing lines. or at least detecting different lengths and exiting with an error) but this is a reasonable starting point if more sophisticated merging is required.

BTW, this uses a really simple algorithm and stores the entire contents of all input files in memory (in @csv) at once. For files up to a few MB each on a modern system, that's not unreasonable. If, however, you are processing HUGE .csv files, a better algorithm would be to:

Although a CSV file can hold a huge amount of data, the sheer weight of these files can cause issues when you want to share the file with others. As a result, large CSV files are often divided into multiple, smaller files. This can become an overwhelming task when you need to edit your data. Luckily, CSV files are compatible with most software applications, so you can easily import your CSV files into your desktop and online spreadsheet tools.

In this article, you’ll learn how to combine multiple CSV files into one Excel file by using the Power Query built-in feature. You’ll also learn how to use the Windows Command Prompt function to merge multiple CSV files. Finally, you’ll explore our top online tools that are an alternative to Excel’s built-in functions.

Get Started With Layer Today!

Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

How do I combine multiple CSV files into multiple worksheets?

Import multiple CSV files to Excel with Copy Sheets tool.
On the Ablebits Data tab, click Copy Sheets and indicate how you want to import the files: ... .
Click the Add files button, and then find and select the csv files for importing. ... .
Finally, the add-in will ask exactly how you want to paste the data..

How do I merge multiple CSV files in UNIX?

Using the csvkit Package csv files on the command line. Now, we can use the command csvstack to merge two . csv files together.

How do I combine multiple CSV files into one Excel spreadsheet?

How to combine multiple CSV files into one using Power Query?.
Go to the folder where you have stored all your CSV files. ... .
Open an Excel spreadsheet and go to Data > Get Data > From File > From Folder. ... .
Select the folder containing the CSV files and click “Open”..

How to combine multiple CSV files into one workbook using shell script?

Once cloned or copy the can execute the below command to merge csv files. In above command -f 1 gets all content (from line 1) of first matching csv file and -s 2 gets all matching file contents from line 2 of the rest of the files.