Hello Monks... been a visitor for years, and have benefited from the knowledge bestowed upon these boards. I now need to seek some Perl wisdom that I can't find through the search feature of this monastery.

I have a few XML files fed from one Excel spreadsheet, one XML file "template" per device type. We have been manually editing template XML files, but now I want to automate this process. I have code that works fine for one device, but I need to expand the logic to include the other device types (which have different XML templates). I would like to keep this as "one script, one Excel file". To do so, I need to figure out how to search the Excel file, across multiple worksheets, for a specific "name" in the XML then gather the value from the worksheet.

The XML looks like this: 

<module name="Security"> 
    <function name="common"> 
      <option name="SECURITY" value="SECURED"> 
        <configuration>A</configuration>  
        <configuration>B</configuration>  
        <configuration>C</configuration>  
        <configuration>D</configuration>  
        <configuration>E</configuration>  
        <configuration>F</configuration>  
        <default_value>SECURED</default_value>  
        <example>MIXED</example>  
        <comment>Comment 1</comment>  
        <comment>Comment 2</comment>  
        <comment>Comment 3</comment>  
        <site_priority>1</site_priority>  
        <mandatory>NO</mandatory>  
        <level>0</level> 
      </option> 
    </function>
    <function name="ssh">
      <option name="..." value="...">
...

[download]

The worksheets look like this (fake data): 

+-----------+---+---+---+---+---+---+---+---+
| Parameter | A | B | C | D | E | F | G | H |
|-----------+---+---+---+---+---+---+---+---+
| SECURITY  | S | S | S | S | S | S | S | S |
|-----------+---+---+---+---+---+---+---+---+
| PARAM 2   | 1 | 0 | 0 | 0 | 1 | 0 | 1 | 0 |
|-----------+---+---+---+---+---+---+---+---+
...

[download]

There are 12 worksheets, all formatted as above, for the XML creation. Each worksheet has the same number of columns (one per device type), but could have anywhere from 1 to 100 rows of data. A-H in the first row represent different devices for which data would be pulled.

So my script logic currently says "start reading the XML from the top of the file. If the line matches 'option name', pull the data from Excel in worksheet x, row y, column z... else, print the line to the output XML file and read next line." Worksheet x, row y, and column z are all either created from script parameters (column z), or calculated from loop counts.

That code works spectacularly for devices A-D in my Excel mock-up, but E-H have a different XML template to follow. So, I am thinking to change my logic to say something like "start reading the XML from the top of the file. If the line matches 'option name', search through the entirety of the Excel file until the matching parameter name is found and pull the appropriate data... else, print the line to the output XML file and read next line." I have been searching for 3 days for how to search an entire Excel workbook for specific text, but thus far I am not having any luck.

So, Perl Monks... can someone please point me in the right direction for searching an Excel workbook for a specific string?

Thank you very much!!

ImJustAFriend

In reply to (( Resolved )) Search Entire Excel Workbook For Text by ImJustAFriend

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.