Output PScustom object to CSV-CodePudding

A little background info;

i'm new to Powershell, but i do have some experience in VBA (excel mostly)

for a project i need to create a program to couple SQL result from a single column to input from users with a USB barcode scanner (just works like keyboard input enter after each scan)

i run a SQL query which results the Instrument column from a table, this is stored in $values.instrument

Now i have a read-host for users to input a barcode that should match this instrument (scanned with usb barcode reader). and in case multiple times the same barcode is scanned, it's shown instrument (in the read-host) should be added to this barcode. So logically we do a group-object on the Barcode property. Now i can't directly export this so i made a [pscustomobject] to create a nice table. It shows correctly how i want in the shell, but export-csv is not working a i expect.

The code is as follows;

foreach($inst in $values.Instrument) #loop through every Instrument result from SQL

{
    
    $temp = New-Object System.Object
    $temp | Add-Member -MemberType NoteProperty -Name "Barcode" -Value (Read-Host -Prompt "scan de barcode van: $($inst)")
    $temp | Add-Member -MemberType NoteProperty -Name "instrument" -Value $inst

    $instrument_data.Add($temp) | Out-Null
  
}


$groups = $instrument_data | Group-Object -property Barcode

 foreach ($instrument in $groups) {
        #$barcode = $instrument.Name
        #$instrument2 = ($instrument.Group.Instrument | Foreach-Object { "$_" }) -join ','
        [pscustomobject]@{'Barcode' = $instrument.Name; 'Instrument' = ($instrument.Group.Instrument | Foreach-Object { "$_" }) -join ','}
    } 
 


 $groups | export-csv -Path "export-path\file.csv" -NoTypeInformation

this results in the follwing on the terminal as i expect;

Terminal output I tried the normal export-csv as that should normally do the export like how the results are shown in terminal, but for some reason i see the system.object stuff instead of the format as shown in the terminal from the resulting [pscustomobject] :

csv output

CodePudding user response：

Try following :

$table = [System.Collections.ArrayList]::new()
foreach($group in $groups)
{
  $group | Format-Table
  table.Add($group) 
}
$table | Format-Table

CodePudding user response：

i found a solution, some of your answers pushed me in the right direction;

foreach($inst in $values.Instrument) #loop through every Instrument result from SQL

{
    
    $temp = New-Object System.Object
    $temp | Add-Member -MemberType NoteProperty -Name "Barcode" -Value (Read-Host -Prompt "scan de barcode van: $($inst)")
    $temp | Add-Member -MemberType NoteProperty -Name "instrument" -Value $inst

    $instrument_data.Add($temp) | Out-Null
  
}


$groups = $instrument_data | Group-Object -property Barcode

    $groups | select Name, 
    @{Name=’instrument’;Expression={[string]::join(“,”, ($_.Group.Instrument))}} | ConvertTo-Csv -NoTypeInformation | % {$_ -replace '"', ""} | Out-File $outfile -Force -Encoding ascii

also changed the path of csv export to a variable for easier change in the future to $outfile

but this gives the solution i want, then only a header removal when the file is done and our software package can read the CSV :).

thank you all for the fast responses.