Consolidating lines in csv file

PowerShell Help
1

Hey guys

I have a large CSV file (approx 15,000 rows) that contains a foldername and a username on each line. My goal is to end up with 1 line per foldername along with all of the usernames.

example file

Folder,Username
C:\myfolder1,user1
C:\myfolder1,user2
C:\myfolder1,user3
C:\myfolder2,user1
C:\myfolder2,user2
C:\myfolder3,user1

desired file

Folder,Username1,Username2,Username3
C:\myfolder1,user1,user2,user3
C:\myfolder2,user1,user2
C:\myfolder3,user1

 

Here’s my code so far and although it works - it takes forever as i’m constantly rereading $data.

$data = import-csv sourcefile.csv
$data = $data | sort Folder

$fullinfo = @()

ForEach($line in $data)
{

$info = new-object -typename PSObject
$CurrentFolder = $line.folder

IF(!($fullinfo | ?{$_.folder -eq $CurrentFolder}))
{

$info | add-member -NotePropertyName Folder -NotePropertyValue $CurrentFolder
$results = ($data | ?{$_.folder -eq $CurrentFolder}).user | sort -Unique
$results | %{$I=0}{$i++ ; $info | add-member -notepropertyname UserName$i -notepropertyvalue $_}
$Fullinfo += $info

}
}

$fullinfo | export-csv output.csv

I’m hoping somebody can help make my code more efficient, allowing the entire task to take minutes instead of hours.

 

Thank you in advance! :slight_smile:

2

Robbie, welcome to Powershell.org. Please take a moment and read the very first post on top of the list of this forum: Read Me Before Posting! You’ll be Glad You Did!.

When you post code, error messages, sample data or console output format it as code, please.
In the “Text” view you can use the code tags “PRE”, in the “Visual” view you can use the format template “Preformatted”. You can go back edit your post and fix the formatting - you don’t have to create a new one.
Thanks in advance.

The way you wanted to do would not provide a valid CSV file. Try this:

$SourceData = 
@'
Folder,Username
C:\myfolder1,user1
C:\myfolder1,user2
C:\myfolder1,user3
C:\myfolder2,user1
C:\myfolder2,user2
C:\myfolder3,user1
'@ |
ConvertFrom-Csv

$SourceData | 
Group-Object -Property Folder |
ForEach-Object {
    [PSCustomObject]@{
        Folder = $_.Name 
        Users  = $_.Group.UserName -join ','
    }
} -OutVariable FullInfo

$FullInfo  | Export-Csv -Path output.csv -NoTypeInformation

Of course you should provide your source data with an Import-Csv. :wink: … the result would look like this:

Folder       Users
------       -----
C:\myfolder1 user1,user2,user3
C:\myfolder2 user1,user2
C:\myfolder3 user1
3

Hey Olaf,

Thank you very much my friend. I have now fixed my original post :slight_smile:

Your code was of great help! I had completely forgotten about the Group-Object cmdlet.

Here’s my new code, it now takes approximately 90 seconds to run!!!

$fullinfo = @()

$data = import-csv sourcefile.csv
$Data | Group-Object -Property Folder | 
ForEach-Object {
$info = new-object -typename PSObject
$info | add-member -NotePropertyName Folder -NotePropertyValue $_.name
$_.group.user | sort -Unique | %{$i=0}{$i++;$info | add-member -NotePropertyName user$i -NotePropertyValue $_}

$fullinfo += $info
}

$fullinfo | Export-csv Destfile.csv -NoTypeInformation

Thanks again!
Robbie

 

 

4

Hello Robbie,

If you would change your $fullinfo to ArrayList (System.Collections.Arraylist) and instead of using += would use ArrayList.Add() it will be even faster.
Like this:

$FullInfo = New-Object System.Collections.ArrayList
$Data = import-csv sourcefile.csv

$Data | Group-Object -Property Folder | ForEach-Object {
$info = new-object -typename PSObject $info | add-member -NotePropertyName Folder -NotePropertyValue $_.name $_.group.user | sort -Unique | %{$i=0}{$i++;$info | add-member -NotePropertyName user$i -NotePropertyValue $_}
$Fullinfo.Add($info) | out-null
}

$Fullinfo | Export-csv Destfile.csv -NoTypeInformation

Hope that helps.

5

There’s an even easier way than that:

$data = import-csv sourcefile.csv
$fullinfo =
$Data | Group-Object -Property Folder | 
ForEach-Object {
    $info = new-object -typename PSObject
    $info | add-member -NotePropertyName Folder -NotePropertyValue $_.name
    $_.group.user | Sort-Object -Unique | ForEach-Object { $i = 0 } { $i++; $info | add-member -NotePropertyName user$i -NotePropertyValue $_ }
}
$fullinfo | Export-csv Destfile.csv -NoTypeInformation
6

Thank you gents, both are great options! :slight_smile: