2014-01-27

As usual, when I embark on a PowerShell project it’s always a learning curve. One might think with all PowerShell’s flexibility, you could just use PowerShell’s” export-csv” command with an array and be on your merry way.

It never turns out to be that easy.

While working on a PowerShell script for SharePoint 2013 to add a three-level Metadata Navigational Menu of Links and Headings to a Site Collection, I required an internal table. For convenience, I chose to store the table as an internal array.  This held the menu titles and urls for a static menu system. This article’s focus is the export of the array to the csv file.

Technically, this is an array of arrays—each line holds seven array values.

After debugging the basic functionality of the script, my plans were to convert this internal array to a Comma Separate file and add a parameter for the name of this file to the script.

The first attempt comes from the eternal optimist’s straight forward approach:

Figure 1 First Try—No Errors–Seems Ok!

All seemed well with no script errors—but alas, upon opening the output file, “C:\tmp\exporttryone.csv”, the data was useless:

Figure 2 C:\tmp\exporttryone.csv

Houston, we’ve got a CSV—but not much else! Recommend launch be scrubbed.

Ok–so we forgot the “-NoTypeInformation” flag—this still doesn’t look promising—even if we ignore the first line.

I guess we can’t get there from here!

We can’t, unless we think about objects like the PowerShell Object, a.k.a the PSObject.  Fast forwarding to what I’ve come to learn: the csv file is kind of a hash table–not an array. Every value in the csv file is associated with a fieldname or label.

This isn’t apparent in the structure of the file. If a csv file has a header, then the columns of that header are associated to the individual values. If no header line exists, then users must supply these values if they are imported or used in any type of display.

Array(s) have one value per field. That value is labeled by an index of position. For example accessing my first menu term “Home” would be $menuTerms[0][0].  These index value are not good labels because they only indicate order within the respective array(s).

The PowerShell export-csv command needs a distinct property name (fieldname) for each value. This is precisely where the PSObject comes in handy. It is able to store a series of values for the property names with the values. It can store a large number of unique property names and values per object.

Array(s) have implicit labels for each value.

If we didn’t know how the array field was being used, it wouldn’t be very useful in programming constructs. This implies a naming scheme.

Looking at the next script, “BuildPublicExport.ps1”, note that there is an array of column names call $pNames. This was added to explicitly label or name the array fields for output. These names will be used in a loop by each PSObject to label the fields.

Note: Only the first four fields are of interest for the export—hence, the variable $numColstoExport. This logic is specific to how it’s being used: the last three values are built dynamically in the script to hold guid values for Terms fields.

Figure 3 BuildPublicExport.ps1

Let’s look a little close at the building of the PSObject and the storing of PSObjects into an array called $holdarr – because this is the magic.

The PSObject is a container that hold the entire line of name/value pairs from the array. The “NoteProperty” is the property or fieldname.  The PSObject is created outside the Inner Loop and populated with values in the inner loop:

$obj = new-object PSObject

In the inner loop, all the values from the first four fields are stored into the PSObject:

     for ($i=0;$i -lt $numColstoExport; $i++){

     $obj | add-member -membertype NoteProperty -name $pNames[$i] -value     $row[$i]

      }

Note: the add-member syntax for the PSObject is rather strange even for PowerShell.  You add members by tossing the $obj down the pipe.

Outside the inner loop, the PSObject is stored into an array and then set to $null for reuse:

   $holdarr+=$obj

   $obj=$null

Once the inner loop has completed, $holdarr is shipped off to be exported via the PowerShell export-csv command.

Setting the $obj to $null is probably superfluous since $holdarr maintains a reference on the $obj.

Also note, the author remembered the “-NoTypeInformation” flag this time.

$holdarr | export-csv C:\tmp\publicmenu.csv -NoTypeInformation

Looking at the csv file C:\tmp\publicmenu.csv:

Figure 4 Proving You can Get Lucky—if you keep at it!

Summary

Getting PowerShell scripts right the first time is not my strong suit. Something always requires experimentation and tweaking.

They are never straight forward—but I always learn something.

Part of my article writing is basic survival in documenting working examples of technology I may again use. A year from now when I haven’t been doing much PowerShell, I’ll remember a Comma Separated File is like a hash table.

I might not remember how PowerShell defines a hash table—but at least I will have a working script to document what I once knew or used and my brain will kick into gear. I also like scripts that work from the get-go, so I’ll attach my examples to this article.

You can’t have too many PowerShell examples!

An added bonus is that my scripts get closer scrutiny and the advantage of your eyes and knowledge.

Let me know if you find this helpful.

 

 

Happy PowerShelling,

Stephan

 

 

 

 

About the author 

Stephan Onisick

Stephan Onisick works as a SharePoint Developer with Analytical Mechanics Associates contracted to the NASA International Space Station in Huntsville, Alabama. He lives in Huntsville with his beautiful wife Janet and 20-month old Shih Tzu named Michael.