CODEWITHSUNDEEP
perlduplicatesappend
Kurt W
Kurt W

Reputation: 351

Perl - Need to append duplicates in a file and write unique value only

I have searched a fair bit and hope I'm not duplicating something someone has already asked. I have what amounts to a CSV that is specifically formatted (as required by a vendor). There are four values that are being delimited as follows:

"Name","Description","Tag","IPAddresses"

The list is quite long (and there are ~150 unique names--only 2 in the sample below) but it basically looks like this:

"2B_AppName-Environment","desc","tag","192.168.1.1"
"2B_AppName-Environment","desc","tag","192.168.22.155"
"2B_AppName-Environment","desc","tag","10.20.30.40"
"6G_ServerName-AltEnv","desc","tag","1.2.3.4"
"6G_ServerName-AltEnv","desc","tag","192.192.192.40"
"6G_ServerName-AltEnv","desc","tag","192.168.50.5"

I am hoping for a way in Perl (or sed/awk, etc.) to come up with the following:

"2B_AppName-Environment","desc","tag","192.168.1.1,192.168.22.155,10.20.30.40"
"6G_ServerName-AltEnv","desc","tag","1.2.3.4,192.192.192.40,192.168.50.5"

So basically, the resulting file will APPEND the duplicates to the first match -- there should only be one line per each app/server name with a list of comma-separated IP addresses just like what is shown above.

Note that the "Decription" and "Tag" fields don't need to be considered in the duplication removal/append logic -- let's assume these are blank for the example to make things easier. Also, in the vendor-supplied list, the "Name" entries are all already sorted to be together.

Upvotes: 0

Views: 159

Answers (2)

Borodin
Borodin

Reputation: 126722

This short Perl program should suit you. It expects the path to the input CSV file as a parameter on the command line and prints the result to STDOUT. It keeps track of the appearance of new name fields in the @names array so that it can print the output in the order that each name first appears, and it takes the values for desc and tag from the first occurrence of each unique name.

use strict;
use warnings;

use Text::CSV;

my $csv = Text::CSV->new({always_quote => 1, eol => "\n"});

my (@names, %data);

while (my $row = $csv->getline(*ARGV)) {
  my $name = $row->[0];
  if ($data{$name}) {
    $data{$name}[3] .= ','.$row->[3];
  }
  else {
    push @names, $name;
    $data{$name} = $row;
  }
}

for my $name (@names) {
  $csv->print(*STDOUT, $data{$name});
}

output

"2B_AppName-Environment","desc","tag","192.168.1.1,192.168.22.155,10.20.30.40"
"6G_ServerName-AltEnv","desc","tag","1.2.3.4,192.192.192.40,192.168.50.5"

Update

Here's a version that ignores any record that doesn't have a valid IPv4 address in the fourth field. I've used Regexp::Common as it's the simplest way to get complex regex patterns right. It may need installing on your system.

use strict;
use warnings;

use Text::CSV;
use Regexp::Common;

my $csv = Text::CSV->new({always_quote => 1, eol => "\n"});

my (@names, %data);

while (my $row = $csv->getline(*ARGV)) {
  my ($name, $address) = @{$row}[0,3];
  next unless $address =~ $RE{net}{IPv4};

  if ($data{$name}) {
    $data{$name}[3] .= ','.$address;
  }
  else {
    push @names, $name;
    $data{$name} = $row;
  }
}

for my $name (@names) {
  $csv->print(*STDOUT, $data{$name});
}

Upvotes: 1

Miller
Miller

Reputation: 35198

I would advise you to use a CSV parser like Text::CSV for this type of problem.

Borodin has already pasted a good example of how to do this.

One of the approaches that I'd advise you NOT to use are regular expressions.

The following one-liner demonstrates how one could do this, but this is a very fragile approach compared to an actual csv parser:

perl -0777 -ne '
    while (m{^((.*)"[^"\n]*"\n(?:(?=\2).*\n)*)}mg) {
      $s = $1;
      $s =~ s/"\n.*"([^"\n]+)(?=")/,$1/g;
      print $s
    }' test.csv

Outputs:

"2B_AppName-Environment","desc","tag","192.168.1.1,192.168.22.155,10.20.30.40"
"6G_ServerName-AltEnv","desc","tag","1.2.3.4,192.192.192.40,192.168.50.5"

Explanation:

Switches:

  • -0777: Slurp the entire file
  • -n: Creates a while(<>){...} loop for each “line” in your input file.
  • -e: Tells perl to execute the code on command line.

Code:

  • while (m{^((.*)"[^"]*"\n(?:(?=\2).*\n)*)}mg): Separate text into matching sections.
  • $s =~ s/"\n.*"([^"\n]+)(?=")/,$1/g;: Join all ip addresses by a comma in matching sections.
  • print $s: Print the results.

Upvotes: 1

Related Questions