Powershell - passing variable into loop for string with quotes

Question

I'm trying to understand how to setup my script so that single quotes will wrap around my variable. I have a list of 1500 customers I need to repeat my script for, so my thought as to do a foreach loop.

$customerlist = Invoke-Sqlcmd -Query "SELECT [CustomerNo] FROM [TABLE]" -ServerInstance "SERVER\INSTANCE"
#Loop through 
foreach ($customer in $customerlist)
{
    $inputParams = @{
      "CustomerNo" = "'"+$customer+"'";
    }
....Do rest of script
}

I need the $customer variable in my $inputparams to show with the string value in single quotes, e.g. '01233456' instead of just 0123456. I've tried several different iterations of "'"+$customer+"'" but cannot seem to get the correct syntax. Could someone help me out?

Looks okay. Is it that you are losing the leading 0s? Are you getting back int or string from invoke-sqlcmd. Without seeing more of "Do rest of script" it's hard to tell where you are having an issue. "'"+$customer+"'" should do what you want or "'$customer'" — Daniel
– Daniel, Commented Aug 9, 2021 at 18:01
@Daniel The rest of my script is to print SSRS reports to PDF. I pass CustomerNo as a parameter to the report so that the SSRS only runs 1 customer at a time. If I hard-code a customer # into PowerShell, "CustomerNo" = '0123456'; it runs just fine. But using the $customer values from the initial query (even if I modify the query to list 1 result) seems to break the script. — MattC
– MattC, Commented Aug 9, 2021 at 18:15
as an alternate method ... take a look at the -f string format operator. this >>> -f Format operator - PowerShell - SS64.com — ss64.com/ps/syntax-f-operator.html — Lee_Dailey
– Lee_Dailey, Commented Aug 9, 2021 at 18:51

Cpt.Whale · Accepted Answer · 2021-08-09 18:20:14Z

2

Invoke-Sqlcmd returns [System.Data.DataRow] type objects with the field name(s) and value(s) of your query result as its properties (basically a table). To select the value in the CustomerNo, you must specify the value by name:

# Expand the CustomerNo property to strings
foreach ($customer in $customerlist.CustomerNo) { }

# Or, later in the script:
$inputParams = @{
  CustomerNo = "'$($customer.CustomerNo)'"
}

answered Aug 9, 2021 at 18:20

Cpt.Whale

5,3911 gold badge15 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MattC Over a year ago

@Capt.Whale That appears to be working now! My users added a request to drop the reports into their own folder. "later in the script" I set t$baseFolder = "\\NetworkShare\CustomerReports\" . How Might I grab "SalesPerson" from $customerlist. I tried; $baseFolder = "\\NetworkShare\CustomerReports\"+$Customerlist.SalespersonNo+"\" but it is crating a folder with all of the SalesPersons name's combined instead of 1 folder for each.

MattC Over a year ago

@Capt.Whale : disregard the previous, I got it working after another hour of tinkering.

Collectives™ on Stack Overflow

Powershell - passing variable into loop for string with quotes

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related