Beefy Boxes and Bandwidth Generously Provided by pair Networks
Pathologically Eclectic Rubbish Lister

Re: Search Entire Excel Workbook For Text

by sundialsvc4 (Abbot)
on Oct 16, 2012 at 14:01 UTC ( #999339=note: print w/replies, xml ) Need Help??

in reply to (( Resolved )) Search Entire Excel Workbook For Text

This seems to me to be an ideal application of XPath expressions, especially since you say that “E-H have a different XML template to follow.”   (If there are two variations to deal with now, one day there will be three and then four.)   With this approach, the expressions define what you are looking for, and it becomes XPath’s job to find it.   This will probably very-considerably simplify your logic overall, because the structure of your program is no longer matched to that of the file, and does not change as the files inevitably do.   All you need is an expression, perhaps in a list of them, that will succeed for a particular file.   (XML::LibXML is my recommendation but not the only choice.)   Notice also that the structure of the expression does not have to consider the entire structure of the surrounding file, either:   “tell me what you’re looking for, and I can find it, wherever it is.”

Another thread, today, mentioned XML::XSH, which seems to include a shell for applying XPath expressions interactively, among other features.   I haven’t (yet!) looked into that, but I want to include a link to it here anyhow.

  • Comment on Re: Search Entire Excel Workbook For Text

Replies are listed 'Best First'.
Re^2: Search Entire Excel Workbook For Text
by ImJustAFriend (Sexton) on Oct 16, 2012 at 15:29 UTC

    So, I am not familiar with XPath. I have been looking at this since I read your post, and it seems XPath is used to read XML, not Excel. Am I seeing this correctly?

    Thank you!!


      You're mostly correct. Most of your post was talking about XML, which I think could confuse folks about what you're needing help with: XML or Excel.

      What OS are you on? If you're on Windows, do you have Excel installed and available for use? Which Excel version is the file that you're using?

      • As for Excel file type, there's the older .xls version and the newer .xlsx type. Very different structures.
      • From non-Windows OSes and Windows without Excel, you'll probably be interested in Spreadsheet::ParseExcel for the older .xls file type and Spreadsheet::XLSX for the newer .xls.
      • From a Windows system with Excel, I personally would use Win32::OLE to control Excel to access the Excel file.
      • Also, with the .xlsx file type, there's another route to go. I believe that's just a compressed XML file. That means that you can uncompress it and then do XML parsing. If you go that route, then sundialsvc4's response about using XPath type modules for XML parsing now becomes very applicable.

        Thank you for your post and info, dasgar. My sincere apologies for the misleading post, to you and to sundialsvc4.

        I need help with Excel, not the XML. I am looking for a way to search for a particular string within an Excel spreadsheet (say "ce0_ipaddress") in a workbook, hopefully with results like "found ce0_ipaddress in worksheet 'Server1 IP Info' in column 0 of row 35". Is that even possible?

        In answer to your questions:
        OS: Windows XP
        Excel: I have Office 2007 installed and ready
        File format: .xls file, not .xlsx

        Thanks again!!


Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://999339]
[stevieb]: man, I absolutely LOVE driving in the worst winter conditions. In the mountainous roads, there are extremely few (if any) other drivers. I love the challenge. I've been doing extreme off-road my whole life, and as I said, I'm always well...
[stevieb]: ...prepared for even the very worst
Discipulus seems a part of the globe very unfrindly with Kawasaki riders..
[thezip]: My doggies love it when it snows
[perldigious]: As long as you are a good "boy scout" stevieb, more power to you... but I like my Jeep, and don't like the idea of rolling it or crashing it in to anything. :-)
[thezip]: Me, not as much.
[perldigious]: Mine too thezip, especially my German Shepherd. He looks like he is about to die of heat exhaustion all summer, so he goes nuts when winter comes and he can play in the snow.
[stevieb]: my main off-road vehicle nowadays is a strengthened mercedes ML320. I'm very hard on it, but I've never had any real issues at all (I love how small and light it is, and it has a very, very good centre of gravity for how I roll)

How do I use this? | Other CB clients
Other Users?
Others chilling in the Monastery: (11)
As of 2016-12-06 16:50 GMT
Find Nodes?
    Voting Booth?
    On a regular basis, I'm most likely to spy upon:

    Results (112 votes). Check out past polls.