Compress Rows of Similar Data into One File Using Perl

We have a data file with the following rows of data:

Field1, Field2, Field3, Data
A, B, C, 1
A1, B, C, 2
A, B, C, 2

We need to have some way to process the file so that it combines similar rows of data into one line. So the end result would look like

A, B, C, 3
A1, B, C, 2

Because it adds the 1st and 3rd rows of data together.

We have a file with over two million rows of data but many of them could be combined into one row and are trying to come up with an automated way using Perl to reduce the size of the file before we load it into a database.

Any help would be appreciated or even a suggestion on approach to doing this.
dlnewman70Asked:
Who is Participating?
 
dlnewman70Connect With a Mentor Author Commented:
#use warnings;
#use strict;

use Fcntl ':flock'; # contains LOCK_EX (2) and LOCK_UN (8) constants

$afterfile = $ARGV[1];
 
open (OUTFILE,">>", $afterfile);

my %totals_hash;

while (<>)
{
  chomp;
  my @cols = split /\|/;

  my $key = join '|', @cols[3,6];

  $totals_hash{$key} += $cols[9];
}

foreach (sort keys %totals_hash)
{
  print OUTFILE $_, '|', $totals_hash{$_}, "\n";

}

close(OUTFILE);
0
 
jmcgOwnerCommented:
If you sort the file first, do you get a result that causes all of the "similar" rows to be together? Is a sorted result acceptable?
0
 
ozoCommented:
perl -lne '1..1 and print and next; /(.*),(.*)/ and $r{$1}+=$2;END{print "$_, $r{$_}" for keys %r}' <<END
Field1, Field2, Field3, Data
A, B, C, 1
A1, B, C, 2
A, B, C, 2
END
0
 
ozoCommented:
my @cols = split /\|/;

  my $key = join '|', @cols[3,6];

  $totals_hash{$key} += $cols[9];
Does not match the format you reported:
Field1, Field2, Field3, Data
A, B, C, 1
A1, B, C, 2
A, B, C, 2


Also, you probably want to pop $afterfile off of @ARGV so that it will not be read as part of <>

And Fcntl ':flock';  is unused, did you intend to lock something?
0
 
dlnewman70Author Commented:
Solved the challenge myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.