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
4 5274
What have you tried so far?
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
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++;
-
}
-
}
-
}
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
Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
13 posts
views
Thread by Dixie |
last post: by
|
reply
views
Thread by Peter |
last post: by
|
reply
views
Thread by Chris Becker |
last post: by
|
24 posts
views
Thread by clare at snyder.on.ca |
last post: by
|
18 posts
views
Thread by Frank M. Walter |
last post: by
|
11 posts
views
Thread by Tempo |
last post: by
| | | | | | | | | | | | | |