Merging contents of multiple .csv files into single .csv file

Question

I want to write a script that merges contents of several .csv files in one .csv file, i.e appends columns of all other files to the columns of first file. I had tried doing so using a "for" loop but was not able to proceed with it.

Does anyone know how to do this in Linux?

Answer 1

The simplest approach for achieving that would be typing the following command

cat *csv > combined.csv

This file would contain the contents of all your csv files just in the way you mentioned.

Answer 2

awk '(NR == 1) || (FNR > 1)' *.csv > 1000Plus5years_companies_data.csv

Answer 3

use paste

paste -d ',' file1.csv file2.csv ... fileN.csv

Answer 4

Use csvstack from csvkit:

 csvstack *.csv  > out.csv

Answer 5

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.

#!/usr/bin/perl

use strict;

my @csv=();

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

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

  while (<F>) {
    chomp;
    $csv[$linenum++] .= "," . $_;
  };

  close(F);
};

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

Given the following input files:

==> 1.csv <==
1,2,3,4
1,2,3,4
1,2,3,4
1,2,3,4

==> 2.csv <==
5,6,7,8
5,6,7,8
5,6,7,8
5,6,7,8

==> 3.csv <==
9,10,11,12
9,10,11,12
9,10,11,12
9,10,11,12

it will produce the following output:

$ ./mergecsv.pl *.csv
1,2,3,4,5,6,7,8,9,10,11,12
1,2,3,4,5,6,7,8,9,10,11,12
1,2,3,4,5,6,7,8,9,10,11,12
1,2,3,4,5,6,7,8,9,10,11,12

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:

• open all the input files and, while there's still input to read:
  • read a line from each file
  • append the lines (in @ARGV order)
  • print the appended line

Answer 6

The below command basically takes all *.csv files , performs a unique sorting to remove duplicates ( headers ) and creates a new file having all the requisite data

cat users/*.csv | sort -u > users.csv

Pre-Requisites

1. Unique Data
2. Every .csv file must have a line break in the end

Answer 7

Assuming all the csv's have a header and you only want it to show once heres the solution. Make bash script

#! /usr/bin/env bash
regex_pattern=$1
output_file=$2
first_file=`ls $regex_pattern | awk '{if(NR == 1) print $0}'`
# get header from first line of file
read -r first_line < $first_file
# print header 
echo $first_line > $output_file
# cat all files. Use grep to remove the headers. append to output file
cat $regex_pattern | grep -v $first_line >> $output_file 
# delete old csvs
rm $regex_pattern

then run bash csv_combiner.sh "path/to/dir/data.*.csv" combine_data.csv

Answer 8

If all CSV file contains same columns, this simple command could do the magic to collate CSV Summary from multiple CSV files.

cat *csv | awk '!a[$0]++' > summary.csv

Answer 9

I wrote this simple file merge script in which you can merge CSV files to a single CSV file (row wise not column wise though).

Once cloned or copy the file-merge-script.sh can execute the below command to merge csv files.

/bin/bash file-merge-script.sh -d ~/myfiles -o 'result.csv' -f 1 -s 2

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.

Get the file-merge-script.sh from here https://github.com/prabushitha/file-merge-script