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]
:
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.