File handling

[anunitha]

Hi,
I have excel sheet which contain 100 and 100 of email and corresponding name. I need to seprate them according to the number of occurrence of their name.

eg:

name email

chandran [email][e-mail][/email]
maidhu maidu2yahho.com
karan [email][e-mail][/email]
Chandran [email][e-mail][/email]
Chandran [email][e-mail][/email]
karan [email][e-mail][/email]

excell sheet contain name and email, the work is we should found out

1.Total no of contact in excell sheet.
2. From that total number should find out single contribution(the name of person occurs only once)(only should consider the number time occurrence of persons name).

3.>=2,>=3,.......>=16,(its only up to 16 times occurrence of some name)

the findings should tally with the total number of list.

out put should be in another text

note: the same name will be written in different case,(Chandran,chandran), some time space (Hari chandran or harichandran) like

plz help me solve this problem..manually its difficult to find 1000 of names so plz help to do in programmatic. Because i know some basics in Perl so i can try..

[k_manimuthu]

I assumed your excel file having only one sheet and the data will be two columns.
The first column is the name field and second one is mail ids. Try the below code.

use strict;
use Spreadsheet::ParseExcel;

my $oExcel = new Spreadsheet::ParseExcel;

die "You must provide a filename to $0 to be parsed as an Excel file" unless @ARGV;

my $oBook = $oExcel->Parse($ARGV[0]);
my($iR, $iC, $oWkS, $oWkC);

my %name_list=(); my $count=0; my %occurence=();

for(my $iSheet=0; $iSheet < 1 ; $iSheet++)
{
 $oWkS = $oBook->{Worksheet}[$iSheet];

 for(my $iR = $oWkS->{MinRow} ;
     defined $oWkS->{MaxRow} && $iR <= $oWkS->{MaxRow} ;
     $iR++)
 {
  for(my $iC = $oWkS->{MinCol} ;
      $iC < 1;
      $iC++)
  {
   $oWkC = $oWkS->{Cells}[$iR][$iC];
   if($oWkC) {
	   my $name=lc(cleanup_names($oWkC->Value));
	   $name_list{$name}++; $count++;
	   $occurence{$name_list{$name}}++;
   }
  }
 }
}

print "\nTotal Occurences : $count";

my @values = values %name_list;
foreach my $key (sort {$a<=>$b} keys %occurence ) {
	print "\n\n\nName found at $key time only\n---------------------------"; $count=1;
	foreach my $names ( sort keys %name_list )  {
		if ($name_list{$names} == $key) {
			print "\n$count) $names"; $count++;
		}
	}
}

sub cleanup_names() {
	my ($given_name)=@_;
	$given_name=~ s{^\s+}{};
	$given_name=~ s{\s+$}{};
	$given_name=~ s{\s{2,}}{ };
	return $given_name;
}

[anunitha]

Hi,Thank you for your time and consideration.Actually i tried this program but it showing some error

1. i have saved prog as author.pl
2.excel sheet as email.xls

but it says

can't locate spreadsheet/praseexcell.pm in @INC(@INC contain perl/site/lib C:/perl/lib.) at E:\author.pl line 2
BEGIN failed--- completion aborted at E:\author.pl line 2

Can you please guide me to run this program..

[anunitha]

Hi,Thank you for your time and consideration.Actually i tried this program but it showing some error

1. i have saved prog as author.pl
2.excel sheet as email.xls

but it says

can't locate spreadsheet/praseexcell.pm in @INC(@INC contain perl/site/lib C:/perl/lib.) at E:\author.pl line 2
BEGIN failed--- completion aborted at E:\author.pl line 2

Can you please guide me to run this program..

[anunitha]

Hi,

Thank you So much for helping me to solve the problem. Actually the prob was spreadsheet-ParaseExcell was not installed,after installing the program worked properly. Thanks for your valuable time.