Mohan,
I would suggest you learning perl hash: http://perl101.org/hashes.html
#!/usr/bin/perl
use Text::CSV;
use DBI;
use Data::Dumper;
# CONFIG VARIABLES
...
# DATA SOURCE NAME
...
my @regions = ('east','north','south','west');
my @statuses_string = ('pws','open','hold','pwu','reopen');
my %region_data;
foreach my $region (@regions) {
foreach my $status_string (@statuses_string) {
$region_data{$region}{status}{$status_string}{count} = 0;
}
$region_data{$region}{total_count} = 0;
}
print Dumper \%region_data; # inspect the pristine data structure
sub ByRegion
{
my @columns=@_;
$regions{ lc($columns[1]) }{status}{ lc($columns[2]) }c++;
$regions{ lc($columns[1]) }{total_count}++;
}
#############
# reg_id maps:
# 0 -East
# 1 -North
# 2 -South
# 3 -West
############
sub InsertByRegion
{
... I'll leave this to you ...
}
On Wed, Jul 13, 2011 at 10:33 AM, Mohan L <l.mohan...@gmail.com> wrote:
> Dear All,
>
> I have the tab separated csv file with below data.
>
> http://pastebin.com/iDvuhjCc
>
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "Asset" "West" "pws"
> "OnCall" "West" "pws"
> "OnCall" "West" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "pws"
> "OnCall" "South" "Open"
> "Onsite" "South" "Open"
> "Onsite" "South" "Open"
> "Onsite" "South" "Hold"
> "Onsite" "East" "Hold"
> "Onsite" "East" "Hold"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "East" "Open"
> "Remote" "North" "Open"
> "Ven" "North" "Open"
> "Ven" "North" "Open"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Ven" "North" "Hold"
> "Remote" "North" "Hold"
> "Onsite" "North" "Hold"
> "Asset" "North" "Hold"
>
> I have to summarise above date like this:
>
> +--------+-----------+-----+------+------+-----+---------+-------+
> | reg_id | region_id | pws | open | hold | pwu | re_open | total |
> +--------+-----------+-----+------+------+-----+---------+-------+
> | 0 | 1 | 0 | 4 | 2 | 0 | 0 | 6 |
> | 1 | 1 | 0 | 3 | 7 | 0 | 0 | 10 |
> | 2 | 1 | 0 | 3 | 1 | 0 | 0 | 4 |
> | 3 | 1 | 0 | 0 | 0 | 0 | 0 | 0 |
> +--------+-----------+-----+------+------+-----+---------+-------+
>
> Where reg_id is :
> 0 -East
> 1 -North
> 2 -South
> 3 -West
>
> I am a C programmer, but very beginner to perl. I wrote the below lengthy
> script to summarise data in the above format and inserting into mysql
> database.
>
> The function "ByRegion" does the aggregation and summarise data. I think
> there may other way I will achieve it using perl way.
>
> If the below array size increase, my code also will increase:
>
> my @Region=('East','North','South','West');
> my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
>
> I think, I am doing very bad code in function "ByRegion" for aggrication. I
> need someone guide to achive this perl way. Any help will be really
> apricated.
>
> #!/usr/bin/perl
> use Text::CSV;
> use DBI;
>
> # CONFIG VARIABLES
> my $platform = "mysql";
> my $database = "new";
> my $host = "localhost";
> my $port = "3306";
> my $tablename = "by_region";
> my $username = "root";
> my $password = "root123";
>
> # DATA SOURCE NAME
> my $dsn = "dbi:$platform:$database:$host:$port";
>
> my @Region=('East','North','South','West');
> my @Status_String=('Pws','Open','Hold','Pwu','Reopen');
>
> my @EastCount =(0,0,0,0,0);
> my @NorthCount =(0,0,0,0,0);
> my @SouthCount =(0,0,0,0,0);
> my @WestCount =(0,0,0,0,0);
> my @Region_Total=(0,0,0,0);
>
> sub ByRegion
> {
> my @columns=@_;
>
> if($columns[1] =~ /^$Region[0]$/)
> {
> $EastCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
> $EastCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
> $EastCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
> $EastCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
> $EastCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
>
> }
> elsif($columns[1] =~ /^$Region[1]$/)
> {
> $NorthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
> $NorthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
> $NorthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
> $NorthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
> $NorthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
>
> }
> elsif($columns[1] =~ /^$Region[2]$/)
> {
> $SouthCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
> $SouthCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
> $SouthCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
> $SouthCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
> $SouthCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
>
> }
> elsif($columns[1] =~ /^$Region[3]$/)
> {
> $WestCount[0]++ if $columns[2] =~ /^$Status_String[0]$/;
> $WestCount[1]++ if $columns[2] =~ /^$Status_String[1]$/;
> $WestCount[2]++ if $columns[2] =~ /^$Status_String[2]$/;
> $WestCount[3]++ if $columns[2] =~ /^$Status_String[3]$/;
> $WestCount[4]++ if $columns[2] =~ /^$Status_String[4]$/;
>
> }
>
> }
>
> #############
> # reg_id maps:
> # 0 -East
> # 1 -North
> # 2 -South
> # 3 -West
> ############
>
> sub InsertByRegion
> {
> $Region_Total[0]+=$_ foreach @EastCount;
> $Region_Total[1]+=$_ foreach @NorthCount;
> $Region_Total[2]+=$_ foreach @SouthCount;
> $Region_Total[3]+=$_ foreach @WestCount;
> my $region_id=1;
>
> my @data = (
> ['0',$region_id,$EastCount[0],$EastCount[1],$EastCount[2],$EastCount[3],$EastCount[4],$Region_Total[0]],
> ['1',$region_id,$NorthCount[0],$NorthCount[1],$NorthCount[2],$NorthCount[3],$NorthCount[4],$Region_Total[1]],
> ['2',$region_id,$SouthCount[0],$SouthCount[1],$SouthCount[2],$SouthCount[3],$SouthCount[4],$Region_Total[2]],
> ['3',$region_id,$WestCount[0],$WestCount[1],$WestCount[2],$WestCount[3],$WestCount[4],$Region_Total[3]],
> );
>
> ## PERL DBI CONNECT
> my $connect = DBI->connect($dsn, $username, $password);
>
> ### PREPARE THE QUERY
> my $query = "INSERT INTO by_region
> (reg_id,region_id,pws,open,hold,pwu,re_open,total) VALUES
> (?,?,?,?,?,?,?,?)";
>
> my $query_handle = $connect->prepare($query);
> ### EXECUTE THE QUERY
> for my $datum (@data) {
> $query_handle->execute(@$datum);
> }
>
> }
>
> # Over All Pending data File
> my $oapdata='sample.csv';
> my $csv=Text::CSV->new({ sep_char => "\t" });
> open(CSV,"<",$oapdata) or die $!;
> while (<CSV>) {
> if ($csv->parse($_))
> {
> my @columns = $csv->fields();
> ByRegion(@columns);
> }
> else
> {
> my $err = $csv->error_input;
> print "Failed to parse line: $err";
> }
>
>
> }
>
> InsertByRegion;
>
> print "The END!!!\n";
>
> close CSV;
>
>
>
> Thanks for your time.
>
> - Mohan L
>
--
To unsubscribe, e-mail: beginners-unsubscr...@perl.org
For additional commands, e-mail: beginners-h...@perl.org
http://learn.perl.org/
