How to get a duplicate rows based on the particular cell value in excel using Perl

[ravir81]

Hi,

I am currently working on excel validation using Perl. I am new to Excel validation but not for Perl. I have a question regarding one of the validation. Could anyone please tell me how to get the number of duplicate rows based on a particular cell value of each these duplicate rows. I mean all the cell values of a row will not be duplicated but a individual columns cell value will be duplicated and I need to create a separate excel with all these duplicates.

Eg :

Name Org Dep Place

RavindraN TCS Development Blore
Ravi TCS Scripting blore
Vimal AOL Development blore
Ram Aol Development blore
Priya aol Scripting blore

Here, I need to get the duplicate rows based on Dep. The output should be :

RavindraN TCS Development Blore
Vimal AOL Development blore
Ram Aol Development blore

and

Ravi TCS Scripting blore
Priya aol Scripting blore

Thanks
Ravi

[nithinpes]

What have you tried so far?

[ravir81]

Hi,

Since I am new to excel validation, I am trying out the how to get the value and display them. I tried to get all the values of a particular cell and store it in a array so that I can try to get a duplicated from that cell.

I need to get the duplicate records based on the column "Dep".

#!/usr/local/bin/perl
 
use strict;
use Spreadsheet::ParseExcel;
 
my %UniqueCell_Row = ();
my $excel_name; 
 
$excel_name = "D:\\RAVINDRAN\\Excel-Email-sending\\name.xls";
print "Excel Name -> <$excel_name> \n";
 
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($excel_name);
 
print "Book -> <$workbook> \n";
my $cont_col;
my $cell_value;
 
### To get the particular columns all the value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    my @cols;
 
    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
        print "Column -> <$col> \n";
        print "$sheet->{Cells}[0][$col]->{Val} \n";
        if ($sheet->{Cells}[0][$col]->{Val} eq "Dep") {
            $cont_col = $col;
            print "Column Number -> $cont_col\n";
            last;
        }
    }
 
    for(
        my $iR = $sheet->{MinRow};
        defined $sheet->{MaxRow} && $iR <= $sheet->{MaxRow}; 
        $iR++
    ){
 
        # fetch columns
        my $cell = $sheet->{Cells}[$iR][$cont_col];
        print "sheet->{Cells}[$iR][$cont_col] \n";
        print "cell->{Val} ----> <$cell->{Val}> \n";
        $cell_value = $cell->{Val};
        push(@cols,$cell_value);
        #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
    }
 
    print "Colllummmn : <@cols> \n";
}
 
## This is just to print all the columns value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
 
            my $cell = $sheet->{Cells}[$row][$col];
            my $cell_value = $cell->{Val};
 
            if ( $cell ) {
                printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                $UniqueCell_Row{$row}{$col} = $cell_value;
            }
            if ( $cell && $cell_value eq "Dep" ) {
                printf("Department Cell Value is : ( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
            }
        }
    }
}
 
## This is just to get the values of the record
my @row_cont = ();
foreach my $row ( sort keys %UniqueCell_Row ) {
    foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
        print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cell} \n";
    }
}

Could you please help me on this.

Thanks

[ravir81]

Hi,

I have taken the duplicate records based on the particular cell value. I need to created excel file for all the duplicate records. Here I am facing problem with writing the header only once for all the new excels created here.

Here is the updated code :

#!/usr/local/bin/perl
 
use strict;
use Spreadsheet::ParseExcel;
use Spreadsheet::WriteExcel;
 
my %UniqueCell_Row = ();
my $excel_name; 
 
$excel_name = "D:\\RAVINDRAN\\Excel-Email-sending\\name.xls";
print "Excel Name -> <$excel_name> \n";
 
my $workbook = Spreadsheet::ParseExcel::Workbook->Parse($excel_name);
 
print "Book -> <$workbook> \n";
my $cont_col;
my $cell_value;
my @cols;
 
### To get the particular columns all the value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
 
    foreach my $col ($sheet->{MinCol} .. $sheet->{MaxCol}) {
        print "Column -> <$col> \n";
        print "$sheet->{Cells}[0][$col]->{Val} \n";
        if ($sheet->{Cells}[0][$col]->{Val} eq "Dep") {
            $cont_col = $col;
            print "Column Number -> $cont_col\n";
            last;
        }
    }
 
    for(
        my $iR = $sheet->{MinRow};
        defined $sheet->{MaxRow} && $iR <= $sheet->{MaxRow}; 
        $iR++
    ){
 
        # fetch columns
        my $cell = $sheet->{Cells}[$iR][$cont_col];
        print "sheet->{Cells}[$iR][$cont_col] \n";
        print "cell->{Val} ----> <$cell->{Val}> \n";
        $cell_value = $cell->{Val};
        push(@cols,$cell_value);
        #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
    }
 
    print "Colllummmn : <@cols> \n";
}
 
## This is just to print all the columns value
foreach my $sheet (@{$workbook->{Worksheet}}) {
    printf("Sheet: %s\n", $sheet->{Name});
    foreach my $row ($sheet->{MinRow} .. $sheet->{MaxRow}) {
        foreach my $col ($sheet->{MinCol} ..  $sheet->{MaxCol}) {
 
            my $cell = $sheet->{Cells}[$row][$col];
            my $cell_value = $cell->{Val};
 
            if ( $cell ) {
                printf("( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                $UniqueCell_Row{$row}{$col} = $cell_value;
            }
            if ( $cell && $cell_value eq "Dep" ) {
                printf("Department Cell Value is : ( %s , %s ) => %s\n", $row, $col, $cell->{Val});
                #push ( @{$UniqueCell_Row{$cell_value}},"$row,$col,$cell_value" );
            }
        }
    }
}
 
## This is just to get the unique record
my @row_cont = ();
foreach my $row ( sort keys %UniqueCell_Row ) {
    foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
        print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cell} \n";
        #if ( $UniqueCell_Row{$row}{$cont_col}
 
    }
}
 
print "========================================= \n";
 
my @Dup;
my %uniqISU = ();
my $duplicate;
my $unique;
 
foreach my $row ( sort keys %UniqueCell_Row ) {
    foreach my $cell ( sort keys %{$UniqueCell_Row{$row}} ) {
        print "Rows and Column -----> <$row> | <$cell> | $UniqueCell_Row{$row}{$cont_col} \n";
        if ( grep( /$UniqueCell_Row{$row}{$cont_col}/, @Dup ) ) {
            print "Present : <$UniqueCell_Row{$row}{$cont_col}> | <@cols> \n";
            $duplicate = $UniqueCell_Row{$row}{$cont_col};
            $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
        }
        else {
            print "NOT PRESENT : <$UniqueCell_Row{$row}{$cont_col}> \n";
            push @Dup, $UniqueCell_Row{$row}{$cont_col};
            $duplicate = $UniqueCell_Row{$row}{$cont_col};
            $uniqISU{$duplicate}{$row}{$cell} = $UniqueCell_Row{$row}{$cell};
        }
    }
}
 
print "***************************************************** \n";
foreach my $cell_val ( sort keys %uniqISU ) {
    print "Cell Value ---> <$cell_val> \n";
    foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
        foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
            print "Cell_val -> <$cell_val> | rows -> <$rows> | columns -> <$cell> | Col Val -> <$uniqISU{$cell_val}{$rows}{$cell}> \n";
        }
    }
}
 
my $format;
my ($header1,$header2,$header3);
my %header;
 
foreach my $cell_val ( sort keys %uniqISU ) {
    my $header = 1;
    my $workbook;
    my $sheet1;
 
    if ( $cell_val ne "Dep" ) {
        $workbook = Spreadsheet::WriteExcel->new("$cell_val.xls");
        $sheet1 = $workbook->add_worksheet("$cell_val");
 
        # Add a Format
        $format = $workbook->add_format();
        $format->set_bold();
        $format->set_size(12);
        $format->set_color('blue');
        $format->set_align('center');
 
        $sheet1->set_column(0, 0);
    }
 
 
    foreach my $rows ( sort keys %{$uniqISU{$cell_val}} ) {
        foreach my $cell ( sort keys %{$uniqISU{$cell_val}{$rows}} ) {
            if ( $header == 1 && $cell_val eq "Dep" ) {
                print "Header Detail ************ $rows, $cell, $uniqISU{$cell_val}{$rows}{$cell}\n";
                $header{$cell_val}{$rows}{$cell} = $uniqISU{$cell_val}{$rows}{$cell};
            }
            elsif ( $cell_val ne "Dep" ) {
                print "$header{$cell_val},$header{$cell_val}{$rows},$header{$cell_val}{$rows}{$cell} \n";
                $sheet1->write($header{$cell_val},$header{$cell_val}{$rows},$header{$cell_val}{$rows}{$cell}) if ( $header == 1 );
                $sheet1->write($rows, $cell, $uniqISU{$cell_val}{$rows}{$cell});
            }
            $header++;
        }
    }
}

[kogantivijay]

hi ,

Just want to let you know that the code that you have pasted helped me understand how to solve one of my problem..i was new to perl and wondering where/how to start and it gave me a good starting point..

thanks,
vijay