Re: Script question - merging files based on first parameter (290 Views)
Reply
Regular Advisor
S.Rider
Posts: 216
Registered: ‎02-02-2004
Message 1 of 9 (290 Views)
Accepted Solution

Script question - merging files based on first parameter

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 ?
Ride Boldly Ride, but watch out for El Dorado's
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 2 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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...
Regular Advisor
S.Rider
Posts: 216
Registered: ‎02-02-2004
Message 3 of 9 (290 Views)

Re: Script question - merging files based on first parameter

THANKS - but one dumb perl question.
How do I get the commas between the values outputed ?
Ride Boldly Ride, but watch out for El Dorado's
Honored Contributor
Sandman!
Posts: 2,220
Registered: ‎01-13-2005
Message 4 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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!
Honored Contributor
Sandman!
Posts: 2,220
Registered: ‎01-13-2005
Message 5 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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
Honored Contributor
Hein van den Heuvel
Posts: 6,588
Registered: ‎05-19-2003
Message 6 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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";
}
Honored Contributor
Peter Nikitka
Posts: 1,575
Registered: ‎02-10-2003
Message 7 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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
The Universe is a pretty big place, it's bigger than anything anyone has ever dreamed of before. So if it's just us, seems like an awful waste of space, right? Jodie Foster in "Contact"
Acclaimed Contributor
James R. Ferguson
Posts: 21,184
Registered: ‎07-06-2000
Message 8 of 9 (290 Views)

Re: Script question - merging files based on first parameter

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...
Frequent Advisor
Thom Cornwell
Posts: 63
Registered: ‎05-22-2000
Message 9 of 9 (290 Views)

Re: Script question - merging files based on first parameter

I would suggest possibly using the join command line command
The opinions expressed above are the personal opinions of the authors, not of HP. By using this site, you accept the Terms of Use and Rules of Participation.