PowerShell
PowerShell Get-Command -Syntax output explained →
Find out in which version a PowerShell Cmdlet was introduced →
 Some days ago Thomas Rayner (whom I admire for his passion and consistency when it comes to sharing his knowledge) posted about the same topic on his blog. He mentioned a method on how to utilize GitHub in order to find out the earliest Version a PowerShell Cmdlet was introduced. When I read the Thomas’s post, I couldn’t resist thinking about an option to automate the process. My first attempt was to check whether the information is already part of the help system or could be retrieved using Get-Command (using ‘Expand-Archive’ as an example): [code language=”powershell”] Get-Command Expand-Archive | Format-List * | Out-String -Stream | Where-Object {$ -like ‘version’} Get-Help Expand-Archive -Full | fl * | Out-String -Stream | Where-Object {$_ -like ‘version’} [/code] While both commands return some information in relation to a version, in general, there is nothing telling us about the PowerShell version the Cmdlet was first introduced. The next thought I had, looking at Get-Help was the online version. [code language=”powershell”] Get-Help Expand-Archive -Online [/code] This command opens the default browser opening the HelpURI, which in this case looked like https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Archive/Expand-Archive?view=powershell-5.1. See the last part of the URI? There seemed to be some hope following this route. The webpage features a drop-down where users can select a PowerShell version (3-6). Selecting Version 3 from the drop-down for the Expand-Archive help page brought me to this URI https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Archive/Expand-Archive?view=powershell-6&viewFallbackFrom=powershell-3.0. Version 4 revealed a similar result, while the Version 5 URI did not contain the “viewFallback…” part. Similarly, when pasting a URI like https://docs.microsoft.com/en-us/powershell/module/Microsoft.PowerShell.Archive/Expand-Archive?view=powershell-3.0 into the browser’s address bar, it redirects automatically to the “viewFallback…” version. Great, there we have it, a pattern that can be automated :-):
Get .net Format Strings for given input →
Yet again, long time no post!
.net Format Strings are very useful when it comes to taking control over the output of your scripts. e.g.:
[code language=”powershell”]
‘{0:t}’ -f (Get-Date)
#output: h:mm tt
‘{0:n1}’ -f 2.45
#output: 2.5
[/code]
The problem with those Format Strings is, that hardly anyone can remember them. While there they are thoroughly documented and several nice folks have created cheat-sheets (e.g. here), I thought it would be nice to be able to get (at least the most common) Format Strings for a given input automatically along with the respective outputs within PowerShell.
The function returns output for Integer, Double, and DateTime input. You can download Get-FormatStrings from my GithHub repository or just grab the code below:
https://gist.github.com/25ec562a8bb21b1899044ca7913ae7ef
PowerShell tricks – Convert copied range from excel to an array of PSObjects →
In this post, I would like to share a simple function that converts tables copied from Excel to the clipboard into PowerShell objects.
While there are several more efficient ways to retrieve data out of Excel files (e.g. COM, EPPlus), I sometimes just want something quick and dirty to get the job at hand done.
https://gist.github.com/12f06bec82dc4cc93370b2fa7dc750f4
Usage:
Full text search using PowerShell, Everything, and Lucene →
Searching for files is something everyone does on a very regular basis. While Windows is consistently changing the way this is done with every new operating system, the built-in functionality is still far from being sufficient. Therefore, I’m always looking for methods on how to improve this (you can also find several blog posts in relation to file searches around here). In regards to searching for files based on file names or paths, I’m pretty happy with the performance of Everything. If it is about searching for files based on their content (aka full-text search), there is still room for improvement in my opinion.
Recently I’ve been watching the session recordings from the PowerShell Conference Europe 2016 (I can highly recommend anyone that is interested in PowerShell to watch those).
Get help for Windows built-in command-line tools with PowerShell →
One of the reasons I like PowerShell is its built-in help system (here is a nice post in case you don’t know how to use PowerShell’s built-in help). E.g.:
[code language=”powershell”]
Get-Help Get-Command
Get-Help Get-Command -Examples
Get-Help Get-Command -Parameter Name
[/code]
In fact, once you get comfortable using PowerShell help aka Get-Help, you start missing similar built-in documentation for other tools/scripting languages. Wouldn’t it be nice if one could use Get-He.lp for Windows command-line tools?:
[code language=”powershell”]
Get-Help chkdsk
Get-Help chkdsk -Examples
Get-Help chkdsk -Paramater c
[/code]
Reporting against Pester test results →
Pester is (for very good reasons) getting more and more popular. If you don’t know about Pester I would highly recommend you to start using it. Here are some good resources to learn about the framework:
PowerShell tricks – Open a dialog as topmost window →
Windows.Forms provides easy access to several built-in dialogs (see MSDN: Dialog-Box Controls and Components). Here is an usage example to show a “FolderBrowse” dialog:
[code language=”powershell”]
Add-Type -AssemblyName Windows.Forms
$FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
$FolderBrowser.Description = ‘Select the folder containing the data’
$result = $FolderBrowser.ShowDialog()
if ($result -eq [Windows.Forms.DialogResult]::OK){
$FolderBrowser.SelectedPath
}
else {
exit
}
[/code]
While this works as expected, the dialog won’t show up as the topmost window. This could lead to situations where users of your script might miss the dialog or simply complain because they have to switch windows. Even though there is no built-in property to set the dialog as the topmost window, the same can be achieved using the second overload of the ShowDialog method (MSDN: ShowDialog method). This overload expects a parameter which indicates the parent windows of the dialog. Since the owning window will not be used after the dialog has been closed we can just create a new form on the fly within the method call:
[code language=”powershell” highlight=”4”]
Add-Type -AssemblyName System.Windows.Forms
$FolderBrowser = New-Object System.Windows.Forms.FolderBrowserDialog
$FolderBrowser.Description = ‘Select the folder containing the data’
$result = $FolderBrowser.ShowDialog((New-Object System.Windows.Forms.Form -Property @{TopMost = $true }))
if ($result -eq [Windows.Forms.DialogResult]::OK){
$FolderBrowser.SelectedPath
}
else {
exit
}
[/code]
PowerShell and XML part 1 - Visualize XPath expression →
Use Bing Search and Synonyms APIs through PowerShell →
Add spell checking to PowerShell ISE →
Fix: Clipboard is not working + Restart-Process with PowerShell →
Sometimes it happens that the clipboard stops working. The routine of copy and paste we all rely on so many times a day suddenly refuses to do its job. The reason this happens is usually an application blocking the keyboard, making it impossible for other applications to get access to the clipboard. In order to fix this, one needs to find out which application is the culprit and either stop or restart the respective process in order to “free up” the clipboard.
I put together a small PowerShell function (Clear-Clipboard), that does just that:
Retrieve UninstallStrings to fix installer issues →
Recently I have encountered several installer related issues on my machine. Most of them seemed to be caused by insufficient privileges.
This kind of issue can be usually fixed by running the installer “As Administrator”. In case the issue is in relation to an already installed software packet, it’s sometimes not so easy to locate the respective uninstaller/MSI packet, though. For that purpose, I’ve written a small PowerShell function that scans the registry (it turned out that if you are using PowerShell v5, there is a better way of doing this. See below for more details) (have a look here on why I didn’t want to use WMI Win32_Product instead) for the information. The function has the following features:
Use PowerShell to set Exchange Out of Office status from any PC →
I’m sure this has also happened already several times to you. You finish up your work to start into your well deserved holidays and only after you arrive at home do you realize that you forgot about to set your “Out of Office” status (considering that you actually do that). Usually, this would mean that you need to use your company device in order to connect back to work and set it up. If your company is running Exchange mail servers there is actually another option available which enables you to do the same from any PC that is connected to the Internet. The EWS Managed API is the technology that enables this.
I’ve written a module that uses the API in order to set an Out of Office message. The function has the following features:
Convert-String for PowerShell is like FlashFill for Excel →
Simplified syntax for calculated Properties with Select-Object →
WMI query filters with PowerShell syntax instead of WQL →
PowerShell comes already with tight integration to WMI with its built-in Get-WmiObject and Get-CimInstance cmdlets. One of the things that people already familiar with PowerShell syntax bothers about WMI is that it comes with its very own query language WQL. While WQL is very similar to SQL. Wouldn’t it be nicer if we could use the same operators and wild-card patterns we are already familiar with?
Well, for myself the answer is Yes:
Zen Coding for the PowerShell console and ISE →
First of all let’s clarify what Zen Coding actually is. According to their website: Emmet (formerly known as Zen Coding) is …
Simplified Where-Object for multiple conditions on the same property for PowerShell? →
[](https://powershellone.files.wordpress.com/2015/10/3107494832_60009ec22b_m.jpg)
While PowerShell version 3 already introduced a (quite controversial) simplified syntax for the Where-Object cmdlet (alias where). It still doesn’t account for a quite common error PowerShell beginners encounter when using where with multiple conditions on the same property. As an example let’s say we would like to filter the range 1-10 to get only those numbers that are between 6 and 7. I’ve seen many people (yes that includes me) attempting to do it like below since it seems a logical translation of ‘where x is greater than 5 and lower than 8’.:
[code language=”powershell”]
1..10 | where {$ -gt 5 -and -lt 8}
#correct version
1..10 | where {$_ -gt 5 -and $_ -lt 8}
[/code]
Granted that this failing makes mathematically total sense since it should say ‘where x is greater 5 than and x is lower than 8’ . I’d wish there would be a syntax supporting something like this:
[code language=”powershell”]
1..10 | where {$_ (-gt 5 -and -lt 8)}
#or
Get-Process | where {$_.Name (-like ‘power’ -and -notlike ‘ise’)}
[/code]
The idea is that the parentheses would indicate that the preceding variable should be considered as the (left-hand) parameter for the operator. I came up with a crude proof of concept on how this could be done:
https://gist.github.com/d24442b2bbb72f5ff269
What do you think, would you also like to see this kind of syntax for Where-Object?
Review of methods to download files using PowerShell →
The goal of this post is to review and compare different methods to download files using PowerShell. As part of the review I would like to share (in addition to the inline source code you can also download a module (Get-FileMethods) that contains all functions via GitHub) some wrapper functions that follow the same pattern:
Show-CommandGUI an enhanced Show-Command for PowerShell →
After my last post (PowerShell tricks - Use Show-Command to add a simple GUI to your functions). I was thinking how one could write a function that would not have the deficiencies that Show-Command has when it comes to providing a GUI for functions. In addition to what Show-Command does I wanted a function that:
PowerShell tricks - Use Show-Command to add a simple GUI to your functions →
The Show-Command cmdlet has been introduced in PowerShell Version 3 and is very useful to help discovering and learning more about PowerShell cmdlets and their respective parameters (also built into the ISE as the Show-Command Add-on).:
[code language=”powershell”]
#Discover commands by running Show-Command without parameters
Show-Command
#Run Show-Command for a specific cmdlet
Show-Command Get-ChildItem
[/code]
Show-Command can be also utilized for your own functions in order to provide your users with a simple GUI as it builds a graphical user interface for the provided function on the fly. Show-Command displays:
PowerShell tricks – Useful default parameters to add to your profile →
Since version 3 PowerShell introduced $PSDefaultParameterValues which is a built-in preference variable which lets you specify default values for any cmdlet or advanced function. You can read much more about it inside the respective help file. In a nutshell $PSDefaultParameterValues is a hash-table where (in its most common version) the key consists of the cmdlet name and parameter name separated by a colon (:) and the value is the custom default value:
[code language=”powershell”]
Expanding aliases in PowerShell ISE or any PowerShell file →
Further extending my PowerShell ISE module (ISEUtils) I’ve added a function to convert aliases either in the currently active ISE file or (in case a a path is provided) within any PowerShell file (that way the function can be also used from the PowerShell Console) to their respective definitions.
Aliases are very useful when working interactively, since they help saving extra keystrokes when you just want to get things done fast. At the same time if we are speaking about production code where readability, and easy comprehension of the code are much more important the usage of aliases should be avoided ( read here for a good article on best practices for PowerShell alias usage).
With the Expand-Alias function you can get the best of both worlds. Writing clearer code while avoiding extraneous keystrokes. For the code samples in my blog posts I’m also using aliases quite a lot, but would like to start using the new function from now on.
Below is the source code for Expand-Alias:
https://gist.github.com/9db6632423d673ff18f6
Usage:
PowerShell tricks - Using dot(s) to refer to the current location →
Most people are aware that PowerShell supports commandline navigation in the same way as the good old command prompt (see my previous post Improve PowerShell commandline navigation for ways to enhance this):
[code language=”powershell”]
cd $env:USERPROFILE\Desktop
Resolve-Path ‘.’
#change to the current direction (doing nothing)
cd .
#move up one level
Resolve-Path ‘..’
cd ..
[/code]
The above is using cd as the alias for the Set-Location Cmdlet providing:
Create an integrated (WPF based) ISE Add-On with PowerShell →
Using Microsoft SyncToy through PowerShell →
This post is about running Microsoft SyncToy via PowerShell. For those that don’t know SyncToy:
Using the String.Split method with multiple separator characters in PowerShell →
This post is about what I thought of an odd behaviour when calling the .NET String.Split method with multiple separator characters from PowerShell. I first came across this myself but didn’t really pay much attention to it. Only after reading about it again over on Tommy Maynard’s blog, I decided to find out more.
Let’s have a look at an example first:
[code language=”powershell”]
#using String.Split with one separator character works as expected
‘This is a test’.Split(‘e’)
#using multiple characters not so much
‘c:\test’.Split(‘\’)
‘c:\test’.Split(‘\’).Count
[/code]
When running the second example trying to split a string based on double backslashes the result is an array of 3 strings instead of two. Let’s try to see why this is happening by retrieving the specific overload definition we are using:
[code language=”powershell”]
#get the overload definition of the method we are using
‘‘.Split.OverloadDefinitions[0]
#string[] Split(Params char[] separator)
[/code]
Ok, it looks like this overload of the Split method expects a character array for the separator parameter. That is why we saw an additional split, every character of the string argument ‘\’ is considered as a unique separator. Let’s see if String.Split has other overload definitions that accept a String as the separator argument:
[code language=”powershell”]
‘‘.Split.OverloadDefinitions | Select-String ‘string[] separator’ -SimpleMatch
<#
string[] Split(string[] separator, System.StringSplitOptions options)
string[] Split(string[] separator, int count, System.StringSplitOptions options)
#>
[/code]
Indeed, there are two overloads that accept a String array argument instead. Let’s use the first one. We don’t need the StringSplitOptions parameter in this case and can therefore use a value of ‘None’ for the argument.
[code language=”powershell”]
#this doesn’t work since we need a String array
‘c:\test’.Split(‘\’, ‘None’)
#finally we get only two parts back
‘c:\test’.Split(@(‘\’), ‘None’)
‘c:\test’.Split(@(‘\’), ‘None’).Count
[/code]
We could have used the -split operator in the first place, but that would have been to easy, right ;-). Furthermore with the String.Split method we can also split a string by multiple strings in just one go:
[code language=”powershell”]
#using -split operator we need to escape the \ by doubling them since we are dealing with regular expressions
‘c:\test’ -split ‘\\’
#splitting by two strings
‘split by xx and yy in one go’.Split((‘xx’,’yy’),’None’)
#can be done also with -split using a scriptBlock
Adding ‘Edit with PowerShell ISE’ and ‘Open PowerShell here (as Admin)’ to the context menu →
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:
I wrapped the creation of the context menu into a function. The function offers to following options:
A nicer PromptForChoice for the PowerShell Console Host →
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:
Use PowerShell to open regedit at a specific path or RegJump for PowerShell →
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:
Using PowerShell to clean-up excel data with multiple entries per cell →
How many times did you come across a situation where you were supposed to work with Data that looks like this?:
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:
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
Add a default code template to the PowerShell ISE →
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.
PowerShell tricks - replace and transform a value within a string →
PowerShell Error - “Select-Object : Cannot convert System.Object[] to one of the following types…” →
Search file content by keyword using Everything + PowerShell + GUI →
Even with Windows 10 MS still didn’t manage to include a proper in-built file search functionality. If it is about searching for files I definitely prefer the excellent Everything search engine (see also my post on a PowerShell wrapper around Everything commandline) .But quite frequently I also need to search for keywords/pattern within files. PowerShell’s Get-ChildItem and Select-String can certainly do this together:
[code language=”powershell”]
#search through all .ps(m)1 files for instances of the word ‘mySearchString’
$path = ‘c:\scripts\powershell’
Get-ChildItem $path -Include (“.ps1”,”.psm1”)) -Recurse |
Select-String ‘mySearchString’ | select Path, Line, LineNumber
[/code]
While this does the job it doesn’t follow my preferred workflow and is also not very quick when running it against a large set of files. I would prefer to have the ability to search and drill down a list of files within a Graphical User Interface just like Everything and then search through the filtered list of files using keyword(s)/pattern(s) and get back the search results within a reasonable time-frame.
Say hello to “File Searcher” (I didn’t spend any time thinking about a catchy name):
The three text boxes at the top of the UI can be used to:
Finding the index of an object within an array by property value using PowerShell →
Today I was looking for an option to find the index of an object within an array of objects. Using an array of one dimensional objects like integers this is as simple as using the static IndexOf method of the Array class in the System namespace:
[code language=”powershell”]
$array=10..1
$index = $array.IndexOf(3)
$array[$index]
[/code]
But in my case I wanted to get the index of an item within an array of multidimensional or rich objects. As an example let’s say after running Get-Process we would like to find the index of the process whose Name property equals “powershell” (the return type here is an array of System.Diagnostics.Process objects).
My first approach was to use the Select-String cmdlet since I knew it returns a LineNumber property. After some trial and error I came up with the following:
[code language=”powershell”]
$processes = Get-Process
$index = (($processes | Out-String).Split(“n") |
Select-String "powershell").LineNumber
#index needs to be decremented by 4 since the data starts at line 3 and LineNumber is 1 based
$processes[$index-4]
[/code]
While this returns the desired result it's not a very robust solution. If there is, for example, powershell and powershell_ise running at the same time this would return two line numbers instead of one. Furthermore, the approach does not permit to look for items by property values (unless you throw in some crazy regex).
Ok, let's give it another try. The problem with the $processes array is that it doesn't have an index property, but fortunately, with PowerShell it's not a problem at all to add one:
[code language="powershell"]
$processes = Get-Process | foreach {$i=0} {$_ | Add-Member Index ($i++) -PassThru}
$index = ($processes | where {$_.Name -eq "powershell"}).Index
$processes[$index]
[/code]
This looks already better. Adding an Index property to the array makes it easy to replicate the IndexOf method's functionality with an array of rich objects. It still involves quite some steps, though. Looking for yet another approach, I came across the [FindIndex method](https://msdn.microsoft.com/en-us/library/x1xzf2ca(v=vs.110).aspx) that is part of the System.Collections.Generic namespace:
[code language="powershell"]
$processes = [Collections.Generic.List[Object]](Get-Process)
$index = $processes.FindIndex( {$args[0].Name -eq "powershell"} )
$processes[$index]
[/code]
With that approach, we first need to cast the array to generic list. Generics are a concept of strongly typed languages like C# which make it possible to write a method that can be used against multiple types of objects (hence generic). For PowerShell, this doesn't matter so much since its type system is rather dynamic and mainly implicit therefore we just use a generic list of objects (kind of a generic generic list) here. The FindIndex method expects a typed predicate as its only argument which in PowerShell conveniently translates to a ScriptBlock. The predicate is exactly the same as what is used as the FilterScript parameter for the Where-Object cmdlet. The only difference is that we need to use the built-in [$args variable](https://technet.microsoft.com/en-us/library/hh847768.aspx) in order to access the current ("Pipeline") element instead of "$_".
How do those approaches compare in terms of execution speed?:
[code language="powershell"]
@'
(gps | foreach {$i=0} {$_ | Add-Member Index ($i++) -PassThru} | where Name -eq "powershell").Index
((gps | out-string).split("n”) | sls “powershell”).LineNumber
(Collections.Generic.List[Object]).FindIndex({$args[0].Name -eq “powershell”})
‘@ -split “`n” | foreach{
(Measure-Command ([ScriptBlock]::Create($))).TotalSeconds
}
[/code]
On my machine I got 0.21, 1.67, 0.02 respectively. Looks like the last approach also outperformed the others by at least an order of magnitude.
PowerShell tricks - Build an array of strings without quotation marks →
This is one of the tricks I keep forgetting about and therefore document it here for myself but also in case someone else might find it useful.
In order to create an array of strings one usually does something like this:
[code language=”powershell” light=”true”]
$stringArray = “first”, “second”, “third”, “fourth”
[/code]
It involves quite some redundant characters in order to do a simple thing. This can be made easier using a simple function that is part of the excellent PowerShell Communicty Extensions. The QL (QL is short for Quote-List an idea borrowed from Perl) function has the following definition:
[code language=”powershell”]
function ql {$args}
ql first second third fourth
[/code]
Note that extraneous commas and quotation marks can be avoided using this approach. There is actually even a built-in cmdlet that can be used for the same purpose. Write-Output alias echo or write:
[code language=”powershell” light=”true”]
echo first second third fourth
[/code]
If an element of the string array we’d like to create contains a space the element needs to be surrounded in quotes:
[code language=”powershell” light=”true”]
echo first second third fourth “with space”
[/code]
As a bonus tip we can use a similar idea as for the ql function in order to create strings without having to limit them by quotation marks:
[code language=”powershell”]
function qs {“$args”}
qs this is a long string without any quotes
#only gotcha is when using quotes (single or double) within the argument
#qs this does not’ work
qs quotes require escaping using a 'backtick’ otherwise it will not work
[/code]
Sort data using a custom list in PowerShell →
Run SQL against Excel and Access through Access Database Engine with PowerShell - Part 1 →
This is part 1 of a series of blog posts around using SQL against Excel and Access through ACE drivers (Access database engine) with PowerShell.
Part 1 is about introduction and getting up and running.
Comparing two objects or .csv files column by column →
Extending the PowerShell range operator →
Work with files and folders that have paths longer than 260 characters in PowerShell using AlphaFS →
How to convert Excel 97-03 (.xls) to new format (.xlsx) using office file converter (ofc.exe) →
PowerShell Format-Table Views →
PowerShell Property Sets →
Improve PowerShell commandline navigation →
Using Everything search command line (es.exe) via PowerShell →
Use PowerShell to measure the time to download a file →
PowerShell scope write-up →
PowerShell and the “missing” ternary operator →
Waiting for elements presence with Selenium Web driver →
Group-Object “Wildcard characters are not allowed in …” →
Excel
Automatically convert an Excel table to a checklist for JIRA →
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:
Into that:
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:
Automatically clean-up excel data with multiple entries per cell separated by comma or line-breaks →
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:
Into this:
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):
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:
Using the Excel intersection operator →
One of the lesser-known features of Excel is the intersection operator which can be used to simplify look-up operations. An intersection is the overlap of two or more cell ranges within excel. For instance:
In the screenshot below the ranges C1:C5 and B3:D3 (Cell ranges in Excel are written by using the range operator “:”) overlap in the cell C3.
The intersection operator “ “ (a space) can be used to find the intersection of ranges. To find the intersection of the two ranges one can just use the following formula “=C1:C5 B3:D3”:
Combining the intersection operator with named ranges yields to pretty intuitive look-ups in Excel. Let’s take up another example using monthly revenue data by region:
Highlighting the table and pressing CTRL+SHIFT+F3 will bring up the “Create Names from Selection” dialog. We can go with the defaults (Top row, Left column) in order to create named ranges for each column and row within the table based on their labels:
Now, in order to retrieve the March Results for the East region we can simply use “=March East”:
Getting the Sum of the revenues from January-April for the West region is equally simple “=Sum(January:April West)”:
Even non-consecutive ranges can be easily referred to. Pulling up the Sum of revenues for the month of January, March, and May for the South region is as easy as typing “=Sum((January,March,May) South)”:
How to convert Excel 97-03 (.xls) to new format (.xlsx) using office file converter (ofc.exe) →
Convert between US and European date formats in Excel →
“Please wait while windows configures Microsoft Visual Studio…” when starting Excel →
Troubleshooting
Fix: Clipboard is not working + Restart-Process with PowerShell →
Sometimes it happens that the clipboard stops working. The routine of copy and paste we all rely on so many times a day suddenly refuses to do its job. The reason this happens is usually an application blocking the keyboard, making it impossible for other applications to get access to the clipboard. In order to fix this, one needs to find out which application is the culprit and either stop or restart the respective process in order to “free up” the clipboard.
I put together a small PowerShell function (Clear-Clipboard), that does just that:
Fix: Chrome won’t start or taking a long time to start →
Retrieve UninstallStrings to fix installer issues →
Recently I have encountered several installer related issues on my machine. Most of them seemed to be caused by insufficient privileges.
This kind of issue can be usually fixed by running the installer “As Administrator”. In case the issue is in relation to an already installed software packet, it’s sometimes not so easy to locate the respective uninstaller/MSI packet, though. For that purpose, I’ve written a small PowerShell function that scans the registry (it turned out that if you are using PowerShell v5, there is a better way of doing this. See below for more details) (have a look here on why I didn’t want to use WMI Win32_Product instead) for the information. The function has the following features:
Fix: Battery Icon and/or Volume Control Missing From Windows 10 Taskbar →
I came across a rather weird problem today where my taskbar was missing the icons for the volume control and battery meter:
My first attempt to fix this was to check the related system settings:
