Powershell -join Object property values

Question

I'm attempting to produce a variable containing a string of Object properties that are joined with "','". This is to pass into a SQL select where clause. My input looks like this:

foreach ($csv in $CSVFiles) {
$csvOutput = Import-Csv $csv.FullName
$group = $csvOutput | Group-Object order-id, amount-type | Where-Object {$_.Group.'order-id' -ne '' -and $_.Group.'amount-type' -eq 'ItemPrice'}}

Within the above loop. I'm looking to retrieve the order-id and pass it into a new variable $OrdNum. I'm doing this like so:

$OrdNum = $group | Select-Object @{Name='order-id';Expression={$_.Values[0]}}

To perform the join I have attempted:

$OrdNum = ($group | Select-Object  @{Name='order-id';Expression={$_.Values[0]}}) -join "','"

This gives ','','','','','','','','','','','',' with no values. I have also tried:

$OrdNum = ($group | Select-Object -Property 'order-id') -join "','"

Which produces the same result.

I'm expecting $OrdNum to look like 12345','43567','76334','23765 etc. I'm working under the assumption that $OrdNum is required in that format to pass to this SQL query:

  $query = “SELECT ARIBH.ORDRNBR AS [ORDER No'],AROBP.IDRMIT AS [RECPT No'], FROM [XXXX].[dbo].[AROBP] FULL JOIN [XXXX].[dbo].[ARIBH] ON [XXXX].[dbo].[AROBP].[IDMEMOXREF] = [XXXX].[dbo].[ARIBH].[IDINVC] where ARIBH.ORDRNBR IN ('$OrdNum')"

Any assistance on the -join greatly appreciated OR if there is an alternative method to pass the values into SQL avoiding the -join then I'm open to suggestions. Thanks very much.

Thanks to Theo for the updated code. This works as expected. I have also reworked my existing example with the following. Preserving the original grouping, this also works:

foreach ($csv in $CSVFiles) {
$csvOutput = Import-Csv $csv.FullName -Delimiter "`t"
$group = $csvOutput | Group-Object order-id, amount-type | Where-Object {$_.Group.'order-id' -ne '' -and $_.Group.'amount-type' -eq 'ItemPrice'}
($OrdNum = $csvOutput | Where-Object {![string]::IsNullOrWhiteSpace($_.'order-id')}).'order-id' | Out-Null
$OrdNum = ($OrdNum.'order-id' | Select-Object -Unique) -join "','"
}

the .Name property from the G-O call should already have the value pairs of the two props you used to group the items. for my test, i used G-CI & Group-Object -Property LastWriteTime, Length. so the .Name prop for the last group is 2009-07-14 12:32:31 AM, 620888. does that do what you want? — Lee_Dailey
– Lee_Dailey, Commented Apr 22, 2020 at 8:41
As aside, straighten the curly opening quote in your $query “ --> ". — Theo
– Theo, Commented Apr 22, 2020 at 9:48
@Lee_Dailey Thanks for your response. Apologies, would you be able to post a quick example of how this approach can be used with the -join? — ColinA
– ColinA, Commented Apr 22, 2020 at 9:49
@ColinA - it looks like Theo has posted what you need. glad to know you got it working as wanted ... [grin] — Lee_Dailey
– Lee_Dailey, Commented Apr 22, 2020 at 11:48

nicholas · Accepted Answer · 2024-01-22 04:05:04Z

I'm not quite sure if I understand the question properly, but I don't really see the need for grouping at all, when all you seem to want is an array of 'order-id' values joined with a comma.

# assuming $CSVFiles is a collection of FileInfo objects
$OrdNum = foreach ($csv in $CSVFiles) {
    # import the csv and output the order numbers that match your where condition
    (Import-Csv -Path $csv.FullName | Where-Object { ![string]::IsNullOrWhiteSpace($_.'order-id') -and $_.'amount-type' -eq 'ItemPrice'}).'order-id'
}

# if needed you can de-dupe the returned arrau with either `$OrdNum | Select-Object -Unique` or `$OrdNum | Sort-Object -Unique`
# join the array elements with a comma to use in your query
$OrdNum = $OrdNum -join ','

As per your comment, you need the grouping for other purposes later on.

In that case, something like this could work for you:

# create a List object to collect the order-id values
$orders = [System.Collections.Generic.List[string]]::new()

# loop through the CSV files and collect the grouped data in variable $group
$group  = foreach ($csv in $CSVFiles) {
    # import the csv and output objects that match your where condition
    $items = Import-Csv -Path $csv.FullName | Where-Object { ![string]::IsNullOrWhiteSpace($_.'order-id') -and $_.'amount-type' -eq 'ItemPrice'}
    if ($items) {
        # add the 'order-id' values to the list
        $orders.AddRange([string[]]($items.'order-id'))
        # output the grouped items to collect in variable $group
        $items | Group-Object order-id, amount-type
    }
}

# join the elements with a comma to use in your query
$OrdNum = $orders -join ','

^{P.S. You need the [string[]] cast for the AddRange() method to avoid exception: Cannot convert argument "collection", with value: "System.Object[]", for "AddRange" to type "System.Collections.Generic.IEnumerable``1[System.String]": "Cannot convert the "System.Object[] " value of type "System.Object[]" to type "System.Collections.Generic.IEnumerable``1[System.String]"."}

Thanks for the suggestion. The grouping is required for some Measure-Object activities later in the script. I was hoping to use the group as it is already de-duped. I don't want to have to import the files twice.
@ColinA I have added new code to both do the grouping and at the same time get a list of order-id values to use in your query

Collectives™ on Stack Overflow

Powershell -join Object property values

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related