I am writing code to parse sales information for a auto generated excel report. I am able to pull the data from each cell for a row and print it to a text file. The issue begins when I attempt to loop reading rows. The spreadsheet is setup with data on every other cell. I attempted to create a for loop to increment the cells but get an error.
syntax error at C:\taylor\perl\test_parse.pl line 128, near ")
my "
Global symbol "$value" requires explicit package name (did you forget to declare "my $value"?) at C:\taylor\perl\test_parse.pl line 128.
Global symbol "$value" requires explicit package name (did you forget to declare "my $value"?) at C:\taylor\perl\test_parse.pl line 129.
Global symbol "$value" requires explicit package name (did you forget to declare "my $value"?) at C:\taylor\perl\test_parse.pl line 168.
Execution of C:\taylor\perl\test_parse.pl aborted due to compilation errors."
I have lookled into moving the variables out of the scope of the if block but am unsure if this is possible.
#!/usr/bin/perl
use warnings;
use strict;
use Spreadsheet::ParseXLSX;
use Spreadsheet::ParseExcel;
use Spreadsheet::XLSX;
use Date::Format;
my $filename = "c:/taylor/perl/DCS8.xlsx";
my $files = "C:\\Taylor\\perl\\imports.csv";
#Parse excel file
my $parser = Spreadsheet::ParseXLSX->new();
my $workbook = $parser->parse("$filename");
for (my $i=0; $i <= 200; $i+=2) {
my $worksheet1 = $workbook->worksheet('Sheet1');
my $a = $worksheet1->get_cell(15+$i,0);
my $d = $worksheet1->get_cell(15+$i,3);
my $f = $worksheet1->get_cell(15+$i,5);
my $h = $worksheet1->get_cell(15+$i,7);
my $j = $worksheet1->get_cell(15+$i,9);
my $l = $worksheet1->get_cell(15+$i,11);
my $n = $worksheet1->get_cell(15+$i,13);
my $p = $worksheet1->get_cell(15+$i,15);
my $r = $worksheet1->get_cell(15+$i,17);
my $t = $worksheet1->get_cell(15+$i,19);
my $v = $worksheet1->get_cell(15+$i,21);
my $x = $worksheet1->get_cell(15+$i,23);
my $z = $worksheet1->get_cell(15+$i,25);
my $ab = $worksheet1->get_cell(15+$i,27);
my $af = $worksheet1->get_cell(15+$i,29);
my $ah = $worksheet1->get_cell(15+$i,31);
my $aj = $worksheet1->get_cell(15+$i,33);
my $ao = $worksheet1->get_cell(15+$i,35);
if (( defined $a and $a->value() ne "")
or ( defined $d and $d->value() ne "")
or ( defined $f and $f->value() ne "")
or ( defined $h and $h->value() ne "")
or ( defined $j and $j->value() ne "")
or ( defined $l and $l->value() ne "")
or ( defined $n and $n->value() ne "")
or ( defined $p and $p->value() ne "")
or ( defined $r and $r->value() ne "")
or ( defined $t and $t->value() ne "")
or ( defined $v and $v->value() ne "")
or ( defined $x and $x->value() ne "")
or ( defined $z and $z->value() ne "")
or ( defined $ab and $ab->value() ne "")
or ( defined $af and $af->value() ne "")
or ( defined $ah and $ah->value() ne "")
or ( defined $aj and $aj->value() ne "")
or ( defined $ao and $ao->value() ne ""))
my $value = $a->value();
$value =~ s/[_, -]//g;
my $value2 = $d->value();
$value2 =~ s/[_, -]//g;
my $value3 = $f->value();
$value3 =~ s/[_, -]//g;
my $value4 = $h->value();
$value4 =~ s/[_, -]//g;
my $value5 = $j->value();
$value5 =~ s/[_, -]//g;
my $value6 = $l->value();
$value6 =~ s/[_, -]//g;
my $value7 = $n->value();
$value7 =~ s/[^a-zA-Z0-9,]//g;
my $value8 = $p->value();
$value8 =~ s/[_, -]//g;
my $value9 = $r->value();
$value9 =~ s/[_, -]//g;
my $value10 = $t->value();
$value10 =~ s/[_, -]//g;
my $value11 = $v->value();
$value11 =~ s/[_, -]//g;
my $value12 = $x->value();
$value12 =~ s/[_, -]//g;
my $value13 = $z->value();
$value13 =~ s/[_, -]//g;
my $value14 = $ab->value();
$value14 =~ s/[_, -]//g;
my $value15 = $af->value();
$value15 =~ s/[_, -]//g;
my $value16 = $ah->value();
$value16 =~ s/[^a-zA-Z0-9,]//g;
my $value17 = $aj->value();
$value17 =~ s/[^a-zA-Z0-9,]//g;
my $value18 = $ao->value();
$value18 =~ s/[^a-zA-Z0-9,]//g;
my $files = "C:\\Taylor\\perl\\imports.csv";
unlink ($files);
open (OUTFILE, ">>$files");
print OUTFILE "$value,$value2,$value3,$value4,$value5,$value6,$value7,$value8,$value9,$value10,$value11,$value12,$value13,$value14,$value15,$value16,$value17,$value18\n";
}
Ideally this would loop through the get cells adding 2 rows each time, instead it seems when the loop begins it breaks.
The solution is already given in the comments, but it might help to make it clearer for anyone else who comes across this problem in the future.
The code contains an if statement that was written like this (simplified massively):
if ($some_condition)
# do something
But in Perl, the code attached to an if statement needs to be in a code block. So it should be written like this:
if ($some_condition) {
# do something
}