Home: Perl Programming Help: Beginner:
How to append or prepend a string to a variable



Tejas
User

Nov 18, 2014, 11:06 PM


Views: 35328
How to append or prepend a string to a variable

The problem has nt so easy as i have written in the subject

The task is to create a workbook for 14 differennt countries in one go


Code
 
use strict;
use warnings;

use Excel::Writer::XLSX;
use Spreadsheet::XLSX;


my $US_workbook = Excel::Writer::XLSX->new( "US_GL_SL.xlsx");
my $US_GL_Sheet = $US_workbook->add_worksheet("GL_Sheet");
my $US_SL_Sheet = $US_workbook->add_worksheet( "SL_Sheet" );

my $GB_workbook = Excel::Writer::XLSX->new( "GB_GL_SL.xlsx");
my $GB_GL_Sheet = $GB_workbook->add_worksheet("GL_Sheet");
my $GB_SL_Sheet = $GB_workbook->add_worksheet( "SL_Sheet" );

my $DE_workbook = Excel::Writer::XLSX->new( "DE_GL_SL.xlsx");
my $DE_GL_Sheet = $DE_workbook->add_worksheet("GL_Sheet");
my $DE_SL_Sheet = $DE_workbook->add_worksheet( "SL_Sheet" );

my $FR_workbook = Excel::Writer::XLSX->new( "FR_GL_SL.xlsx");
my $FR_GL_Sheet = $FR_workbook->add_worksheet("GL_Sheet");
my $FR_SL_Sheet = $FR_workbook->add_worksheet( "SL_Sheet" );

my $ES_workbook = Excel::Writer::XLSX->new( "ES_GL_SL.xlsx");
my $ES_GL_Sheet = $ES_workbook->add_worksheet("GL_Sheet");
my $ES_SL_Sheet = $ES_workbook->add_worksheet( "SL_Sheet")


Now iam trying to set the columns for all the worksheet in one go appending _workbook with @Orgs value linearly



Code
my @Orgs = ("US","GB","DE","ES","IT","FR","GB","MX","BR","AU"); 

or(my $col = 0 ;$col < scalar @Orgs ; $col++ ){
my $workbook = $Orgs[$col];
$workbook .= "_workbook";
print "$workbook \n";
foreach my $worksheet ($workbook->sheets())
{
$worksheet->set_column( 'A:Q', 20 ); # Column E width set to 20
}
}


Though it is able to convert the variable name to worksheet name and prints = > US_workbook

Error

Quote

Can't call method "sheets" without a package or object reference at ./GL_SL_Final_Test.pl line 48.


What is missing ? Is something wrong in code or in understanding.
Is it possible in any other way to do it one go


And the same isssue with the code below


Code
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>"; 
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";
while (my $my_line = <$gl_fh>) {
my $Sheet = (split /,/, $my_line)[0];
$Sheet .= "_GL_Sheet";
my @gl_line = (split /\|/, $my_line);
for(my $col=0 ;$col < scalar @gl_line ; $col++ ){
$Sheet->write_string ($gl_row, $col, $gl_line[$col],$frmt);
}
$gl_row++;
}



(This post was edited by Tejas on Nov 18, 2014, 11:15 PM)


Zhris
Enthusiast

Nov 19, 2014, 12:31 AM


Views: 35324
Re: [Tejas] How to append or prepend a string to a variable

Hi,

The key issue with both your examples is that you are assigning a variable with a string, but then later using it like an object by attempting to call a method on it, resulting in "Can't call method "sheets" without a package or object reference" error:


Code
my $workbook = $Orgs[$col]; # assigns workbook with string i.e. US. 
...
foreach my $worksheet ($workbook->sheets()) # attempts to call method sheets on workbook string.


The below is completely untested, but is what I think you should be doing instead:


Code
my @orgs = ('US','GB','DE','ES','IT','FR','GB','MX','BR','AU'); # note duplicate gb.  

for my $org ( @orgs )
{
my $workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx");
$workbook->add_worksheet( 'GL_Sheet' );
$workbook->add_worksheet( 'SL_Sheet' );

print "${org}_workbook";

for my $worksheet ( $workbook->sheets( ) )
{
$worksheet->set_column( 'A:Q', 20 ); # Column E width set to 20
}
}


You had alot of duplicate workbook instantiations at the top, 1 for each org, this can be reduced by handling in the org for loop instead. the variable $workbook is then handly an org specific excel writer object.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 12:39 AM)


Tejas
User

Nov 19, 2014, 12:40 AM


Views: 35315
Re: [Zhris] How to append or prepend a string to a variable

Hi Zhris

But i am also trying to read the a file , And depending on the input org, iam trying to write the excel sheet,
How can this be acheived


Code
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";  
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";
while (my $my_line = <$gl_fh>) {
my $Sheet = (split /,/, $my_line)[0];
$Sheet .= "_GL_Sheet";
my @gl_line = (split /,/, $my_line);
for(my $col=0 ;$col < scalar @gl_line ; $col++ ){
$Sheet->write_string ($gl_row, $col, $gl_line[$col],$frmt);
}
$gl_row++;
}


So Through bove code u ve posted, iam able to create 10 excel sheets.
But here i have a file with data in it
And depending on the Org(US,DE,...) value in that file, i have to write data to the correspondig worksheet.

IF ORG IS US , THEN I HAVE TO OPEN USE WORKBOOK AND WRITE DATA TO US WORKSHEET(Here all the names of work sheet are same (gl_shhe and sl_sheet))
How can this be acheived

I would like to have each woksheetwith its org or it ll be tough to find the excat worsheet where i can fill the data depending on th einput i get


(This post was edited by Tejas on Nov 19, 2014, 1:09 AM)


Zhris
Enthusiast

Nov 19, 2014, 1:26 AM


Views: 35305
Re: [Tejas] How to append or prepend a string to a variable

Hi,

The below is again untested, I am also inexperienced using Excel::Writer::XLSX therefore my code is likely more convuluted than it could be (I was looking through the documentation to try and find methods to get/set single worksheets and get their max row number, with no luck). It creates a single workbook, then worksheets are added as and when an org is seen. Worksheets and row numbers are held in the worksheets state hash.


Code
my %worksheets; 

my $workbook = Excel::Writer::XLSX->new( 'workbook.xlsx' );

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

while ( my $line = <$gl_fh> )
{
chomp $line;

my ( $org, @data ) = split /,|\|/, $line;

my ( $worksheet, $row );

if ( defined $worksheets{$org} )
{
$worksheet = $worksheets{$org}{worksheet};
$row = ++$worksheets{$org}{row};
}
else
{
$worksheet = $worksheets{$org}{worksheet} = $workbook->add_worksheet( "${org}_GL_Sheet" );
$row = $worksheets{$org}{row} = 0;
}

for my $col ( 0 .. $#data )
{
$worksheet->write_string( $row, $col, $data[$col], $frmt );
}
}

close $gl_fh;

__DATA__
US,one|two|three
GB,four|five|six


I'm still not certain this is what you desire, but appears to be more fitting based on your most recent description.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 2:26 AM)


Tejas
User

Nov 19, 2014, 1:28 AM


Views: 35301
Re: [Zhris] How to append or prepend a string to a variable

The requirement is such that i need a seperate workbook per org
not worksheets

I have to create 10 workbooks , with 2 worksheets each

Depeing on the input from a file , i decide which file has to be printed with these values

Thanks
Tejas


Zhris
Enthusiast

Nov 19, 2014, 1:35 AM


Views: 35298
Re: [Tejas] How to append or prepend a string to a variable

Could you provide your input data, I made a guess based on your code, then explain how we decide which of the two worksheets to write to.

"AND WRITE DATA TO US WORKSHEET" threw me, I assumed you needed a worksheet for each org.

Chris


(This post was edited by Zhris on Nov 19, 2014, 1:38 AM)


Tejas
User

Nov 19, 2014, 1:51 AM


Views: 35294
Post deleted by Tejas

 


Zhris
Enthusiast

Nov 19, 2014, 2:20 AM


Views: 35290
Re: [Tejas] How to append or prepend a string to a variable

Hi,

I am by no means 100% clear. Careful of your namespacing, i.e. workbooks, books, worksheets, sheets, tabs etc, its very confusing when you say worksheet but you mean workbook etc. The most useful thing you could do would be to attach example input and the resultant xlsx output.

Here is another untested attempt. It creates a workbook for each unique org found, stores this in the workbooks state hash along with the gl worksheet and row number and sl worksheet and row number. It proceeds to write the data to the gl worksheet only as per your code:


Code
my %workbooks; 

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

while ( my $line = <$gl_fh> )
{
chomp $line;

my ( $org, @data ) = split /\|/, $line;

my $workbook = $workbooks{$org}{workbook} //= Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
my $gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet} //= $workbook->add_worksheet( 'GL_Sheet' );
my $gl_row = $workbooks{$org}{worksheets}{gl}{row} += ( defined $workbooks{$org}{worksheets}{gl}{row} ) ? 1 : 0;
my $sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet} //= $workbook->add_worksheet( 'SL_Sheet' );
my $sl_row = $workbooks{$org}{worksheets}{sl}{row} += ( defined $workbooks{$org}{worksheets}{sl}{row} ) ? 1 : 0;

for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

close $gl_fh;

__DATA__
US|one|two|three
GB|four|five|six


Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 3:01 AM)


Tejas
User

Nov 19, 2014, 3:24 AM


Views: 35263
Re: [Zhris] How to append or prepend a string to a variable

As per xlsx terminology
A workbook is a new excel file
And Worksheet is a tab in that excel file


I need 14 Diffferent excel files , with two tabs in each file

Is it clear ?
Thanks
Tejas


Tejas
User

Nov 19, 2014, 3:36 AM


Views: 35262
Re: [Zhris] How to append or prepend a string to a variable

Hey Zhris

As per xlsx terminology
A workbook is a new excel file
And Worksheet is a tab in that excel file


I need 14 Diffferent excel files , with two tabs in each file

Is it clear ?
Also ,

Quote
Can u please explain your code line by line.
As u have used shortest way , i dint undertand a bit

Also tell me what //= , ||= mean in perl..


And your code throws following error, i really dint understand why :(
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 30.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 31.
Search pattern not terminated at ./Test.pl line 33.



Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 3:47 AM)


Zhris
Enthusiast

Nov 19, 2014, 3:57 AM


Views: 35257
Re: [Tejas] How to append or prepend a string to a variable

Hi,

With regards to terminology, I was thrown off most recently by "I print to GL_Tab of the US Excel sheet", whereas I assumed you meant "I print to GL worksheet of the US workbook" based on your other descriptions. Inevitably if I had assumed you did meant "US worksheet", the resultant script would be very different.


Quote
I need 14 Diffferent excel files , with two tabs in each file


My most recent code creates a different excel file (workbook) per org seen, if there are fourteen unique orgs then it certainly will create fourteen different excel files (workbooks). Two tabs (worksheets) are created per workbook (gl and sl).


Quote
Can u please explain your code line by line.


I will post a new version and include comments.


Quote
As u have used shortest way , i dint undertand a bit


The short way of reading / constructing the state hash is an alternative way to the way I used in my code prior to that. It is indeed confusing whats going on, I'll revert to a longer form thats easier to follow rather than explain.


Quote
Also tell me what //= , ||= mean in perl..


//= means assign the left side the right side value unless the left side is already defined.

||= means assign the left side the right side value unless the left side is already true.


Code
my $var1 = undef; # assign $var1 an undefined value. 
$var1 //= 'hello'; # $var1 becomes hello because it is/was undefined.
$var1 //= 'world'; # $var1 remains hello because it is/was defined.

my $var2 = 0; # assign $var2 an untrue value.
$var2 ||= 1; # $var1 becomes 1 because it is/was untrue.
$var2 ||= 2; # $var1 remains 1 because it is/was true.



Quote
And your code throws following error, i really dint understand why :(
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 30.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 31.
Search pattern not terminated at ./Test.pl line 33.


I'm not certain why you are getting global symbol errors. Could you post your full code and I can see why. And the search pattern not terminated could be because you are using an older version of Perl that doesn't support the //, //= notation, I will remove in my new version.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 4:41 AM)


Tejas
User

Nov 19, 2014, 4:03 AM


Views: 35254
Re: [Zhris] How to append or prepend a string to a variable

Hi

I have attached an excel sheet (A work book) and 14 such have to be created
If u can see insode , there are 2 tabs (i.e 2 worksheets , a tab can also be called a worksheet in a workbook)


I tried running your code
and below are errors


Quote
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 29.
Global symbol "$workbook" requires explicit package name at ./Test.pl line 30.
Search pattern not terminated at ./Test.pl line 32.


Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 4:04 AM)
Attachments: US_GL_SL.xlsx (8.22 KB)


Zhris
Enthusiast

Nov 19, 2014, 4:23 AM


Views: 35251
Re: [Tejas] How to append or prepend a string to a variable

As per above:


Code
# define workbooks state hash.  
my %workbooks;

# open input filehandle.
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

# read input data.
while ( my $line = <$gl_fh> )
{
# remove newline character from end of line.
chomp $line;

# split the line up into org and column data.
my ( $org, @data ) = split /\|/, $line;

# define variables we require in order to write column data to appropriate worksheet.
my ( $workbook, $gl_sheet, $gl_row, $sl_sheet, $sl_row );

# we have seen this org before.
if ( defined $workbooks{$org} )
{
# assign each variable FROM the workbook state hash. Increment row numbers.
$workbook = $workbooks{$org}{workbook};
$gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet};
$gl_row = ++$workbooks{$org}{worksheets}{gl}{row};
$sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet};
$sl_row = ++$workbooks{$org}{worksheets}{sl}{row};
}
# we haven't seen this org before.
else
{
# assign each variable FROM the instantiated Excel::Writer::XLSX object. 0 row numbers.
$workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
$gl_sheet = $workbook->add_worksheet( 'GL_Sheet' );
$gl_row = 0;
$sl_sheet = $workbook->add_worksheet( 'SL_Sheet' );
$sl_row = 0;

# assign each variable TO the workbook state hash, in preparation for the next time we see this org.
$workbooks{$org}{workbook} = $workbook;
$workbooks{$org}{worksheets}{gl}{worksheet} = $gl_sheet;
$workbooks{$org}{worksheets}{gl}{row} = $gl_row;
$workbooks{$org}{worksheets}{sl}{worksheet} = $sl_sheet;
$workbooks{$org}{worksheets}{sl}{row} = $sl_row;
}

# write column data to appropriate worksheet.
for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

# close input filehandle.
close $gl_fh;



(This post was edited by Zhris on Nov 19, 2014, 4:27 AM)


Zhris
Enthusiast

Nov 19, 2014, 4:27 AM


Views: 35249
Re: [Tejas] How to append or prepend a string to a variable

Please can you post the actual code you ran, I do not receive the same errors running the code I provided.

Chris


Tejas
User

Nov 19, 2014, 4:44 AM


Views: 35243
Re: [Zhris] How to append or prepend a string to a variable

here is the code




Code
my $gl_file  = $ARGV[0]; 
my $gl_fh;


my $workbook = Excel::Writer::XLSX->new( 'workbook.xlsx' );
my $frmt = $workbook->add_format(color => 9,bg_color => 8,pattern => 1,border => 1,font => 'Calibri',size => 9,align => 'center' );
open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

my %workbooks;

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";

while ( my $line = <$gl_fh> )
{
chomp $line;

my ( $org, @data ) = split /;/, $line;

my $workbook = $workbooks{$org}{workbook} //= Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
my $gl_sheet = $workbooks{$org}{worksheets}{gl}{worksheet} //= $workbook->add_worksheet( 'GL_Sheet' );
my $gl_row = $workbooks{$org}{worksheets}{gl}{row} += ( defined $workbooks{$org}{worksheets}{gl}{row} ) ? 1 : 0;
my $sl_sheet = $workbooks{$org}{worksheets}{sl}{worksheet} //= $workbook->add_worksheet( 'SL_Sheet' );
my $sl_row = $workbooks{$org}{worksheets}{sl}{row} += ( defined $workbooks{$org}{worksheets}{sl}{row} ) ? 1 : 0;

for my $gl_col ( 0 .. $#data )
{
$gl_sheet->write_string( $gl_row, $gl_col, $data[$gl_col], $frmt );
}
}

close $gl_fh;


The error

Quote
Global symbol "$gl_sheet" requires explicit package name at ./Test.pl line 36.
Search pattern not terminated at ./Test.pl line 39.



Tejas
User

Nov 19, 2014, 4:48 AM


Views: 35241
Re: [Zhris] How to append or prepend a string to a variable

Sorry but i have two input files
SL and GL

So i will have to read two file

my $gl_file = $ARGV[0];
my $sl_file = $ARGV[1];

open $gl_fh, '<', $gl_file or die "could not open $gl_fh <$!>";
open $sl_fh, '<', $sl_file or die "could not open $sl_fh <$!>";

while ( my $line = <$gl_fh> ) {
//Write data to GL Tab of the excel

}


and

while ( my $line = <$sl_fh> )
{ // Write data to sl taab of excel
}

Did u get this ?


Thanks


Zhris
Enthusiast

Nov 19, 2014, 4:48 AM


Views: 35241
Re: [Tejas] How to append or prepend a string to a variable

That isn't your actual script i.e. you haven't used Excel::Writer::XLSX and line 36 doesn't exist etc.


Zhris
Enthusiast

Nov 19, 2014, 4:49 AM


Views: 35239
Re: [Tejas] How to append or prepend a string to a variable

Yes I understand, lets get it working with just 1 input file for now.


Tejas
User

Nov 19, 2014, 4:51 AM


Views: 35237
Re: [Zhris] How to append or prepend a string to a variable

Hey its working with one input file zhris..
The latest code u ve posted ..works wonders

Now all i have to do is to read the second input file again
and write it to corresponding tab

I hope u r inline, as i undestand its merely confusing .

I think i have to repeat the same code for the other input file too .is that correct ?

You really do have a superb understanding though ur nt into XLSX.

Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 4:52 AM)


Zhris
Enthusiast

Nov 19, 2014, 4:57 AM


Views: 35232
Re: [Tejas] How to append or prepend a string to a variable

Ah, it was perhaps the "Search pattern not terminated at ./Test.pl line 32" error that inadvertedly caused the other errors! Is the formatting working correctly since you applied it to a workbook that we do not use? I am just off out for half an hour, I will get back to you shortly with a hopefully finalised version.

Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 4:58 AM)


Tejas
User

Nov 19, 2014, 5:02 AM


Views: 35229
Re: [Zhris] How to append or prepend a string to a variable

:( sorry i dint get that really

But ur second code works really great
I just have to read a second file and write it to other tab

GL Tab with GL data is done
Have to fill the sl tab


Thanks
tejas


(This post was edited by Tejas on Nov 19, 2014, 5:36 AM)


Tejas
User

Nov 19, 2014, 6:09 AM


Views: 35219
Re: [Zhris] How to append or prepend a string to a variable


Code
while ( my $line = <$gl_fh> ) {  
//Write data to GL Tab of the excel

}


and

while ( my $line = <$sl_fh> )
{ // Write data to sl taab of excel
//Here we have to point to file we have created in above while loop
//Aslo the value will not be direcly in the file
if ($org eq '04' || $org eq '2Y' || $org eq '2R' || $org eq '3S' || $org eq '79' || $org eq '3T' || $org eq '3M' || $org eq '2H'){
$org = "US " ; }

//And then we have to write the data to Excel sheet created in the above while loop to a SL_Tab

}


I think its too confusing

But all in all, i have to read two input file and write data accordingly to thet correspoding Excel sheets tab.

And condition being we dont get the org id as we got in first file


Zhris
Enthusiast

Nov 19, 2014, 6:18 AM


Views: 35219
Re: [Tejas] How to append or prepend a string to a variable

Hi,

Again I feel that as I am unfamiliar with the module and I couldn't find everything I needed via the documentation, the code is probably more convuluted than it could be i.e. I couldn't find a method to get the format from the workbook, I couldn't find a method to read a single identifiable worksheet from the workbook and I couldn't find a method to get the last row number of the worksheet, therefore this information is stored and fetched from the workbooks state hash.

The below reads the gl and sl files from args and uses their data to build the workbooks. I have once again not tested the code below but I'm sure you'll let me know if there are any issues. This is a standalone script, run it as it is, do not try to fit it in somewhere in your existing code:

$perl Test.pl

Code
use strict; 
use warnings;
use Excel::Writer::XLSX;

my %workbooks;

my %input =
(
GL =>
{
path => $ARGV[0],
delim => '|',
org_map => undef,
},
SL =>
{
path => $ARGV[1],
delim => ';',
org_map =>
{
'3H' => 'US',
'04' => 'US',
'2R' => 'US',
'6H' => 'GB',
}
}
);

while ( my ( $worksheet_id, $worksheet_info ) = each ( %input ) )
{
my $path = $worksheet_info->{path};
my $delim = $worksheet_info->{delim};
my $org_map = $worksheet_info->{org_map};

open my $handle, '<', $path or die "could not open '$path': $!";

while ( my $line = <$handle> )
{
chomp $line;

my ( $org, @data ) = split /\Q$delim\E/, $line;

$org = $org_map->{$org} if ( defined $org_map and exists $org_map->{$org} );

my ( $workbook, $format );
if ( defined $workbooks{$org} )
{
$workbook = $workbooks{$org}{workbook};
$format = $workbooks{$org}{format};
}
else
{
$workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" );
$format = $workbook->add_format( color => 9,
bg_color => 8,
pattern => 1,
border => 1,
font => 'Calibri',
size => 9,
align => 'center' );

$workbooks{$org}{workbook} = $workbook;
$workbooks{$org}{format} = $format;
}

my ( $worksheet, $row );
if ( defined $workbooks{$org}{worksheets}{$worksheet_id} )
{
$worksheet = $workbooks{$org}{worksheets}{$worksheet_id}{worksheet};
$row = ++$workbooks{$org}{worksheets}{$worksheet_id}{row};
}
else
{
$worksheet = $workbook->add_worksheet( "${worksheet_id}_Sheet" );
$row = 0;

$workbooks{$org}{worksheets}{$worksheet_id}{worksheet} = $worksheet;
$workbooks{$org}{worksheets}{$worksheet_id}{row} = $row;
}

for my $col ( 0 .. $#data )
{
$worksheet->write_string( $row, $col, $data[$col], $format );
}
}

close $handle;
}


Regards,

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:36 AM)


Tejas
User

Nov 19, 2014, 6:22 AM


Views: 35216
Re: [Zhris] How to append or prepend a string to a variable

Zhris,

The two input files are delimited with diffrent demlimiters
dur to some constraints

Second file is delimited with ; as the values might contain | in it

ex: my|tranasaction|success can be a value

And reading a second file isnt staright forward , it doesnt hasve ORG code in it
we have to match it to the exisiting sheet like this, which is failing

Code
 my ( $org, @data ) = split /\|/, $line; 
if ($org eq '04' || $org eq '2Y' || $org eq '2R' || $org eq '3S' || $org eq '79' || $org eq '3T' || $org eq '3M' || $org eq '2H'){
$org = "1" ; }

Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 6:26 AM)


Zhris
Enthusiast

Nov 19, 2014, 6:35 AM


Views: 35209
Re: [Tejas] How to append or prepend a string to a variable

I have updated the code above to allow for a different deliminator per file. For simplicity, the file information is now hardcoded in the script rather than fetched from args.


Quote
And reading a second file isnt staright forward , it doesnt hasve ORG code in it
we have to match it to the exisiting sheet like this, which is failing


Looks like you have this covered by chucking a set of conditions in beneath the split, although a lookup hash, perhaps "regexp => org" pairs would be nicer.

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:24 AM)


Tejas
User

Nov 19, 2014, 6:41 AM


Views: 23043
Re: [Zhris] How to append or prepend a string to a variable

error at split

Quote
Use of uninitialized value in regexp compilation at ./Test_Stand.pl line 48, <$handle> line 2419.
Use of uninitialized value in regexp compilation at ./Test_Stand.pl line 48, <$handle> line 2420.


Thanks


Zhris
Enthusiast

Nov 19, 2014, 6:46 AM


Views: 23041
Re: [Tejas] How to append or prepend a string to a variable

What are the lines indicated by the errors at lines 2419 and 2420 of the input file it falls over on?


(This post was edited by Zhris on Nov 19, 2014, 6:47 AM)


Tejas
User

Nov 19, 2014, 6:48 AM


Views: 23037
Re: [Zhris] How to append or prepend a string to a variable

when i tried to print the delimiter , its printing space
Actually the input file is ok
And for all the lines its throing the same error
I just pasted a part of it

print " $delim \n";



here are the input file's data , treat all the lines as different,all of them have to be printed


Quote
gl_file.txt

04|2110|0|Failure|TBD
04|2110|0| Settlement|TBD
04|2110|0| Failure|TBD
04|2110|0|Failure|TBD
04|2110|0|Failure|TBD
04|2110|0|Settlement|TBD
04|2110|0|Failed|TBD
04|2110|0|Settlement|TBD
04|2110|0|lure|TBD
04|2110|0|TBD


sl_file.txt

1;04;1;Sale
1;2R;1;Sale
1;04;1;Sale
1;2R;1;Sale
1;04;3;Refund
1;04;4;Refund



(This post was edited by Tejas on Nov 19, 2014, 6:53 AM)


Zhris
Enthusiast

Nov 19, 2014, 6:52 AM


Views: 23030
Re: [Tejas] How to append or prepend a string to a variable

I have modified the code again to remove the use of qr, in case its not compatible with your Perl version, can you try again.


Tejas
User

Nov 19, 2014, 7:01 AM


Views: 23027
Post deleted by Tejas

 


Zhris
Enthusiast

Nov 19, 2014, 7:18 AM


Views: 23023
Re: [Tejas] How to append or prepend a string to a variable

Ah, so removing use of the regular expression qr quote operator worked. What version of Perl are you using out of interest.

If the condition is working fine, then you can leave it, but what if org doesn't match either '04', '2Y', '2R', '3S', '79', '3T', '3M' or '2H', how do you handle. Note it will also test this condition for all input files, you might need to chuck in $worksheet_id eq 'SL'. My preference would be to extend the input hash, perhaps add a handler / closure which orgs are run through.


Quote
Can i change this to ARGV[0] and ARGV [1]
I might have more input files to , now they are just 2


You can replace the hardcoded filenames with @ARGV values i.e. path => $ARGV[0],, but if you you add more input files, you would have to extend the %input hash appropriately. If you want to build the %input hash dynamically, then you will have to supply all necessary info via args i.e. --GL.path=..., --GL.delim=..., --SL.path=..., --SL.delim=..., then parse them.

Finally, if you provide your final code once you have finished, I can take a look at it, re-look through the Excel::Writer::XLSX documentation and see if I can tidy / improve it as best as possible. My latest version was rushed.

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:22 AM)


Tejas
User

Nov 19, 2014, 7:23 AM


Views: 23015
Post deleted by Tejas

 


Zhris
Enthusiast

Nov 19, 2014, 7:33 AM


Views: 23007
Re: [Tejas] How to append or prepend a string to a variable

I have edited my latest code again to support mapping of orgs. I used a hash lookup approach, but if the hash got too large, I may take a different approach.

Also, once we have a finalised version I will comment it for you.

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:34 AM)


Tejas
User

Nov 19, 2014, 7:36 AM


Views: 23004
Re: [Zhris] How to append or prepend a string to a variable

Thanks a lot for your support.
i will test it now.
Please explain the code line by line too


Because i see that tab doesnt get created if the data is nt available for a speific file for a specific org, which is great

For example If us has GL data and no sl data, only gl tab is getting created.
By Looking at the code , its termendously generic.

Please explain in line by line


Thanks


Zhris
Enthusiast

Nov 19, 2014, 7:41 AM


Views: 23003
Re: [Tejas] How to append or prepend a string to a variable


Quote
Because i see that tab doesnt get created if the data is nt available for a speific file for a specific org, which is great

For example If us has GL data and no sl data, only gl tab is getting created.


Yup, I made the assumption that this would be desirable, glad it worked out. It wouldn't be difficult to adjust if you ever needed to create both tabs regardless of whether or not they have data.

Once you are happy with the code, please post the version you are using, just in case there are differences, and I will comment it for you.

Chris


(This post was edited by Zhris on Nov 19, 2014, 7:42 AM)


FishMonger
Veteran / Moderator

Nov 19, 2014, 7:47 AM


Views: 22999
Re: [Tejas] How to append or prepend a string to a variable

Chris has done a good job with the xls file creation solution so I'll just point out 1 or 2 things that will help you to write more readable/maintainable code.

When writing for/foreach loops don't use the C style construct. The Perl construct is cleaner and more efficient in its execution. Chris has already given examples using the Perl construct so no need for me to duplicate that example.

Your alternation method for testing the value of $org is overly verbose and doesn't scale well. A better approach would be to put those values into an array and use either the any or first function from the List::Util module to handle the test.
http://search.cpan.org/~pevans/Scalar-List-Utils-1.41/lib/List/Util.pm


Code
my @org_values = qw(04 2Y 2R 3S 79 3T 3M 2H); 

while ( my $line = <$handle> ) {
chomp $line;

my ( $org, @data ) = split /\Q$delim\E/, $line;
$org = 1 if any {$_ eq $org} @org_values;


Another approach would be to build a hash using those values as the keys and use the exists function to do a simple hash lookup. Chris alluded to this in one of the posts.


Code
my %orgs = map {$_ => 1} qw(04 2Y 2R 3S 79 3T 3M 2H); 

while ( my $line = <$handle> ) {
chomp $line;

my ( $org, @data ) = split /\Q$delim\E/, $line;
$org = 1 if exists $orgs{$org};



Tejas
User

Nov 19, 2014, 7:54 AM


Views: 22996
Re: [FishMonger] How to append or prepend a string to a variable

my %orgs = map {$_ => 1} qw(04 2Y 2R 3S 79 3T 3M 2H);


how to have multiple orgs here

Code
my %input = 
( GL =>
{
path => $ARGV[0],
delim => '|',
org_map =>
{
'3H' => '1',
'04' => '1',
'2R' => '1',
'2Y' => '1',
'79' => '1',
'3T' => '1',
'3M' => '1',
'79' => '1' ,
'4R' => '3' , #gb
44 => '5' #de
}
},
SL =>
{
path => $ARGV[1],
delim => ';',
org_map => undef,
}
);


Also can u explain what map does
I have always been doing things in C style, as i dont have a great knowlege on this


Thanks
Tejas


(This post was edited by Tejas on Nov 19, 2014, 7:57 AM)


FishMonger
Veteran / Moderator

Nov 19, 2014, 8:08 AM


Views: 22989
Re: [Tejas] How to append or prepend a string to a variable

If you want separate org_map definitions for GL and SL, then yes, you can define them like that inside your %input hash and the test could be:

Code
$org = $input{GL}{org_map}{$org} if exists $input{GL}{org_map}{$org};



Quote
Also can u explain what map does

See perldoc -f map
http://perldoc.perl.org/functions/map.html


(This post was edited by FishMonger on Nov 19, 2014, 8:12 AM)


Tejas
User

Nov 19, 2014, 8:09 AM


Views: 22988
Re: [Zhris] How to append or prepend a string to a variable

Hi Chris

Quote
1. U have made a great effort to accomplish the imposible task
for me any given day
2. My problem statements and explanations were vague and u have understood them too. :)

Iam attachoing the code.
I ll be glad if you can comment every line.
So thai
i will get knowledge on this particular thing and might be helpful in my future projects.
Attachde :)
Thanks

Tejas


(This post was edited by Tejas on Nov 19, 2014, 8:28 AM)
Attachments: Code.pl (2.61 KB)


Zhris
Enthusiast

Nov 19, 2014, 8:26 AM


Views: 22978
Re: [Tejas] How to append or prepend a string to a variable

Hi,

No problem at all, it can often be difficult to describe the problem you have at hand, if you supply example input and example output that covers every scenario from the beginning, it is alot easier to decipher what needs to be done to get from input to output.

Also, I think you have forgotten to attach your code.

Chris


Tejas
User

Nov 19, 2014, 8:47 AM


Views: 22969
Re: [Zhris] How to append or prepend a string to a variable

There is a small code change i have made

Removed $workbook from while and declared above while
Attachments: Test_Stand.pl (3.49 KB)


Zhris
Enthusiast

Nov 19, 2014, 9:26 AM


Views: 22965
Re: [Tejas] How to append or prepend a string to a variable

Hi,

I have commented the first version you attached. It was actually quite difficult to explain, particularly aspects surrounding the state hash, I have rushed it therefore please raise anything you still struggle to understand. Usually I don't comment my own code, since I'm the only one who ever looks at it, and I break it up into well defined / namespaced functions that are often self explanatory. I definately need more practice in explaining code to others.

If you make changes to the code, please feel free to post the updated version, as promised I will go through at some point and re-write properly.

Chris
Attachments: explained.pl (5.34 KB)


Tejas
User

Nov 19, 2014, 9:48 PM


Views: 22950
Re: [Zhris] How to append or prepend a string to a variable

Thanks Zhris

Next step has been
Now i have to create a third tab (We wont be having an input file for this)

Can u tell me how to create a tab even thgough we do not have data.
Now we are just creating a tab dependent on the input data,
But i need to have a empty tab if data is nt there.
So , i can take a cue form this and create a third tab for comparing.

And i will let you know what has to be done in that tab.

As of now, i can tell you that i will have to
http://perlguru.com/gforum.cgi?do=post_view_flat;post=80220;page=2;sb=post_latest_reply;so=ASC;mh=25;

Create retail, digital and various hashes from the two inputs and calculate the amounts and paste it in third tab.
You can see that in url above


Thanks
Tejas


Zhris
Enthusiast

Nov 20, 2014, 3:49 AM


Views: 22943
Re: [Tejas] How to append or prepend a string to a variable

Hi,


Quote
There is a small code change i have made
Removed $workbook from while and declared above while


Moving the declaration is not a problem, but you later call $workbook->close, the problem with this is that you will only close the last workbook object you dealt with. You can access any of the workbook objects via the workbooks state hash, to close all workbooks:


Code
$workbooks{$_}{workbook}->close for ( keys %workbooks );



Quote
Now i have to create a third tab (We wont be having an input file for this)
Can u tell me how to create a tab even thgough we do not have data.
Now we are just creating a tab dependent on the input data,
But i need to have a empty tab if data is nt there.
So , i can take a cue form this and create a third tab for comparing.


The easiest way would be to create it right after you have instantiated the Excel::Writer::XLSX object then add it to the workbooks state hash if necessary:


Code
$workbook = Excel::Writer::XLSX->new( "${org}_GL_SL.xlsx" ); 
my $compare_worksheet = $workbook->add_worksheet( 'compare_Sheet' );
$workbooks{$org}{worksheets}{compare}{worksheet} = $compare_worksheet;



Regards,

Chris


(This post was edited by Zhris on Nov 20, 2014, 4:46 AM)


Zhris
Enthusiast

Nov 20, 2014, 4:44 AM


Views: 22934
Re: [Tejas] How to append or prepend a string to a variable

Hi,

After reading through the documentation for Excel::Writer::XLSX in more detail, I believe that we have taken the best approach, by storing the state of each workbook inside a hash. As mentioned previously, the module unfortunately doesn't provide a nice interface to manage multiple workbooks, fetch the format from the workbook, manage multiple worksheets within a workbook ( although it could be done via the sheets methods, its easier to deal with sheets by id ), fetch the last row number from a worksheet etc. If I were to rewrite, there would be little difference other than improved namespacing, validation where necessary, and breaking the code up into functions with more specific roles.

We discussed building input dynamically from args, therefore removing the hardcoded input hash. You could write your own input function to parse the args in whatever manner you wish. With regards to mapping orgs, it would be easiest to supply a boolean value which indicates whether orgs should be mapped or not, then hardcode a single mapping hash. A rough example:

$perl input.pl GL.path='gl.txt' GL.delim='|' GL.map_orgs='1' SL.path='sl.txt' SL.delim=';'

Code
use Data::Dumper; 

my $input = input( \@ARGV );

print Dumper $input;

sub input
{
my ( $args ) = @_;

my $input = { };

for my $arg ( @$args )
{
my ( $key, $val ) = split /=/, $arg;
my ( $id, $attr ) = split /\./, $key;

$input->{uc $id}->{lc $attr} = $val;
}

return $input;
}


Code
$VAR1 = { 
'GL' => {
'map_orgs' => '1',
'path' => 'gl.txt',
'delim' => '|'
},
'SL' => {
'path' => 'sl.txt',
'delim' => ';'
}
};


Regards,

Chris


(This post was edited by Zhris on Nov 20, 2014, 5:32 AM)


Tejas
User

Nov 20, 2014, 5:25 AM


Views: 22930
Re: [Zhris] How to append or prepend a string to a variable

Thanks Chris.
I am going ahead with th eprev approach, as i have to send numerous arguements

The next phase os quite tough to deal with
And , i will explain you about that, as explaining it is again hard task .


Thanks
Tejas