Beefy Boxes and Bandwidth Generously Provided by pair Networks
Just another Perl shrine
 
PerlMonks  

XLSX read and dump

by chirp84 (Novice)
on Jan 18, 2012 at 15:52 UTC ( [id://948576]=perlquestion: print w/replies, xml ) Need Help??

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

I am brand new to the perl community and am looking for help with a problem. I have 100+ files in .xlsx format in a folder and I want to dump only cells B37:B45 and C45:R45 from each file into a separate single file. I got the modules set up from CPAN on my new Mac OSX 10.7.2. I have had minor success with printing to an excel file and converting .xlsx to .csv after I saw how good the documentation was for different modules. However, I am not much closer to my end goal and feel like I've waded into the deep end. Can someone help me out please?

Replies are listed 'Best First'.
Re: XLSX read and dump
by talexb (Chancellor) on Jan 18, 2012 at 16:51 UTC

    Have you installed Spreadsheet::XLSX yet? If not, do that, then and read the documentation. It contains a simple program that opens a spreadsheet and dumps the contents. That should provide you with enough information that you'll be able to extract the data you want.

    Jump in, try it out, and let us know where you get stuck!

    Alex / talexb / Toronto

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

      Yes, I started with Spreadsheet::XLSX but got an error as below: IO error: opening test.xlsx for read : No such file or directory at /System/Library/Perl/Extras/5.12/Archive/Zip/Archive.pm line 546 Archive::Zip::Archive::read('Archive::Zip::Archive=HASH(0x7f87628288d8)', 'test.xlsx') called at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33 Spreadsheet::XLSX::new('Spreadsheet::XLSX', 'test.xlsx') called at parse.pl line 6 Cannot open test.xlsx as Zip archive at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33.

        First of all, a meta-comment: you should wrap your errors in code tags to a) make that part of it easier to read and b) make it stand out from the rest of your post. Ideally, the errors would look like

          IO error: opening test.xlsx for read : No such file or directory at /System/Library/Perl/Extras/5.12/Archive/ +Zip/Archive.pm line 546 Archive::Zip::Archive::read('Archive::Zip::Archive=HASH(0x7f87628288d8 +)', 'test.xlsx') called at /Library/Perl/5.12/Spreadsheet/XLSX.pm line 33 Spreadsheet::XLSX::new('Spreadsheet::XLSX', 'test.xlsx') called at parse.pl line 6 Cannot open test.xlsx as Zip archive at /Library/Perl/5.12/Spreadsheet +/XLSX.pm line 33
        I've added a few line breaks to make it easier to read.

        Second, it looks like (and I'm guessing, because you haven't posted any code yet) you are accessing the spreadsheet while it's inside a zip file. To make it easier, can you just extract one of the spreadsheets and operate on that?

        Let's try to solve one problem at a time (aka, "You've got to walk before you can run.")

        Alex / talexb / Toronto

        "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

Log In?
Username:
Password:

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

How do I use this?Last hourOther CB clients
Other Users?
Others surveying the Monastery: (4)
As of 2024-03-29 08:07 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found