I'm running into some issues with my PowerShell script and could use a hand. I haven't worked with PowerShell in a while, and I think I might be making a simple mistake. I'm trying to take the first two columns from a CSV file and replicate these for each entry in the third column. My goal is to have each person's name on its own row, accompanied by the corresponding group and description from the source file. The problem is that although it looks correct when I display each line as I work with the array, the output file shows the right number of rows but keeps repeating the last set of data for every row. Here's the code I'm using:
[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 my data file:
"Group1","Description1","Bob,Sam,Fred"
"Group2","Description2","Bob"
"Group3","Description3","Bob,Sam,Dave,Mike"
Thanks a ton in advance!
3 Answers
I think the issue lies in how you're handling the $newItem variable. You're creating one single object and updating it repeatedly instead of making new objects each time. Every time you loop over the members, you only modify that one object, which results in multiple rows having the same data.
Try creating a new object within the inner loop each time you process a member.
There's quite a bit of unnecessary code in your example.
* Avoid using `+=` for arrays like you're doing; it can slow things down.
* You're doubling up on processing the data which isn't efficient.
* It’d be better to create a `[PSCustomObject]` inside the loop for cleaner output.
You could try something like this:
$srcFile = 'D:DataInput.csv'
$csv = Import-Csv $srcFile
$newCSV = foreach ($item in $csv) {
foreach ($mbr in $item.members.Split(',')) {
[PSCustomObject]@{
Group = $item.Group
Description = $item.Description
Person = $mbr
}
}
}
And that should give you results like:
Group Description Person
----- ----------- ------
Group1 Description1 Bob
Group1 Description1 Sam
Group1 Description1 Fred
Group2 Description2 Bob
Group3 Description3 Bob
Group3 Description3 Sam
Group3 Description3 Dave
Group3 Description3 Mike
Could you share an example of what your desired output looks like? Like, are you aiming for this format?
"Group1","Description1","Bob"
"Group1","Description1","Sam"
"Group1","Description1","Fred"
"Group2","Description2","Bob"
Stuff like that, right?
Yes, that's it! I have other data where users are unique records, and this setup is supposed to match a one-to-many relationship.