Using a PoSh variable in a string- #SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

This is something I haven’t quite understood or used often, but I’ve been aware of it and wanted to learn more.

A member at SQLServerCentral wanted to embed a value in a string, and was having issues. In this case, they had this code:

$dt = get-date -format "_yyyyMMMdd_HHmss"
Invoke-Sqlcmd -Query "SELECT * FROM [Sandbox].[dbo].[Customer]" -ServerInstance "Plato\SQL2017" |
Export-Csv -Path E:\Documents\sql\$dt.csv -NoTypeInformation

In this case, there was an error with the Export-Csv cmdlet, with a syntax issue near the period. I suspected this was some variable expansion that didn’t work.

I found this post that helped me understand a bit more and decided to experiment a bit. Let’s try some things. First, I used to do this type of code:

$dt = Get-Date –format “yyyyMMdd”
write-host(“Today is “ + $dt)

I then see this:

2019-12-02 14_42_23-cmd - powershell

However, I can use this code:

 write-host("Today is $dt")

That gives me the same result. Apparently, I can include the variable in the string and it gets expanded. This works with just a string, as shown here:

PS C:\Users\Steve> write-host("Today is $dt.csv")
Today is 20191202.csv
PS C:\Users\Steve>

Not the error I expected, but this makes more sense with a value that’s needed in a parameter. The blog helps explain this with the following code:

PS C:\Users\Steve> $directory = Get-Item 'c:\windows'
PS C:\Users\Steve> $message = "Time: $directory.CreationTime"
PS C:\Users\Steve> $message
Time: C:\windows.CreationTime
PS C:\Users\Steve>

An issue. However, if I use the expression evaluation of $() inside, I get this:

PS C:\Users\Steve> $message = "Time: $($directory.CreationTime)"
PS C:\Users\Steve> $message
Time: 09/15/2018 00:09:26
PS C:\Users\Steve>

That’s the trick I needed for Export-Csv. I used this code in the last line:

Export-Csv -Path E:\Documents\sql\$($dt).csv –NoTypeInformation

And the code worked as expected.

There’s likely more I should know, but I will start to use varaiables inside strings when I just need the value of the variable as a string. If I need this to better work with some property, method, or parameter value, I’ll use $() around the variable.


This post was about 20 minutes of me experimenting with a few things and slowly working out how some variables worked. I somewhat wrote this as I was experimenting, adding in the code that ran.

A good example of writing while learning. You could do this on your blog as you learn to work through some code or a feature.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.