32

I need to merge multiple .CSV files (using the cat command) but without copying the header for each file.

What's the best way to accomplish this task?

6 Answers 6

42

You'll need more than the cat command, as described here:

Say you have 3 CSV-files: file1.csv, file2.csv, and file3.csv and want to join them to bigfile.csv and your header is always (only) the first line, then use

either (keep header from first file "file1.csv"):

cat file1.csv <(tail +2 file2.csv) <(tail +2 file3.csv) > bigfile.csv

or (remove header from all files who's names begin with "file"):

awk 'FNR > 1' file*.csv > bigfile.csv
2
  • 4
    I found this looking for a generic linux answer, but in my case this didn't exactly work. It would silently ignore file1.csv. I needed to cat that file. cat <(cat file1.csv) <(tail +2 file2.csv) <(tail +2 file3.csv) > bigfile.csv
    – Lelon
    Jun 18, 2013 at 20:41
  • actually I had to use tail -n+2, tail +2 wouldn't work Jan 9, 2014 at 16:51
29

I agree with the top answer but I suggest to extend it with the following scenario (as I can not comment):

If you want the output file to contain header (once) the correct script is:

awk '(NR == 1) || (FNR > 1)' file*.csv > bigfile.csv

FNR represents the number of the processed record in a single file. And NR represents it globally, so first line is accepted and the rest are ignored as before.

1
  • I keep comming back to this answer everytime I come across a CSV file merge on command line, requiring to keep the header of only one file.
    – lsdr
    Dec 17, 2020 at 0:31
8

You could also use a group command ({ ; }) instead of process substitution (<()):

{ head -n1 file1.csv; for f in file*.csv; do tail -n+2 "$f"; done; } > new.csv

It also works with CRLF line endings as long as the files end with an empty line (\r\n).

The number-only versions of head and tail were made obsolete by POSIX 1003.1-2001, and they result in warnings in some environments.

2

Needed to concatenate two large CSVs with identical columns into larger CSV for chunking script (data does not have unique id's).

First took header out of second csv

awk 'FNR > 1' file2.csv > file2_noheading.csv

Next, concatenated via the following

cat file1.csv file2_noheading.csv > newfile.csv
1

Using the command sequence above resulted in a file looking like this:

header,of,csv1
contents,of,csv1
==> csv2.csv

contents,of,csv2

To make it a proper CSV, with one header line and all the relevant values, I employed the following sed incantation... sed -ie "/^$/d;/^==>/d" bigfile.csv

1

Easier Solution if you have a ton of files :

awk 'FNR > 1' *.csv > merged.csv

Just go back to edit the big file and add the header back in.

3
  • How is your answer any different than what was all ready presented by iolsmit in 2013, being awk 'FNR > 1' file*.csv > bigfile.csv? It's not! Mar 21, 2017 at 0:15
  • Re: how is it any different? It is a more terse answer and the one that I copied and pasted, at least : ) Gets my upvote Oct 17, 2018 at 16:14
  • 1
    This is a good answer, because you don’t need all the files to start with file
    – big_smile
    Sep 26, 2019 at 12:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .