Hi Mohan, Let me comment on your code below.
On Wed, 13 Jul 2011 22:20:44 +0530 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 Always start with "use strict;" and "use warnings;" : * http://perl-begin.org/tutorials/bad-elements/ > 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); This is the varvarname anti-pattern: http://perl-begin.org/tutorials/bad-elements/#varvarname You really should use a hash here. > > sub ByRegion > { > my @columns=@_; > > if($columns[1] =~ /^$Region[0]$/) You probably want: if ($columns[1] eq $Region[0]) instead. But this will be a given using a hash: http://perl-begin.org/topics/hashes/ > { > $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]$/; Again map the @Status_String to their numeric values using a hash and do: $EastCount[$status_string_to_index{$columns[2]}]++; [More of the same snipped]. > ############# > # 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; This will be better using a hash. And @Region_Total should not be global. > 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 Why is the comment in all-capital-letters? > 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 > (?,?,?,?,?,?,?,?)"; > Your indentation here is misleading. > 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. > Regards, Shlomi Fish -- ----------------------------------------------------------------- Shlomi Fish http://www.shlomifish.org/ Funny Anti-Terrorism Story - http://shlom.in/enemy When a FLOSS developer says they will work on something, he or she means “maybe”. Please reply to list if it's a mailing list post - http://shlom.in/reply . -- To unsubscribe, e-mail: beginners-unsubscr...@perl.org For additional commands, e-mail: beginners-h...@perl.org http://learn.perl.org/
