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
