17

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?

25

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
  • 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 '13 at 20:41
  • I am getting tail+2: command not found when i used cat < file1.csv <(tail +2 file2.csv) <(tail +2 file3.csv) > bigfile.csv method – user64636 Dec 11 '13 at 20:01
  • @user64636 there should be a space character between tail and +2 – nohillside Dec 11 '13 at 22:20
  • actually I had to use tail -n+2, tail +2 wouldn't work – Matthieu Napoli Jan 9 '14 at 16:51
10

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.

6

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.

  • 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! – user3439894 Mar 21 '17 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 – Rick Davies Oct 17 '18 at 16:14

You must log in to answer this question.

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