-2

I have a perl code that extract from .xls file. My .xls file is as below

NUMBER    NAME    ALPHABET
one       Jane        a
two       Adam        b
three     Josh        c
;four  
five      Agnes       e 
six       Mary        f
;seven 
eight     Lara        h        

I want to extract the info and only take column 1 and 2. My perl code is as below.

     #!/usr/bin/perl

     use warnings;
     use strict;
     use Spreadsheet::ParseExcel;

     main ();

     sub main {

        my $filename = 'Book1.xls';
        my $parser   = Spreadsheet::ParseExcel->new();
        my $workbook = $parser->parse( $filename );

        if ( !defined $workbook ) {
           die "-E-: cannot parse <$filename>.\n ", $parser->error(), ".\n";
        }

        my $worksheet             = $workbook -> Worksheet ( 'a' ) || die "-E-: cannot parse family pin list.\n";   
        my ( $row_min, $row_max ) = $worksheet-> row_range();

        open ( my $file,"> output.txt");

        for my $row ( 1 .. $row_max ) {

           my @data;

           for my $col ( 0 ) {
              my $number = $worksheet-> get_cell( $row, $col );
              if ( $number ) {
                 push @data, $number-> value();
              }
              else {
                 push @data, '';
              }
            }

            for my $col ( 2 ) {
               my $alphabet = $worksheet->get_cell( $row, $col );
               if ( $alphabet ) {
                  push @data, $alphabet->value();
                  print $file  "@data\n";
               }
               else {
               push @data, '';
               }
            }     

     }

     close $file;

      print "done\n";
     }

The result is

    one a
    two b
    three c
    ;four 
    five e
    six f
    ;seven 
    eight h

I want to remove the entire array that start with string ";". I extend my code like below

        open ( my $file,"> output.txt");

        for my $row ( 1 .. $row_max ) {

           my @data;

           for my $col ( 0 ) {
              my $number = $worksheet-> get_cell( $row, $col );
              if ( $number ) {
                 push @data, $number-> value();
              }
              else {
                 push @data, '';
              }
            }

            for my $col ( 11 ) {
               my $alphabet = $worksheet->get_cell( $row, $col );
               if ( $alphabet ) {
                  push @data, $alphabet->value();
               }
               else {
               push @data, '';
               }
            }


      my @new_data = grep(!/;/, @data);
      my @latest_data = grep ( $_ ne '', @new_data);
      print $file  "@latest_data\n";


     }

     close $file;

      print "done\n";
     }

The output result produce like below.

     one a
     two b
     three c

     five e
     six f

     eight h

I don't want to be empty space. How i want to eliminate the empty space that produce result as below?

     one a
     two b
     three c
     five e
     six f
     eight h

I also try doing like this, but the result is same.

     for my $index (reverse 0..$#data) {
        if ( $data[$index] =~ /^;/ ) {
        splice(@data, $index, 1);
       }
     }
     print $file "@data\n";
  • just a quick note that your grep is excluding any element that contains a semicolon anywhere in it; you probably want ... /^;/... – Jeff Schaller Apr 23 at 15:22
  • it doesn't work for me – daffodil Apr 23 at 15:26
  • You're creating empty elements when you do push @data, ''". – JRFerguson Apr 23 at 15:45
  • I already try remove it, it still the same – daffodil Apr 23 at 15:46
  • Hi all, i update my case. Maybe can give you guys clear understanding – daffodil Apr 25 at 1:26
0

You might consider checking column 0 of the current row before iterating over the rest of the columns; if the value starts with a semicolon, skip to the next row:

   # ...

   for my $row ( 1 .. $row_max ) {

       my @data;
       next if $worksheet->get_cell($row, 0) =~ /^;/;

   # ...

That way, you avoid pushing any new (row) data into the array.

  • Thanks for suggestion. It doesn't work for me – daffodil Apr 25 at 2:15
  • In what way does this not work? – Jeff Schaller Apr 25 at 2:17
  • the result is same as my first result (refer to my post) – daffodil Apr 25 at 2:27
  • You inverted the logic, from what I can tell. I skip the line if it matches; you're specifically executing the splice if it matches...? – Jeff Schaller Apr 25 at 10:52
0

If you just want to get that result then you can use awk:

awk '$1 !~ "NUMBER" && $1 !~ "^;" {print $1,$3}' file.xls

That tells it to not print any row that contains the string NUMBER or begins with a ;. It then prints the first and third columns and gives your expected output.

one a
two b
three c
five e
six f
eight h
  • Hi, thanks for suggestion. I need to use perl as in that file have many sheets – daffodil Apr 25 at 2:29
  • @daffodil In that case, your question is better on StackOverflow as it's related to programming than Unix/Linux. – Nasir Riley Apr 25 at 2:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.