Using an Excel XML File for Input to a Runbook

Hello readers.  I thought it would be a good idea to quickly discuss a topic that came up during a recent Runbook development effort.  I was faced with the challenge of how to do the following:

1. Provide an easy way to request data from an end user
2. Make it familiar and intuitive
3. Provide the data in a format that could be easily consumed by Orchestrator via PowerShell.

Solution?

I opted for a quick and dirty Excel XML file.  Looks exactly like an EXCEL XLS file but based on XML data format.  The benefits from this approach were two fold.  I didn’t have to program a fancy front end interface (more on this in a future blog post however ), and PowerShell could consume this data and immediately turn it into Published Data for the Runbook processing within Orchestrator.

STEP 1

So where do you start?  Start with a basic Excel XML file.  The easiest thing to do is put columns together as shown below that frame up what you are looking to gather.  Highlights can be leveraged as shown to call attention to required data.  In addition, you can even put in some data validation (three letters required, etc.) as well as “hover text” to provide your end user the ability to review helpful tips as they are filling out the required data.

STEP 2

Now that you have a basic template file put together, you can either have the user drop it into a pre-determined (monitored by Orchestrator) input directory and allow automation to pick up this file and appropriately process it.  Or what we did was attach it to a service ticket (in our case Team Foundation Server) and allow Orchestrator to collect up that file from the ticket, process the data inside, and act accordingly (updating the ticket along the way of progress).

STEP 3

So far so good right?  Next, once you have an idea of where you are going to place the file for processing, leverage a simple Run.NET object within Orchestrator to process the file according to cell and row analysis.  The code snippit below basically sets variables in PowerShell according to text values located within the called out row and cell. Then you set returned data within your Runbook to the PowerShell variables.

Example: line 9 below evaluates $table.Row[1].Cell[1].Data."#text"

The value shown in row(1) and cell(1) is “SEC”. In contract row(0) cell(1) is “Values”. So for each row and cell combination you have with data you need, you will set to an appropriate variable that you can leverage within your Runbook inside Orchestrator.

  1: #Setup the file and initialize Excel COM object
  2: $file1 = "{Published Data}"
  3:  
  4: #Read in XML Data
  5: $template  = [xml](Get-Content "$File1")
  6: $table = $template.Workbook.Worksheet[0].Table
  7:  
  8: #Read values and set variables
  9: $DeployType        = $table.Row[1].Cell[1].Data."#text"
  10: $DecomSvr           = $table.Row[2].Cell[1].Data."#text"
  11: $DecomVar           = $table.Row[3].Cell[1].Data."#text"
  12: $NewSvr               = $table.Row[4].Cell[1].Data."#text"
  13: $NewSite              = $table.Row[5].Cell[1].Data."#text"
  14: $ParentSvr           = $table.Row[6].Cell[1].Data."#text"
  15: $CNTSVR              = $table.Row[7].Cell[1].Data."#text"
  16: $NetworkBin         = $table.Row[8].Cell[1].Data."#text"
  17: $LocalBin              = $table.Row[9].Cell[1].Data."#text"
  18: $dpGroupOSD      = $table.Row[10].Cell[1].Data."#text"
  19: $Install_directory = $table.Row[11].Cell[1].Data."#text"
  20: $PKGID                 = $table.Row[12].Cell[1].Data."#text"
  21: $DP_Drive            = $table.Row[13].Cell[1].Data."#text"
  22: $emailAddress      = $table.Row[14].Cell[1].Data."#text"

STEP 4

Last and final step is to set Published Data within your Runbook to the variable data you have set in your Run.NET object.

That’s it!  I’ve provided an example input file and PowerShell script for you to review and play around with.  If you have any questions – as always please don’t hesitate to ask!  Thanks for stopping by and till next time, Happy Automating!

Process XML Example Files Process-XML.zip

Comments

• Anonymous
  January 01, 2003
  The comment has been removed
• Anonymous
  November 01, 2011
  If you have a requirement to use an XLS file you can also directly read XLS files from PowerShell without needing Excel installed by using an ADO connection.   www.codeproject.com/.../PowerShell_Guideline_p3.aspx