Perl : converting file to different scalar elements

Shell Programming and Scripting
1

I have a text file containing 2 exec statements as below and trying to store the below 2 execs into 2 different scalar variables in perl.

/* ICD Dist, Total */  /* need to export to Excel, sheet=ICD_Dist__Total */
exec(
'select sum(count(*)) 
		cast(count(*)*100.0/sum(count(*)) over() as decimal(18,2)) as percent_total
from DETAIL_' + @dataset_id + ' 
 
)


/* ICD Dist, by Month */  /* need to export to Excel */
exec(
'select  serv_yrmo_tot_claims, 
		cast(count(*)*100.0/sum(count(*)) over(partition by left(convert(varchar,SERVICE_DT,112),6)) as decimal(18,2)) as percent_per_mth
from DETAIL_' + @dataset_id + ' 
 
)

final output should be like

$query1 = 'exec(
'select sum(count(*)) 
		cast(count(*)*100.0/sum(count(*)) over() as decimal(18,2)) as percent_total
from DETAIL_' + @dataset_id + ' 
 
)'
$query2 = 'exec(
'select  serv_yrmo_tot_claims, 
		cast(count(*)*100.0/sum(count(*)) over(partition by left(convert(varchar,SERVICE_DT,112),6)) as decimal(18,2)) as percent_per_mth
from DETAIL_' + @dataset_id + ' 
 
)'

tried with few possible regex but couldn't able to make it.
Could anyone please help out in solving this.

2

Hi..

How about dynamic variable assignment.

perl -lne 'if(/exec/){$q="";$c=1;$qc++;}if($c){$q = join(" ", $q, $_);if(/\)$/){$f='query'.$qc;$c=0;$$f=$q;}}END{for($i=1;$i<=$qc;$i++){$f='query'.$i;print $f . "=" . $$f;}}' file_name

-Ranga

1 Like
3

thanks rangarasan .

But could you please make this as a perl program instead of one liner.

Once again thanks again for your great help.

4

by sed

sed -i  -e "s/exec/\$query1=\'exec/gi" -e "s/^)/)\'/gi" -e "s/^\/\*.*//gi" file
1 Like
5

Thank you looney.

If possible could you please help me with the perl code instead of sed.

Thanks much.

6

Hi scriptscript,
You said you had code that was close to working, but you needed help with the REs. Since the code Ranga and looney suggested doesn't meet your needs, please show us what you have tried and maybe we can then help you tweak your code get what you want.

7

Thank you Don for your extended support.

Below is what where I got stuck.

my $count = 0;
open (IN, "filetext.txt");
@array = () ;
while (<IN>) 
{
    if (/exec/) 
    {
	$count = 1;
    }
    elsif (/^\)/) 
    {
	$count = 0;
    }
    elsif ($count) 
    {
	push(@array,$_);

    }

}
close IN;

print "@array";

foreach $line ( @array )
{
        ## not sure what to do next
}

Now @array contains both the query. Now I want to make those queries into two scalars.

Thanks,
John

8

Hi John,

The query strings are already a scalar each time through the loop, they are called $line

If my guess is correct you want to use that $line to execute the query via a system call. Unfortunately, that would be wrong in many ways, and it would take a lot of explanation.

Perhaps, these two links might help you how to use the DBI module to interface with a database.
An Embarrassing Confession
Using Perl DBI

If any of those links are not working, please search for Perl and DBI