Beefy Boxes and Bandwidth Generously Provided by pair Networks
Perl Monk, Perl Meditation
 
PerlMonks  

Parsing error

by MoodyDreams999 (Beadle)
on Jan 30, 2023 at 18:01 UTC ( [id://11150034]=perlquestion: print w/replies, xml ) Need Help??

MoodyDreams999 has asked for the wisdom of the Perl Monks concerning the following question:

I keep running into an uninitialized error that doesnt make sense to me, I should be writing the state in column 6, but I keep getting
Use of uninitialized value $t[6] in string eq at Project1_6.pl line 48 +, <FH> line 2551. Use of uninitialized value $t[6] in string eq at Project1_6.pl line 42 +, <FH> line 2552.
Used perl c- it runs, says syntax is okay I tried declaring $t in line 35, "$worksheet->write($rowCount1+1, $_, my $t[$_]);"
Perl C- said that was okay, but I get "syntax error at Project1_6.pl line 37, near "$t[""
use Excel::Writer::XLSX; use strict; use warnings; my $rowCount = 0; my $filename = "Output2022.xlsx"; my $workbook = Excel::Writer::XLSX->new( $filename ); open(FH, "<", "SRC185.xlsx" ) or die; my $worksheet = $workbook->add_worksheet('List'); $worksheet->write(0, 0, "source_id" ); $worksheet->write(0, 1, "first_name" ); $worksheet->write(0, 2, "middle" ); $worksheet->write(0, 3, "last_name" ); $worksheet->write(0, 4, "address1" ); $worksheet->write(0, 5, "city"); $worksheet->write(0, 6, "state"); $worksheet->write(0, 7, "postal_code"); $worksheet->write(0, 8, "phone_number"); $worksheet->write(0, 9, "address3"); $worksheet->write(0, 10,"province"); $worksheet->write(0, 11, "email"); my $rowCount1 = 0; my $t=0; my @z = 90005; while (<FH>){ chomp; my @t=(',',$_); if(defined($t[8])){ my $Count=0; $worksheet->write($rowCount1+1, $_, $t[$_]); $Count++; } elsif($t[6] eq "CA" && $t[7] eq !defined) { $worksheet->write($rowCount1+1, 7, $z[7]); } $rowCount1++; } $workbook->close(); close(FH);

Replies are listed 'Best First'.
Re: Parsing error
by pryrt (Abbot) on Jan 30, 2023 at 19:15 UTC
    You said,
    open(FH, "<", "SRC185.xlsx" ) or die; ... while (<FH>){ chomp; ...

    An XLSX is a binary file. You cannot just read it line-by-line as if it was newline-separated text and assume you're going to get meaningful data. To read through the rows of SRC185.xlsx, I recommend using the Spreadsheet::Read module -- its documentation shows examples of how to use it to read the various cells of your input spreadsheet. (And even if it were a text-based CSV file instead of XLSX, which is newline-separated text, you would have to somehow split the cells apart to become multiple individual pieces of data; and splitting CSV manually is A Bad Idea, so using Spreadsheet::Read or Text::CSV would have been the better choice for parsing even SRC185.csv.)

    Also, the Anonymous Monk had good information in these posts.

    addenda: BTW: kudos on use strict and use warnings -- however, since you are opening files, I highly recommend meanful error messages: saying open(...) or die; is not a helpful message; using open(...) or die "SRC185.xlsx: $!" is better; but if you 'use autodie; as well, then you don't even need to manually die with a self-crafted error message, so you can just open(...) and be done with it.

      I can't use Spreadsheet::Read as I have to input missing zip codes, I didn't see any documentation about writing in that module. Text::CVS I don't really need it to be cvs, I'll check it out, thank you for the help.
        I can't use Spreadsheet::Read as I have to input missing zip codes

        You apparently misunderstood me. I wasn't saying "use Spreadsheet::Read instead of Excel::Writer::XLSX ". I was saying "use Spreadsheet::Read instead of using a manual open and a while(<FH>) loop to read the old spreadsheet in, and continue to use Excel::Writer::XLSX as the way to write your new file out".

        I just brought up Text::CSV (*) because you seemed to be treating the spreadsheet you were reading as a text file, and CSV is the only pure-text spreadsheet format that I know about, so I thought maybe you were confused about file formats. Since your input file is actually named SRC185.xlsx then it is not a CSV file and you cannot use Text::CSV . (*: CSV is a text-based spreadsheet; CVS is a pharmacy chain or a version-control system that served as a precursor to SVN; VCS is the generic abbreviation for version control software; there are too many valid anagrams of that TLA.)

        So in case it wasn't stated succinctly enough yet:

Re: Parsing error
by tangent (Parson) on Jan 31, 2023 at 00:27 UTC
    I'm assuming you have an existing spreadsheet which you need to update and write to a new file. I have two generic subroutines I use to read/write Excel files. I would do it this way:
    1. Read in all rows from existing file
    2. Update the rows
    3. Write the updated rows to new file
    use strict; use warnings; use Spreadsheet::ParseXLSX; use Excel::Writer::XLSX; my $in_file = "SRC185.xlsx"; my $out_file = "Output2022.xlsx"; # Read in all rows from existing file my $rows = read_excel($in_file); my $headers = [ "source_id", "first_name", "middle", "last_name", "address1", "city", "state", "postal_code", "phone_number", "address3", "province", "email", ]; my $CA_zip = 90005; # Update the rows for my $row (@$rows) { if ($row->[6] eq 'CA' && ! $row->[7]) { $row->[7] = $CA_zip; } } # add headers as first row unshift(@$rows, $headers); # Write the updated rows to new file my $col_num = scalar @$headers - 1; write_excel($out_file, $rows, $col_num); sub read_excel { my ( $file, $sheet ) = @_; $sheet ||= 0; my $parser = Spreadsheet::ParseXLSX->new(); my $workbook = $parser->parse($file); if ( not defined $workbook ) { die $parser->error; } my $worksheet = $workbook->worksheet($sheet); my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); my @rows; for my $row ( $row_min .. $row_max ) { my @cells; for my $col ( $col_min .. $col_max ) { my $cell = $worksheet->get_cell( $row, $col ); if (not $cell) { push(@cells,''); next; } my $value = $cell->value(); push(@cells,$value); } push(@rows,\@cells); } return \@rows; } sub write_excel { my ( $file, $rows, $col_max ) = @_; my $workbook = Excel::Writer::XLSX->new( $file ); if ( not defined $workbook ) { die "Could not open file: $!"; } my $worksheet = $workbook->add_worksheet(); my $row_num = 0; for my $row ( @$rows ) { for my $col (0 .. $col_max) { $worksheet->write( $row_num, $col, $row->[$col] ); } $row_num++; } $workbook->close(); return; }
      That's exactly what I'm trying to do and I noticed you used functions calls for this, I haven't played around with that type of data structure much so, much appreciated!!
      That's exactly what I'm trying to do and I noticed you used functions calls for this, I haven't played around with that type of data structure much so, much appreciated!!
Re: Parsing error
by Anonymous Monk on Jan 30, 2023 at 19:00 UTC

    $t[6] means the 7th item of the array @t. But you declared my @t=(',',$_); which only has 2 items ( $t[0] and $t[1] ). So when you try $t[6] eq "CA" then perl says it's uninitialized because that item does not exist in that list.

    The code you posted makes no sense. Try to start from scratch and organize your thoughts, using pen and paper if necessary, before and while writing the code. You're making some progress. Keep trying, and organize your code:

    my $input = "SRC185.xlsx"; my $output = "Output2022.xlsx"; my $workbook = Excel::Writer::XLSX->new( $output ); my $worksheet = $workbook->add_worksheet('List'); my $rowCount = 0; open(FH, "<", $input ) or die "Can't open $input: $!";

    <P> tags added by GrandFather to improve readability

    code tags on array elements added by Discipulus

      Also, don't use a BAREWORD filehandle, add $ to make it a variable and declare it with "my":
      open my $FH, "<", $input or die "Can't open $input: $!";
      <P> tags added by GrandFather to improve readability

      No you broke my post and the monastery ate the brackets!
      It's supposed to look like this, please stop editing lol:

      $t[6] means the 7th item of the array @t. But you declared my @t=(',',$_); which only has 2 items ($t[0] and $t[1]). So when you try $t[6] eq "CA" then perl says it's uninitialized because that item does not exist in that list. The code you posted makes no sense. Try to start from scratch and organize your thoughts, using pen and paper if necessary, before and while writing the code. You're making some progress. Keep trying, and organize your code: my $input = 'SRC185.xlsx'; my $output = 'Output2022.xlsx'; my $workbook = Excel::Writer::XLSX->new( $output ); my $worksheet = $workbook->add_worksheet('List'); my $rowCount = 0; open my $FH, '<', $input or die "Can't open $input: $!";
Re: Parsing error
by exilepanda (Friar) on Jan 31, 2023 at 02:13 UTC
     my @t=(',',$_);
    Do you actually means this?
    my @t = split /,/, $_;

    and...

    ... my @z = 90005; ... ..elsif($t[6] eq "CA" && $t[7] eq !defined) { $worksheet->write($rowCount1+1, 7, $z[7]); ...
    Do you mean ... && ! $t[7]?
    But where is $z[7] came from?
      I suppose that would be another way to write it, but using the bang like that, "&& ! $t[7]" is definitely new to me. "$z[7]" $z is Zipcode, so I was trying to use that varible in the loop like that.

        Do note that "! defined $var" is not the same as "! $var".

        $ perl -Mstrict -wE ' my $x; say "\$x not defined: ", (! defined $x) ? "Y" : "N"; say "\$x not TRUE: ", (! $x) ? "Y" : "N"; $x = 0; say "\$x not defined: ", (! defined $x) ? "Y" : "N"; say "\$x not TRUE: ", (! $x) ? "Y" : "N"; $x = 1; say "\$x not defined: ", (! defined $x) ? "Y" : "N"; say "\$x not TRUE: ", (! $x) ? "Y" : "N"; ' $x not defined: Y $x not TRUE: Y $x not defined: N $x not TRUE: Y $x not defined: N $x not TRUE: N

        — Ken

        I suppose that would be another way to write it
        It is not. But in short, if you want to check if a variable is in false condition, use if (! $var) {..., or  unless ($var) {..., or the example I gave above.
        A false condition includes 0, "" (empty string), and undefined value.

        On the other hand, defined is checking a null condition (not initialized, undefed). In other words, a variable given "" or 0 also consider as defined, because value already inside. And a proper usage on defined should following a variable, like if ( defined $x ). In your code, ... !defined is checking with $_ which is what you read from <FH>, which I believe is not what you wanted.

        "$z[7]" $z is Zipcode...
        That's why I quoted my @z = 90005; in your code snip, as this is not even a proper array, thus there will be no $z[7] existed.

        Finally, please read other's responds, you can't read Excel by simply open.... , not even CSV. Use a proper module for the job.

Re: Parsing error
by MoodyDreams999 (Beadle) on Jan 30, 2023 at 18:56 UTC
    I believe I found the issue to be "my @t=(',',$_);" Do you guys have any advice on how to declare my @t so that I can use to go through each line of the spread sheet, looks like it wasnt reading it right.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://11150034]
Approved by marto
help
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others taking refuge in the Monastery: (6)
As of 2024-03-28 13:45 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found