I've recently dived back into PowerShell scripting and I've run into a little snag. I'm trying to write a script that takes the first two columns of a CSV and expands them for each entry in the third column. My goal is for every individual to appear on their own row in the output, including the corresponding group and description from the original row. However, while the console outputs seem correct, my final CSV file ends up with the last row's data repeating for all entries. Here's the code I've written so far:
[array]$newCSV = @()
[PSCustomObject]$newitem = @{
Group = ''
Description = ''
Person = ''
}
[string]$srcFile = 'D:DataInput.csv'
$csv = Import-Csv $srcFile
foreach ($item in $csv) {
$group = $item.group
$desc = $item.description
$members = $item.members
foreach ($mbr in $members.Split(',')) {
$newItem.group = $item.Group
$newItem.description = $item.Description
$newItem.person = $mbr
$newCSV += $newItem
}
}
$newCSV | Export-Csv -Path C:QTM-ExportsSNOW-Approvals-Results.csv -NoTypeInformation
Here's a sample of what my input data looks like:
"Group1","Description1","Bob,Sam,Fred"
"Group2","Description2","Bob"
"Group3","Description3","Bob,Sam,Dave,Mike"
Any help would be greatly appreciated!
3 Answers
It seems like your main issue is how you're managing the $newItem object. Since you're defining it outside your loops, you're modifying the same object repeatedly rather than creating a new one each time. Try instantiating a new object within the inner loop. That should help with getting the correct output!
Could you share what your expected output looks like? Are you aiming for something like:
"Group1","Description1","Bob"
"Group1","Description1","Sam"
"Group1","Description1","Fred"
"Group2","Description2","Bob"
etc.
That would help clarify!
You're using some redundant code that could be simplified. For instance, using `+=` repeatedly can be inefficient. You could define a new [PSCustomObject] inside the loop for cleaner testing and better readability:
$newCSV = foreach ($item in $csv) {
foreach ($mbr in $item.members.Split(',')) {
[PSCustomObject]@{
Group = $item.Group
Description = $item.Description
Person = $mbr
}
}
}
This will make sure each entry is handled as a separate object!
Yes, that's exactly what I need! I want each person as a unique record with the related group and description.