Adding ‘Edit with PowerShell ISE’ and ‘Open PowerShell here (as Admin)’ to the context menu →

1002140874_11967e2e51_m In order to edit PowerShell files within PowerShell ISE I used to just drag and drop them from Windows Explorer into the ISE scripting pane. Unfortunately this doesn’t work anymore (I believe since Windows 8). The best explanation for the behaviour I found is here. In short drag and drop doesn’t work from Windows Explorer to an elevated application because of the higher Mandatory Integrity Control (MIC) level of the drag & drop source (Windows Explorer has a default level of medium while the elevated application runs with a high MIC level). There are several workarounds available but they all have negative side effects (elevating explorer to a higher privileges, disabling UAT…). The workaround I’m using is adding a context menu for all PowerShell related files to Edit them with PowerShell ISE (while there is already a default ‘Edit’ context menu entry for PowerShell ISE I like to have it open a specific platform version elevated without loading profile). In addition to that I also like to add a context menu entry to open up a PowerShell command prompt from any folder or drive in Windows Explorer: editWithPowerShellISE openPowerShellHere I wrapped the creation of the context menu into a function. The function offers to following options:

1 min read

Automatically convert an Excel table to a checklist for JIRA →

3161323938_3bb7be6248_m JIRA supports a subset of Wiki Markup to add tables and other formatting to fields like Description or Comments. Writing the Wiki Markup manually is quite time consuming though. To make the process a bit easier I ended up writing a Macro that converts an Excel based task tracker list into JIRA. From this: jiraTable Into that: jiraWikiMarkup Running the Macro will copy the Wiki Markup to the clipboard from where it can be pasted into JIRA. The Macro relies on a mapping between Status values and supported Markup symbols and assumes that the selected cell is within the range that needs to be converted before running the Macro. The VBA project requires the following additional references:

~1 min read

A nicer PromptForChoice for the PowerShell Console Host →

378322049_c01db2cbf5_m Sometimes it’s not possible to fully automate a certain process and we need some input from the user(s) of the script in order to determine the further path of action. If this is based on a fixed set of choices the built-in PromptForChoice method can come to the rescue. Here is an example: https://gist.github.com/778414455f932e1f9ac8 Running the code below in PowerShell ISE will produce the following result:

1 min read

Automatically clean-up excel data with multiple entries per cell separated by comma or line-breaks →

tree This is a follow-up from a previous post where I did the same using PowerShell. A short recap first. The goal is to turn something like this: CleanUpData Into this: CleanUpData2 In the original state some of the cells have multiple entries that are either separated by comma or line-breaks (via Alt+Enter). Furthermore several of those entries contain extraneous spaces. In order to tabulate the data the columns for those rows that contain multiple entries per cell also need to be cross-joined (or Cartesian product) to ensure all possible combinations for the entries are accounted for. Rather than merely translating the recursive CartesianProduct function from the previous post into VBA I decided to follow a different approach. Utilizing ADO to build a cross-join (without duplicates) across columns for rows that contain multiple entries. In order do that (I’m not really good at VBA and there might be better ways, that I’d love to hear about) the columns need to be copied to separate sheets so that the ADODB adapter recognizes them as separate tablesI actually found that there is no need to copy the columns to separate sheets since ADO also accepts range references.The SQL for the cross-join with only unique entries is very simple. Assuming the following setup (for the separated entries of the second row from our example): cross-join3 The Macro to build the cross-join looks like this: https://gist.github.com/d840be45ba5c82e6d874 If you like to follow along here are the steps:

2 min read

Use PowerShell to open regedit at a specific path or RegJump for PowerShell →

12206559615_2b81475662_m Even though PowerShell contains everything to read and write to the registry I still find myself quite frequently opening the registry editor (aka regedit.exe). Since navigating the tree manually can be quite time consuming I used to rely on RegJump developed by Mark Russinovich. I was wondering if the same could be implemented using PowerShell and maybe even adding some features like opening multiple registry keys either from the clipboard or provided as an argument to the function. Say hello to Open-Registry alias regJump. The function opens (instances of) the registry editor for provided paths from the clipboard or as argument to the regKey parameter. The registry paths can contain hive name shortnames like HKLM, HKCU, HKCR or PowerShell provider paths syntax like HKLM:\, HKCU:. Similar to how RegJump.exe handles non-existing paths Open-Registry also ignores those parts of the path and works its way backwards until it finds a valid path or returns an error message if the path doesn’t contain any valid parts. Let’s look at some example use cases:

1 min read

Using PowerShell to clean-up excel data with multiple entries per cell →

8231960108_b07671cb72_m How many times did you come across a situation where you were supposed to work with Data that looks like this?: CleanUpData Some of the cells have multiple entries that are either separated by comma or line-breaks (via Alt+Enter). Furthermore several of those entries contain extraneous spaces. Happy days! What would be actually needed in order to work with the data is one clean entry per cell. In order to do that the columns for those rows that contain multiple entries per cell also need to be cross-joined (or Cartesian product) so that all possible combinations for the entries are accounted for. The end result should look like this: CleanUpData2 How could we do the same using PowerShell? Let’s first have a look on how to do the cross-join part. This can be done quite easily with nested loops. Taking the second row from the example, the following will lead to the desired result: [code language=”powershell”] $name= @(‘Nigel’) $products = ‘Product 1’, ‘Product 2’ $preferences = ‘Fast Delivery’, ‘Product Quality’ foreach($n in $name){ foreach($product in $products){ foreach($preference in $preferences){ “$n, $product, $preference” } } } [/code] One way to turn this into a more generic solution is using recursion (You need to understand recursion in order to understand recursion ;-) ). Here is an implementation of the same: https://gist.github.com/a7209498dbaacb1ef951 Ok, having covered the difficult part we now only need to read the data from excel clean it up and apply the Cartesian product function to it. Here is the full code to automate the whole process: https://gist.github.com/67a2b18e59f440184f47 The above contains a modified version of the CartesianProduct function in order handle objects (actually ordered hashtables since they preserve the column order). If time permits I would like to implement the same as an Excel macro and share it as part of another post. Update: I’ve added another post outlining how to do the same (using another approach) via an Excel Macro

1 min read

Add a default code template to the PowerShell ISE →

tree Some of the 3rd party PowerShell editors offer already built-in support for a default code template where the content of the code template replaces the default blank sheet for every new tab as a starting point for new scripts. While the PowerShell ISE does not provide this functionality out-of-the-box, it can be quite easily added through the $psISE object model by registering a custom action for the $psise.CurrentPowerShellTab.Files ‘CollectionChanged’ event. This event is triggered whenever a tab is closed or opened: https://gist.github.com/ae270d78e2a469398ddf After pasting and running the above code inside the ISE we first need to create the template. The code template is required to be located at “MyDocuments\WindowsPowerShell\ISETemplate.ps1” it can be created and/or edited using the Edit-ISETemplate function. Once the ISETemplate.ps1 contains some text. Every new tab should now be pre-filled with the content of the code template file. In order to make this persistent the code should be added to your profile. You can find the path(s) to your profile by running ‘$profile | select *’. I personally favor the ‘CurrentUserAllHosts’ profile since I don’t want to maintain multiple profile files. Host specific code can be added by using conditions like: [code language=”powershell”] if ($host.Name -eq ‘Windows PowerShell ISE Host’){ #ISE specific code here } elseif ($host.Name -eq ‘ConsoleHost’){ #console specific code here } [/code] I’ve also added this functionality to my ISE Add-On over on GitHub.

1 min read