Comparing two csv files matching and merging using perl

Question

I have two csv files named

alexa_products.csv

name,         sku,      urle,     product,  data

amazon,   amazon.com,   current,  mobile,   seller

vinnes,   vinnes.com,   current,  cellular, Aircel_Indore

Data.csv

name,          sku,      urle,    product,   data

linkedin.com, linkeidn,  current, local,     blah

airtel.com,    airtel,   current, sim,       Airtel

amazon.com,    amazon,   face, network,    buyier

vinnes.com,    vinnes,   look, hands,      ddde

Now i have to match name from alexa_products.csv and sku from data.csv if there is any match and i have to print out all the datas only from the particular column from two csv file to another csv file ?

Expected output

amazon.com,    amazon,   face, network,    buyier, current,  mobile,   seller

vinnes.com,    vinnes,   look, hands,      ddde,  current,  cellular, Aircel_Indore

Answer 1

You could try something along these lines:

sed "1d;s/ //g" alexa_products.csv | sort > a
sed "1d;s/ //g" data.csv | sort > b
join -t, -1 1 -2 2 a b > newfile.csv

Yes, I know it's not very good Perl ;-)

The "sed" command removes the header line (line 1), and all spaces from alexa_products.csv. Then the remainder of the file is sorted using "sort" and saved as file "a".

Likewise, file "data_products" has its header and spaces removed, gets sorted and stored in file "b".

Then "join" uses field 1 of file "a" and matches it with field "2" in file b and prints where they match.

You can use the command "man sed" or "man join" to read about the manuals about the commands - press SPACEBAR to get next page and "q" to quit reading.

Answer 2

Since you haven't mentioned about the columns that you are interested in,I just say this command will print all columns of second file when there is a match with the first file.

awk -F, 'FNR==NR && NR!=1 && FNR!=1
        {
        a[$1]=$0;next
        }{if($2 in a)
           {
            split(a[$2],b," ");
            print $0,b[3],b[4],b[5]
           }
         }' alexa_products.csv data.csv

Answer 3

Here is some Perl to get you started, just for kicks!

#!/usr/bin/perl
use strict;
use warnings;
use Data::Dumper;

my %alexa;
my ($name,$sku,$urle,$product,$data);

# Parse first file
my $line=1;
open(my $fh,"<","alexa_products.csv")|| die "ERROR: Could not open alexa_products.csv";
while (<$fh>)
{
   next if $line++==1;  # Ignore header
   chomp;       # Remove LF
   s/ //g;      # Remove spaces
   ($name,$sku,$urle,$product,$data) = split(',');  # Split line on commas
   $alexa{$name}{'sku'}=$sku;
   $alexa{$name}{'urle'}=$urle;
   $alexa{$name}{'product'}=$product;
   $alexa{$name}{'data'}=$data;
}
close($fh);

# Next line for debugging, comment out if necessary
print Dumper \%alexa;

# Now read data file 
$line=1;
open($fh,"<","Data.csv")|| die "ERROR: Could not open Data.csv";
while(<$fh>)
{
   next if $line++==1;      # Ignore header line
   chomp;           # Remove LF
   s/ //g;          # Remove spaces

   my ($name,$sku,$urle,$product,$data) = split(',');   # Split line on commas
   if(defined $alexa{$sku}){
      print "$alexa{$sku}{'sku'},$alexa{$sku}{'data'},$alexa{$sku}{'product'}\n";   # You may want different fields
   }
}