Category Archives: Uncategorized

Powershell: Search and Replace in text files

Searching and replacing within Powershell, seems a little awkward.

To do a search and replace, use the –replace parameter of ForEach-Object, with two attributes, the first being the thing to search for and the second being the replacement.

Get-Content -path ./NGA_Tracking.csv | ForEach-Object {$_ -replace ‘Tracking’, ‘TrackNum’}

You can do a series of these in a single set of piped commands….

Get-Content -path ./NGA_Tracking.csv | ForEach-Object { $_ -replace ‘Tracking’, ‘TrackNum’ `
    -replace ‘X’, ‘Y’ `
    -replace ‘A’, ‘B’ } |
Set-Content $mynewfile

In the case of a text file, each object being modified in the For-Each loop is a string terminated by a newline.

The good news is that there is full support for regular expressions, so if you know how to deal with those, they can be incorporated within the -replace mechanism.

I guess I’m still looking for something a bit more friendly for casual use, Wouldn’t it be nice, for instance to have something like Get-Content $myfile -replace ‘X’,’Y’.   Well, we actually can do this:

(Get-Content ./NGA_Tracking -raw) -replace ‘Tracking’, ‘TrackNum’ | out-file ./NGA_Tracking -Encoding utf8 

If you don’t put the parentheses around Get-Content $filename -raw you generate an error. The parentheses read the entire file into memory as an object, which can then take the -replace parameter. The -Encoding parameter is there to make sure the characters are all read correctly in subsequent uses of the file. (took lots of experimentation and blood on the floor, but that it what worked.)

The effect is the same, when the file name is replaced.

$Myfile = ‘./NGA_Tracking.csv’
(Get-Content $MyFile)  -replace ‘Tracking’, ‘TrackNum’ | Out-File $MyFile -Encoding utf8

But, if you create an object then it works.

$Myfile = Get-Content ‘./NGA_Tracking.csv’
$Myfile -replace ‘Tracking, ‘Tracknum’ 

Text to HTML

As we start fiddling with text files, the question comes up regarding the creation of html files.  There is a Convertto-HTML commandlet, which, on the face of it looks pretty rudimentary. But this TechNet article explains how to enhance things using styles.

Brightpearl API: Add UPS Tracking Numbers

We have now been using our web store for about a month, and for the most part things have been going pretty smoothly. One issue has been sending orders to our warehouse, and we’ve got a pretty good Powershell script that creates a comma delimited text file (.csv) of order numbers and address information from queries to the Brightpearl API. This file is sent daily to the warehouse via FTP, and warehouse staff  import the orders into their UPS Worldship program.


The second half of this saga is to obtain the UPS Tracking number for each shipment. Once the shipment has been processed in UPS Worldship, a tracking number is generated and stored in the UPS Worldship record for that shipment. Worldship has an export function which will add the tracking number to a .csv file of order numbers and tracking numbers that we can use to update the order record in Brightpearl. The structure of this file, (which is completely customizable) is:

Order Number – In our case it is the Brightpearl sales order number
Tracking Number – from UPS. These look like “1Z 041 388 03 8331 4101”
Expected Delivery Date.
The .csv file looks like this. (UPS loves long field names).


The next step is to walk through the .csv file, find an order number, and update the custom field PCF_TRACKING in Brightpearl to contain the tracking number. Here is the Powershell call to update a single record:

PS>$BPOrders=Invoke-RestMethod `
 -Uri `
 -Body $body `
 -Headers $headers `
 -Method Patch

There are a couple points of interest here. For the most part it is “standard” Powershell syntax for the Invoke-RestMethod.
1. We invoke this by assigning the results of the API call to $BPOrders
2. The call has several lines; the line continuation character is a accent aigu or back-tick.
3. Note that this query uses a $headers variable which includes the two validation properties for the Brightpearl query: , the name of the application and the security token for the application. These are stored as a hashtable.

brightpearl-app-ref myappreference
brightpearl-staff-token mystaff-tokenXYZ123

More on obtaining the authcode here.

4. The $body variable is also created as a hashtable, but then converted to JSON, and placed between square brackets. This variable contains three parameters, the operation that you are performing on the record, the field that you want to modify, and the value that you want to put in the field. The syntax below simply says, “Replace the contents of the /PCF_TRACKING field with the value of 12345”.

$body=($body | ConvertTo-JSON)

The result is:

"op": "replace",
"path": "/PCF_TRACKING",
"value": "12345"

5. The -Method parameter is a “Patch”. This allows you to replace the contents of a single field in a record rather than replace an entire record as happens when you use PUT.

6. Finally note in the Invoke-RestMethod call, the URI contains “custom-field”. This is a literal, it isn’t the name of your custom field. The name of the custom field is contained in the body.I In the example above, it is “/PCF_TRACKING”

The above API call will replace the contents of a single field in a single record. The next step is to be able to loop through the .csv file, and for each record, find the corresponding record within the Brightpearl database, and update its Tracking number field.

Oh, one more thing, the results of the operation are contained in $BPOrders. The API actually returns the contents of ALL custom fields. You can choose which ones you want to see using dot notation.


Quick Look at Windows 10

Well, I was going to say that the Windows 10 technical preview solves many of the problems that Windows 8 has, including the lack of a start button.

I installed this in a virtual machine both to take a look at 10, but especially to take advantage of updates to Windows PowerShell, which are available only with Windows 8 or later. I’m happy to say the start button is there.

The start button leads to the tiles left over from Windows 8. Maybe this can be re-configured to avoid the tiles? Didn’t Microsoft get the memo that people working on business-class desktop computers DON’T WANT TILES!  At least not the default ones, with videos, the stock market, etc.

OK so you can right-click and delete a tile.  I can imagine configuring this as a standard operation when deploying a new desktop computer to a co-worker.

If you look in the document explorer the new icons are even more cartoon-like than than before. It makes me nostalgic for the old “cartoon-like” icons of Windows XP.


PowerShell: Simple GUI Message Boxes

One way to build simple message boxes in PowerShell is to “borrow” from .NET COM objects. Basically you initialize the object…(a.k.a. load the code for generating a messagebox), and then you create instances of the object which are the actual message boxes. These messagebox code may  be familiar to anyone who has programmed in vbScript, or any of the Microsoft programming languages such as Visual Basic or C#.  The boxes can look pretty good, even though the coding is a bit arcane.

To get started, initialize the messagebox object. You only need to do this once per session.

PS> $wshell=New-Object -comObject Wscript.Shell

Now you can make message boxes until the cows come home.

PS> $wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”)


You can have multi-line boxes by including the newline (back-tick+’n’) to separate lines.

PS> $wshell.Popup(“Hi! A list of shipping addresses will appear in the next window.`n

If you need to edit the addresses then… `n

  1. Go back to BrightPearl`n
  2. Make the address changes.`n
  3. Run this program again.”,0,”Send To Warehouse”)


The Buttons

You can have a buttons which return an integer based on which button is pressed.

In PowerShell, the button combinations are designated by integers:

0: OK
1: OK, Cancel
2: Abort, Retry, Ignore
3: Yes, No, Cancel,
4: Yes, No,
5: Retry, Cancel

The parameters for showing a window are:

<messagetext>, <duration>, <window title>, <button integers>

PS> $wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,1)

The first parameter is the message that you want to appear in the box. This is a string.

The second parameter “duration”, is an integer that specifies how long you want the messagebox to stay open if there is no activity from the user. If you specify ‘0’, then the box stays open forever. Note that your script is completely paused when this is the case. If you use a positive integer, that is the length in seconds that the messagebox stays open.

The third parameter is a string that is the title of the box. This appears in the top of the window frame.

In the example above, the messagebox call specifies “1” for the OK and Cancel button combination. When the user clicks on a button the messagebox closes, and returns an integer, in this case, 1 for OK or 2 for Cancel. The integers are displayed on the PowerShell command line.  (Technically, the integers are returned to the pipeline).

Note that these returned integers are have nothing to do with the integer used to determine which buttons are displayed. (Why do I have to remind myself of this?) 

Button Return Values

OK Cancel Abort Retry Ignore Yes No
1 2 3 4 5 6 7

Instead of having the result go to the pipeline, you can store it in a variable for further use. Capture the result variable by assigning the message box to a variable.

 PS> $result=$wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,1)

PS> $result

The Icons

Messageboxes can also have an icon. There are four to choose from, and each has a designated integer.

Stop 16
Question 32
Exclamation 48
Information 64

Now, here’s the arcane part.  To display an icon,  you take the icon’s numeric value and add it to the numeric value of the button numeric value.  So, for example to place a Stop icon on our sample message box with OK and Cancel buttons  we add 16 to 1. Its not like they couldn’t provide another parameter?

PS> $result=$wshell.Popup(“Hi…This is a lovely messagebox”,0,”My Window Title”,65)


If you get an error message, when putting up a messagebox, like the one below…   it means that you didn’t run the New-Object cmdlet one time in your PowerShell session, prior to making a call to put up a messagebox.

PS> $wshell=New-Object -comObject Wscript.Shell

Having run that once, you can create as many messageboxes as you need within that particular PowerShell session.

You cannot call a method on a null-valued expression.
At line:1 char:1
+ $result=$wshell.Popup("Hi…This is a lovely messagebox",0,"My Window Title",65)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : InvokeMethodOnNull


eCommerce with Shopify and Brightpearl

We run a web store as part of our operation, with any profits from the store flowing into our programs We recently launched with a new web store, and it has been up and running for two weeks. The transition from our old store to our new one has been pretty smooth. We didn’t write much in the way of actual programming code..we just lease a bunch of it from cloud-based vendors including Shopify, Brightpearl. and various add-on vendors. Well, that’s not entirely true; our web wizard has spent days and weeks working on the html templates for the shop. and I’ve been working on the back-end APIs to integrate the Brightpearl Inventory/CMS with our warehouse system, UPS Worldship.

The front end of our web store is hosted with Shopify. Sales that are made through the web store are passed through to Brightpearl. Along the way we charge credit cards through, and have implemented  a number of custom plugins or “apps” to modify both the Shopify store, and the Brightpearl application, notably from ShipRobot and Bold Apps.

The store took some time to get up and running. We had originally contracted with an outside consulting firm. After it became clear that they weren’t smarter than we are, we brought the implementation in-house, and worked directly with our cloud vendors at Shopify and Brightpearl.

One of the trade-offs of cloud-based computing is that an application of any complexity can easily rely on code from multiple providers, so that some of the time saved in not coding, is spent on “vendor management”. We have good relationships with our vendors, all of whom have provided timely tech and implementation support.

We replaced a system that used a web site hosted by a local programmer which fed the MOM Mail Order Manager program. Although we launched with a full suite of capability that matched our original specification, it is very feasible to launch a less elaborate site with Shopify, and add functionality as your needs grow.

Powershell: Basic Address List Processing

One of the great things that was always a little bit fun (well, for some us… what passes for fun) is processing lists using Unix/Linux shell scripts and tools. When you are in practice, you can perform miracles; leap tall buildings in a single bound. Let’s see what we can do with a file using Powershell commands.

I received a file of “lapsed” donors. These are donors to our organization that gave to one of our campaigns in the past, but haven’t given recently. We know they were our friends in the past, and we think they still are, so we’d like to contact them for a one-time mailing, and/or add them to our master mailing lists.

The file is named “lapsed.csv”. The csv extension suggests this is a text file with a “comma separated values. And indeed, if I look at this file, at the powershell prompt, it is easily visible.

PS C:UsersLarrypowershell> cat lapsed.csv

This shows a comma delimited file with a header line:

Joe Blow, Joe,,,123 W 57TH St Apt 123,New York,NY,10019
Jill Smith,Jill,Howard Services,123 Poor Farm Rd,Colchester,VT,05446

Our standard is:

Org, fname, lname, address1, address2, city, state, zip

So, among other things, we’re going to want to change the order of the information in the fields, as well as the field names.

First thing to do is to import the file into a single PowerShell variable, and then see what we’ve got.

PS>$lapsed= Import-CSV lapsed.csv

Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
Addressee : Jill Smith
 first : Jill
 spouse :
 Organization : Howard Services
 Street : 123 Poor Farm Rd
 City : Colchester
 State : VT
 ZIP : 05446

Using import-CSV, the file is converted into a series of custom objects with members that correspond to the existing field names …so further manipulations can be done using object manipulations, instead of just a bunch of searching and replacing. (Well that’s the theory anyway).

We can find out the number of records we have by looking at the count attribute.

And we can see the “members” or field names of each record by using Get-Member

PS>$Lapsed |Get-Member

TypeName: System.Management.Automation.PSCustomObject
Name MemberType Definition
 ---- ---------- ----------
 Equals Method bool Equals(System.Object obj)
 GetHashCode Method int GetHashCode()
 GetType Method type GetType()
 ToString Method string ToString()
 Addressee NoteProperty System.String Addressee=Joe Blow
 City NoteProperty System.String City=New York
 first NoteProperty System.String first=Joe
 Organization NoteProperty System.String Organization=Paul C Bunn Elementary
 spouse NoteProperty System.String spouse=
 State NoteProperty System.String State=NY
 Street NoteProperty System.String Street=123 W 57TH St Apt 123
 ZIP NoteProperty System.String ZIP=10019

Ok…we knew the field names before by just looking at the raw .CSV file. But now we have seen how the Import-CSV command converts the .CSV file to an array of objects with a type of PSCustomObject.  Since each address record is an object, the way we manipulate it is to use object methods.

1. Add a lname field for the last name
PS>$lapsed | Add-Member -Name “lname”

That takes the $lapsed table and pipes it to the Add-Member cmdlet. This adds the the member to EACH object in the table, rather than adding it to the table itself.

2. Add a fname and lname fields for the first and last name
PS>$lapsed | Add-Member -Name “fname” -MemberType NoteProperty -Value “”

PS>$lapsed | Add-Member -Name “lname” -MemberType NoteProperty -Value “”

Now the fields look like this:
Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP :
 lname : ""
 fname : ""

3. Copy data from the first name and organization fields to their new fields.
PS>$lapsed | ForEach-Object ($_.fname) {$_.fname=$_.first}
PS>$lapsed | ForEach-Object ($ {$$_.Organization}
This leaves us with a record looking like this.

Addressee : Joe Blow
 first : Joe
 spouse :
 Organization : Paul C Bunn Elementary
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname :
 fname : Joe
 org : Paul C Bunn Elementary

6. Having copied the data from the old fields to the new ones, we can delete the old fields.
There isn’t a cmdlet to remove an object member, so the you have to use a different nomenclature. (Note To Self… opportunity to make a custom cmdlet?)

PS> $lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘Organization’)}
PS>$lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘first’)}
PS>$lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘spouse’)}

Now a typical record is starting to look much more like what we want it to look like.

 Addressee : Joe Blow
 Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname :
 fname : Joe
 org : Paul C Bunn Elementary

7. We still need to pick out the last name from the Addressee field.
There might be a couple approaches to this using regular text search methods:
a. Given a string “Joe Blow”, we could find the first blank character, and then take anything to the right of if as our last name.
b. We could start at the right hand side and count backwards until we get to a space.
c. If there are word functions, we can choose the right-most word in the string.
d. Use the split function. This is what we’ll use.

PS> $lapsed | ForEach-Object ($_){$_.lname=$_.Addressee.split()[-1]}

8. Finally, we can eliminate the “Addressee” field
PS> $lapsed | ForEach-Object ($_){$_.PsObject.Members.Remove(‘Addressee’)}

Street : 123 W 57TH St Apt 123
 City : New York
 State : NY
 ZIP : 10019
 lname : Blow
 fname : Joe
 org : Paul C Bunn Elementary

9. Time to export back to a CSV file.
PS> $lapsed | Export-Csv -Confirm -Path “C:UsersLarryPowershellulapsed.csv” -NoTypeInformation

10. Almost done. The one frost is that the field order isn’t exactly as I’d like. This can be fixed with the Select-Object cmdlet.

PS > $lapsed | Select-Object -Property fname,lname,org,street,city,state,zip |
Export-CSV -Path “C:UsersLarryPowershellulapsed.csv” -NoTypeInformation

Recall when typing a string of commands with a pipeline, the pipe delimter will also act as a “newline”, so you can break the command up over the course of a couple of lines and have the full pipeline execute as one command.


As they say on public television: “Many thanks to the following:”

“jrv” on Microsoft Technet

“Root Loop” on StackOverflow

More Info:

More about the split function in Powershell help
PS> Get-Help about_Split

PowerShell Functions II – Output function results as a hash table

In a previous post I wrote a butt-simple function with three parameters, called it a couple ways, and talked about how the function returns its data either as a single string or as an array of variables. Before leaving this, I’m going to experiment about returning data as a hash table.

function Get-ReallySimple($fname,$lname,$age) {

return $OutTable


This prints out nicely, except it is in the wrong order.

 PS>Get-ReallySimple Joe Dokes 32

 Name                           Value
----                            -----
Age                            32
FirstName                      Joe
LastName                       Dokes

By adding the ordered keyword in front of the hash table definition, we can get the hash table to print in the order in which we asked for the function’s input paramenters.

function Get-ReallySimple($fname,$lname,$age) {

$OutTable= [ordered] @{"FirstName"=$fname;"LastName"=$lname;"Age"=$age}
return $OutTable


PS>Get-ReallySimple Joe Dokes 32

Name                           Value
—-                                —–
FirstName                     Joe
LastName                     Dokes
Age                               32

Since the result is a hash table we can also return a portion of the table using dot notation. We surround the functional call with parentheses to force the call to be evaluated first.

PS>(Get-ReallySimple Joe Dokes 32).FirstName


Of course instead of the the parentheses we probably should assign the call to a variable; and then dot notate that.

$Zilch=Get-ReallySimple Joe Dokes 32


There is  an Output Type attribute that can be applied to a function, but this appears to be cosmetic. It doesn’t enforce or do any error checking.  If your function returns a string, and Output type says “hashtable”, nothing happens; the function still return the string.  According to the help documentation the purpose of the Output Type attribute is to provide documentation; but when I queried with Get-Command Get-ReallySimple,  it returned nothing for the output.

Powershell Functions I

Call me crazy, but don’t think the whole method of declaring and calling function parameters is particularly intuitive in PowerShell. But let’s go back to the notion of the function.  What is a function (in PowerShell)?

A function is a block of code that takes variables as input, manipulates those variables and returns one more variables as a result.  In PowerShell, a function can exist independently as a cmdlet (either in its own standing .ps1 file or as part of a .pm1 module file.) or as a block of code in a larger script.  If the function is used within a larger script, the function code must precede the line which calls the function. This can get unwieldy and there is a workaround; but is the price we pay for scripting, instead of programming  in C# or whatever.

function Get-ReallySimple($lname,$fname,$age){

"You passed in the following: $lname, $fname, $age"


PS>Get-MyParams Joe Dokes 32   


You passed in the following: Joe, Dokes, 32

Note that this is a single string displaying the three variables.

I think that’s about the simplest function you  can write. It takes (up to) three parameters, and returns them as part of a single string which is output to the pipeline.

Functions return their results to the pipeline, with or without a return key word at the end of the function.

if you try calling the function with “conventional” notation using paranthesis, PowerShell returns an error:


At line:1 char:21
+ Get-ReallySimple(joe,dokes,32)
+                     ~
Missing argument in parameter list.
    + CategoryInfo          : ParserError: (:) [], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : MissingArgument

If you change the code just display the variables, the resulting output is an array.

function Get-ReallySimple($lname,$fname,$age) {

This looks nice in a grid…

Get-ReallySimple joe dokes 32 | Out-GridView 


…and shows that the output was an array with three elements, two strings and an integer.

PowerShell functions can accept input variables as part of the function call, or, optionally, from the pipeline. If a function is called with variables, they are simply added after the function call, without parentheses.

Get-MyParams Joe Dokes 32   



If you type this second example, you are actually passing in an array to the function. Who knew?

Windows Remoting

Remoting allows you to log into the command line of the target machine. It is similar to the SSH command on Linux, in that it provides access only to the command line.

It has to be set up on beforehand by using PowerShell with Admin privileges on both the controlled and controlling machine.

Note:  You will eventually need to the name of your machine. You can find this out with the following command:

Get-ChildItem env:ComputerName

Here are the steps to set it up:  At the PowerShell prompt on each machine

1. Turn on PSRemoting.

PS> Enable-PSRemoting  -Force  # The Force parameter eliminates a lot of annoying questions.

2. For machines that are not on a domain-based nework, you need to configure the trusted hosts lists on each machine.

PS> Set-Item wsman:localhostclienttrustedhosts *

This allows any computer to connect to this machine.  If you want to restrict the trusted hosts, you can use a comma-separated list of IP addresses, or computer names.

3. Restart the WinRM service

PS> Restart-Service WinRM

Repeat the three steps above for any machine that you wish to access via Remoting.

To test the connection:

Note:  You will eventually need to the name of your machine. You can find this out with the following command:  Get-ChildItem env:ComputerName

Test-WsMan <computename>

If the connection is made you’ll get a reply…if not then the connection will appear to hang at the prompt.

Now for the real deal:

To execute a single command from the source machine on the target machine.

Invoke-Command -ComputerName <computername> -ScriptBlock {command } -credential <username>

The credential paraeter will put up a name-password gui box to get your user credentials.

You can start an entire remote session with a similar command

Enter-PSSession -ComputerName <computer> – Credential <username>

This will give you a command line prefaced with the computer name.

[Win7VM] PS >

You can run commands that reside only on the remote computer….or run PS commands on your own computer that execute on the remote computer.

To return to your own machine:

[Win7VM] PS> Exit

Because Remoting runs as a service, you don’t have to have PowerShell open on the remote machine to be able to connect to it.

If you set this up first using a Virtualbox virtual machine, you can see how both ends of the connection work without inconveniencing a user.

Test PowerShell scripts with VirtualBox

I’m at the point where I am going to deploy some PowerShell scripts to my end-users, and I want to test the scripts on a fresh installation of Windows before trying them on the user’s workstations.

I use VirtualBox to create virtual machines for Powershell testing. Virtual box works on Linux, Mac and Windows host machines. My 8 gig Win 7 box works fine with one or two “guest OS’s”. On my 4 gig (ancient) iMac, it works, but its pretty slow.

One thing that I find amazing, is you have to run updates on all those Windows Virtual machines. Don’t expect to be fully productive on Tuesdays or Wednesdays, when Microsoft sends out Windows updates. It isn’t unusual for updates to run for an hour or more.

Be sure to install the VirtualBox “guest additions” within your Windows VM, once you’ve got your Windows VM up and running. You may also want to change the network settings to “bridged”, so that your VM is on the same network subnet as your host machine.

One other disconcerting thing; your Windows 7 desktop may come up with a black background depending on whether you are running the aero interface and other such fripperies. You can turn all this stuff on if you want; but it will slow down the performance.

More details on setting up VirtualBox are located  here and here.


If you have installed Windows 7, you may find that it has come with Powershell V.2 out of the box. You can test that by starting a PowerShell session from the command box (just type Powershell.exe).  Once Powershell is up, issue the following command at the prompt:

get-host | select-object version 

If it isn’t 4.0 or later, download the latest version of the Windows Management Framework from Microsoft.