Re: Compare 2 CSV files and create a new CSV file out of comparision

Your two files are actually two tables in a relational database system.

So use the power of DBI and SQL.

use Modern::Perl;
use Data::Dump qw/dump/;
use DBI;

my $dbh = DBI->connect(
    "dbi:CSV:",
    "", "",
    {
        f_schema => 'test',
        f_dir    => './test',
        f_ext    => '.csv',
    }
) or die "Could not open database: $DBI::errstr";
$dbh->{RaiseError} = 1;

my $query =
'SELECT file1.vm_name, vm_cluster, vFiler_IP, vFiler_cluster FROM file
+1 join file2 WHERE file1.vm_name = file2.vm_name';
my $result = $dbh->selectall_arrayref($query);

print dump($result);
[download]

Output:

[
  ["vm1", "fd1", "1.1.1.1", "fd4"],
  ["vm2", "fd2", "1.1.1.2", "fd3"],
  ["vm3", "fd3", "1.1.1.3", "fd2"],
  ["vm4", "fd4", "1.1.1.4", "fd1"],
]
[download]

CountZero

A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

My blog: Imperial Deltronics

Comment on Re: Compare 2 CSV files and create a new CSV file out of comparision Select or Download Code

Replies are listed 'Best First'.
Re^2: Compare 2 CSV files and create a new CSV file out of comparision by slayedbylucifer (Scribe) on Jul 03, 2012 at 10:55 UTC
CounterZero, This is Exactly what i was looking for. When I wrote my code for the 1st time and had nested "while" loops, I knew that this not the right way to do and there must be an easier way to do so. Thanks for the DBI solution. Thank you.	[reply]
Re^2: Compare 2 CSV files and create a new CSV file out of comparision by Anonymous Monk on Jan 19, 2014 at 21:48 UTC
This is an example that does not fully work: this is my improved Code ( Hope it will help somebody in the future) : use Data::Dump qw/dump/; use DBI; my $dbh = DBI->connect( "dbi:CSV:", "", "", { #f_schema => 'test', f_dir => '.', f_ext => '.csv', } ) or die "Could not open database: $DBI::errstr"; $dbh->{RaiseError} = 1; $dbh->{csv_tables}->{file1} = { file => 'vm.csv', }; $dbh->{csv_tables}->{file2} = { file => 'vfiler_ds.csv', }; my $query = "SELECT file1.vm_name, vm_cluster, vFiler_IP, vFiler_cluster FROM file1 join file2 WHERE file1.vm_name = file2.vm_name"; my $result = $dbh->selectall_arrayref($query); print dump($result);	[reply]
Re^3: Compare 2 CSV files and create a new CSV file out of comparision by Anonymous Monk on Jan 19, 2014 at 21:50 UTC
Now hopefully more readable This is an example that does not fully work: this is my improved Code ( Hope it will help somebody in the future) : use Data::Dump qw/dump/; use DBI; my $dbh = DBI->connect( "dbi:CSV:", "", "", { #f_schema => 'test', f_dir => '.', f_ext => '.csv', } ) or die "Could not open database: $DBI::errstr"; $dbh->{RaiseError} = 1; $dbh->{csv_tables}->{file1} = { file => 'vm.csv', }; $dbh->{csv_tables}->{file2} = { file => 'vfiler_ds.csv', }; my $query = "SELECT file1.vm_name, vm_cluster, vFiler_IP, vFiler_cluster FROM file1 join file2 WHERE file1.vm_name = file2.vm_name"; my $result = $dbh->selectall_arrayref($query); print dump($result);	[reply]
Re^4: Compare 2 Compare 2 CSV files and create a new CSV file out of comparision by Tux (Canon) on Jan 20, 2014 at 07:13 UTC
Overly complicated. Why use file and file1? That doesn't say anything in the queries, making them hard to read. `my $dbh = DBI->connect ("dbi:CSV:", undef, undef, { f_dir => ".", f_ext => ".csv/r", RaiseError => 1, PrintError => 1, }) or die "Could not open database: $DBI::errstr"; my $result = $dbh->selectall_arrayref (join " " => "SELECT vm.vm_name, vm_cluster, vFiler_IP, vFiler_cluster", "FROM vm JOIN vfiler_ds", "WHERE vm.vm_name = vfiler_ds.vm_name");` [download] Using `f_ext` - which you already do - just add `/r` to make the extension required, and just use the file names as table names in the query. As the `f_***` attributes are just DBI attributes (passed to DBD::File eventually), you can put `RaiseError` and other DBI attributes in the same hash. Enjoy, Have FUN! H.Merijn	[reply] [d/l] [select]