2014-02-22

From my last PowerShell Article “PowerShell from an Array to Comma Separated file (CSV) via the PSObject“, http://goo.gl/3ATJgp, I pointed out that a CSV or Comma Separated Value file is kind of like a Hash Table where every value is associated with a column heading.

In PowerShell, just because you get somewhere is no guarantee you can get back.  My design in this project was to use an array internally to test a PowerShell script to add a static 3-level Metadata Navigational Menu to a SharePoint 2013 Site Collection.  Once comfortable that the script was working with the array, my plan was to save the array to a CSV file. This adds flexibility for input to the internal array.  If you followed the last article, we’re half-way there by having created the CSV file.

The task, now, at hand is to read in a CSV file and store the values in a blank array to use for script processing. (The script still needs an array internally for storing values.)

We can and will use the the PowerShell “Import-CSV” Command–but we need to loop through the input data to store it in the array or in our case array of arrays or multi-dimensional array.

The following is a code snippet that I am using in a PowerShell script to read in a table of nine values.

 #region ReadIn Termset

    $menuTerms=@()
     import-csv $CSVFile |
       % { $menuTerms+=(,($_.L1Title, $_.L1Url, $_.L1NewWindow, $_.L2Title, $_.L2Url, $_.L2NewWindow,        $_.L3Title, $_.L3Url, $_.L3NewWindow, $_.L1Guid, $_.L2Guid, $_.L3Guid) )}

#endregion

Note: I’m actually looking for 12 values but don’t expect the last three of each row to be in the file. If you were to examine one of the values from array positions 9-11, you will find that they are null. Either of these two expressions will produce “True” in a debug window when the script is breakpointing after reading in the file:

    $menuTerms[0][9] -eq $null

  [string]::IsNullorEmpty($menuTerms[0][9])

 

The PowerShell script uses the extra fields in the array for storing Guids of various Termstore items.  Also “%” is shorthand for “foreach“.  The whole procedure is enclosed in a “region” to enable code to quickly collapsed in a code editor (regions” don’t impact the code).

The array is initialize to “@()” which is an empty array in PowerShell.

The syntax of adding to the array is slightly weird.  Note the “+=(,(” at the beginning and the “))” at the end: 

 % { $menuTerms+=(,($_.L1Title, $_.L1Url, $_.L1NewWindow, $_.L2Title, $_.L2Url, $_.L2NewWindow, $_.L3Title, $_.L3Url, $_.L3NewWindow, $_.L1Guid, $_.L2Guid, $_.L3Guid) )}

It is as if you are adding an additional array of values in each read–which is precisely what is being done in building an array of arrays with 12 values. Each row will execute this addition of 12 items.

The whole operation is pretty straight forward once you get used to the syntax.  This brings me to the point of why anyone needs to learn this stuff like arrays and CSVs.  It would not interesting to me if I had not needed the information to accomplish a task.  In fact, that is how I learn most programming: the need to accomplish some objective and stuff gets in the way–so I learn it.

These last two articles of mine were spin-offs of a major project I just launched on CodePlex entitled “Static SharePoint 2013 Navigational Menu from a Comma Delimited File (CSV) for Input” https://staticsharepoint2013navmenu.codeplex.com/.

Figure 1:  Demo of 3-Level Static Metadata Navigational Menu using SharePoint 2013 “Composed Looks

If you have the bandwidth and an On-Premise SharePoint 2013 system, I invite you to take the script for a ride. The CSV was built just as a test for the format of the file. I have already loaded a menu with close to 200 items and 3-levels.

My next article will go into more detail on the project, I just wanted to share some of the technology I visited in producing the final project.

Happy SharePointing and 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.