in reply to Combining Records From Multiple Files based on Common Key Values

The subject line of your question contains the words "key values", that could have given you a hint that you can solve this problem using a hash :-).

Normally I recommend using Text::CSV_XS on CSV files, but this example data looks simple enough to handle without an extra module (reconsider using the module if your real datafiles should contain things like quoted data or escaped commas):

#!/usr/bin/perl

use strict;
use warnings;

my %result;

for my $file ("File_A","File_B","File_C") {

    open (my $fh,"<",$file) or die "Can't open file $file: $!";
    <$fh>; # skip header line

    while (my $line = <$fh>) {
        my ($server,$cpua) = (split(",",$line))[0,1];
        push @{$result{$server}},$cpua;
    }

    close $fh or die "Can't close file $file: $!";
}

open (my $nfh,">","Result_File") or die "Can't open result file: $!";

for my $server (sort keys %result) {
    print $nfh $server.",".join(",",@{$result{$server}})."\n";
}
close $nfh or die "Can't close result file: $!";

[download]

This assumes that you want the result file to contain the servers listed in alphabetical order. If you want a different ordering, simply construct an array holding the server names in the order you want them and use that for the final loop. For example 

my @order = qw(wsompapgtw05 wsomddvfxa01 wsomdavpra03 WSOMQAVPRA05);

for my $server (@order) {
...

[download]

All dogma is stupid.

Replies are listed 'Best First'.
Re^2: Combining Records From Multiple Files based on Common Key Values
by country1 (Acolyte) on Jul 16, 2007 at 12:39 UTC
    tirwhan, thanks for your help. Your solution assumes that the 3 input files have the server names in the same order in each of the 3 files. This is not always the case. How do you get the for and while loops to match the keys (i.e., server name) regardless of where they are in the 3 input files?
[reply]
      Your solution assumes that the 3 input files have the server names in the same order in each of the 3 files

      Umm, no it doesn't. The cpu value get associated with the server name, regardless on which line they appear. Output order is also regardless of the order the servers are in in the input files. Try it out and you'll see (this is a hash, not an array!).


      All dogma is stupid.
[reply]
[d/l]

        tirwhan,


        I'm sorry. The question I meant to ask was as
        follows:


        The 3 CSV files which I am using as input do not have
        identical server names.


        File A
        Server,Avg CPU,P95 CPU,Avg Mem Util,P95 Mem Util
        WSOMQAVPRA05,93.75,95.87,66.67,68.13
        wsomdavpra03,90.39,94,65.77,68.51
        wsomddvfxa01,39.22,92.19,82.59,88.25


        File B
        Server,Avg CPU,P95 CPU,Avg Mem Util,P95 Mem Util
        WSOMQAVPRA05,34.78,100,55.1,67.6
        wsomdavpra03,69.04,98.55,84.07,89.73
        wsomddvfxa01,92.44,97.54,67.72,71.69
        wsompapgtw05,48.77,96.9,92.1,93.55


        File C
        Server,Avg CPU,P95 CPU,Avg Mem Util,P95 Mem Util
        WSOMQAVPRA05,93.13,98.11,68.95,73.47
        wsomdavpra03,68.85,97.56,76.35,98.23
        wsomddvfxa01,46.97,96.29,88.23,94.02
        wsompapgtw05,30.66,93.74,39.89,71.35


        What I am trying to do is for each Server (in Column
        1) I want to get the Avg CPU (in Column 2) from each
        of the 3 files. In the files above this would produce


        File OUT
        WSOMQAVPRA05,93.75,34.78,93.13
        wsomdavpra03,90.39,69.04,68.85
        wsomddvfxa01,39.22,92.44,46.97
        wsompapgtw05,0,48.77,30.66


        Notice in File OUT for Server Name (wsompapgtw05),
        since wsompapgtw05 does not appear in File A, the
        value is replaced with '0'. How can I get perl to
        place a '0' in the output file when a particular
        server name appears in at least 1 of the input files,
        but not in all of the input files?

[reply]

In Section Seekers of Perl Wisdom