Beefy Boxes and Bandwidth Generously Provided by pair Networks
Think about Loose Coupling

Xlsx to txt

by Nansh (Acolyte)
on Jun 16, 2017 at 13:08 UTC ( [id://1192929]=perlquestion: print w/replies, xml ) Need Help??

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


Can anyone please help me to put all the data from .xlsx file to .txt file

I have struggled a lot

Thanks in Advance.

Replies are listed 'Best First'.
Re: Xlsx to txt
by Corion (Patriarch) on Jun 16, 2017 at 13:14 UTC

    What code have you written and where did it fail for you?

    Also see xlscat in Spreadsheet::Read. It needs some XLS module installed but will tell you so.

Re: Xlsx to txt
by perldigious (Priest) on Jun 16, 2017 at 13:29 UTC

    Hi Nansh,

    One easy way to do this is to save your .xlsx as something like a "text (tab delimited)" type file using Excel and then use Perl to process it as a simple... well tab delimited .txt file. I actually do this a lot.

    If you do want a way to do it with Perl directly, here is some unrelated (and VERY crude) example code of my own using of Spreadsheet::ParseXLSX I wrote as a quick one-off awhile back. It may at least get you started along with the examples provided by the author in CPAN.

    #!/usr/bin/perl use utf8; use 5.022; use strict; use Spreadsheet::ParseXLSX; use HTTP::Tiny; BEGIN { $| = 1; # turn on forced buffer flushing say "Performing overhead tasks..."; $| = 0; # turn off forced buffer flushing } my @input_files = glob "*.xlsx"; die "ABORT: There were no .xlsx files found in the local directory" un +less @input_files; say "Reading Parts List(s)..."; my @parts; foreach my $input_file (@input_files) { my $parser = Spreadsheet::ParseXLSX->new; my $workbook = $parser->parse($input_file); foreach my $worksheet ( $workbook->worksheets() ) { my ( $row_min, $row_max ) = $worksheet->row_range(); my ( $col_min, $col_max ) = $worksheet->col_range(); foreach my $row ( $row_min+1 .. $row_max ) { my $cell = $worksheet->get_cell( $row, $col_min ); next unless $cell; push @parts, $cell->value(); } } } say "Fetching Featured Products..."; my (@products, @links); foreach my $part (@parts) { my $url = '' . $ +part; my $response = HTTP::Tiny->new->get($url); if ( $response->{success} ) { if ( $response->{content} =~ /Featured Product.*?<a href="(.*? +)".*?>(.*?)<\/a>/si ) { say "Success: $part, $2, $1"; push @links, $1; push @products, $2; } else { say "Success: $part, but no Featured Product listed"; push @links, '-'; push @products, '-'; } } else { say "Failed: $part, unable to connect to \"$url\""; } } say "Creating \"Featured_Products.txt\" Output File..."; open(my $out_fh, ">", "Featured_Products.txt") or die "Cannot open \"F +eatured_Products.txt\": $!."; print $out_fh "DK Part Number\tFeatured Product Text\tFeatured Product + Link\n"; print $out_fh "$parts[$_]\t$products[$_]\t$links[$_]\n" foreach (0 .. +$#parts); close $out_fh; say "FINISHED"; END { require ExtUtils::MakeMaker; # placing this require statement here + for convenient copy-paste of this END block code to other programs. ExtUtils::MakeMaker::prompt('---Press Enter to quit---') if ($?); +# stop and prompt for acknowledgement if program exits with a non-zer +o value. }

    EDIT: Whoops, said unrelated when I meant related... not sure what my brain was doing there.

    Just another Perl hooker - But the purist monks keep telling me I should do it for love, not money.
Re: Xlsx to txt
by marto (Cardinal) on Jun 16, 2017 at 13:15 UTC

      ++. It was posted in the wrong section again.

      Please Nansh, I'm sure you've been told this many times, be careful where you post. You've done it right a few times (like your very first post), so clearly you can do it.

Re: Xlsx to txt
by shadowsong (Pilgrim) on Jun 16, 2017 at 13:21 UTC
      That will write an xlsx file, but the OP sounds like he wants to read an xlsx file and write a text file.

Log In?

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

How do I use this?Last hourOther CB clients
Other Users?
Others browsing the Monastery: (8)
As of 2024-05-21 07:53 GMT
Find Nodes?
    Voting Booth?

    No recent polls found