Script question - merging files based on first parameter

[S.Rider]

I have multiple files, here's the first few lines of each.
[ File-1 ]
Parameter,hostname1
NSTRBLKSCHED,-
NSTREVENT,50
NSTRPUSH,16

[ File-2 ]
Parameter,hostname2
NSTRBLKSCHED,-
NSTREVENT,100
NSTRPUSH,26

[ File-3 ]
Parameter,hostname3
NSTRBLKSCHED,-
NSTREVENT,150
NSTRPUSH,36

etc - etc - etc

I would like to read multiple input files, and if the first value in each line matches, output a single output line.
For example, from the above, I would get

Parameter,hostname1,hostname2,hostname3
NSTRBLKSCHD,-,-,-
NSTREVENT,50,100,150
NSTRPUSH,16,26,36

I would then use the csv file to create an xls comparing the kernal parms of multiple servers. Is there an easy way to merge files like this based on the 1st parameter ?

[James R. Ferguson]

Hi:

The following should serve your needs:

# cat cmpkparm
#!/usr/bin/perl
use strict;
use warnings;
my %lines;
my ( $parm, $value );
while (<>) {
chomp;
( $parm, $value ) = split( /,/, $_ );
if ( exists $lines{$parm} ) {
push( @{ $lines{$parm} }, $value );
}
else {
push( @{ $lines{$parm} }, $parm, $value );
}
}
foreach $parm ( sort keys %lines ) {
print "@{$lines{$parm}}";
print "\n";
}
1;

...Run as:

# ./cmpkparm file1 file2 file3 ...

That is, pass as many file names on the commandline as you need to process. The format of each file is as you posted.

Regards!

...JRF...

[S.Rider]

THANKS - but one dumb perl question.
How do I get the commas between the values outputed ?

[Sandman!]

How about giving this awk construct a try:

awk -F, '{if(p[$1])p[$1]=p[$1]","$2;else p[$1]=$2}END{for(i in p) print i,p[i]}' files

cheers!

[Sandman!]

Matter of fact the code is easier to follow if indented and you can specify as many files as you like on the awk input line...so here goes:

awk -F"," '{
if(p[$1])
p[$1]=p[$1]","$2
else
p[$1]=$2
} END{for(i in p) print i","p[i]}' File-1 File-2 File-3

[Hein van den Heuvel]

You can fix JRF's solution to get commas by replacing the final printout with:

foreach $parm ( sort keys %lines ) {
$_ = join ",", @{$lines{$parm}};
chop;
print "$_\n";
}

I did not expect to need to chop the last ",", but it was needed on my windoze box

Mind you, that solution requires that all params are present for each node, and the params are re-ordered alphabetically, including the 'header' line.


Below is an alternative which tolerates missing values and keeps the order initially based on the first file processed.
Enable the '# print' in the middle to see what it is doing.

hth,
Hein.

use strict
my ($name,$value,$param,$params,$host,$hosts,$line,@data);
while (<>) {
chomp;
($name,$value) = split /,/;
next unless $name;
$hosts++ if /^Parameter,/;
$param = $names{$name};
if (!defined $param){
$names{$name} = ++$params;
$param = $params;
$data[$param][0] = $name;
}
$data[$param][$hosts] = $value;
# print "p=$param, h=$hosts, n=$name, v=$value\n";
}
foreach $param (1..$params) {
$_ = "";
foreach $host (0..$hosts) {
$_ .= $data[$param][$host].",";
}
chop;
print "$_\n";
}

[Peter Nikitka]

Hi,

to get the output lines in order with Sandmans awk solution you have to add one additional array only:

awk -F, '{
if(p[$1])
p[$1]=p[$1]","$2
else {key[++j]=$1
p[$1]=$2
}
}
END{for(i=1;i<=j;i++) print key[i]","p[key[i]]}' files ...

mfG Peter

[James R. Ferguson · Accepted solution]

Hi (again):

To add the commas to the output, simply change the line:

print "@{$lines{$parm}}";

...to:

print join ",", @{$lines{$parm}};

A simple hack to keeping the header line (sorted) to the top would be to enclose the first header field in angle brackets, e.g. for your File-1:

,hostname1

Regards!

...JRF...

[Thom Cornwell]

I would suggest possibly using the join command line command