Consolidating lines in csv file

Welcome Forums General PowerShell Q&A Consolidating lines in csv file

Viewing 5 reply threads
  • Author
    Posts
    • #236470
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      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! 🙂

    • #236476
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

      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. 😉 … the result would look like this:

      Folder       Users
      ------       -----
      C:\myfolder1 user1,user2,user3
      C:\myfolder2 user1,user2
      C:\myfolder3 user1
      
    • #236539
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      Hey Olaf,

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

      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

       

       

      • This reply was modified 3 weeks, 6 days ago by Robbie Cook.
    • #236746
      Participant
      Topics: 0
      Replies: 30
      Points: 284
      Helping Hand
      Rank: Contributor

      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.

      • This reply was modified 3 weeks, 6 days ago by AndySvints.
    • #236764
      Participant
      Topics: 5
      Replies: 2384
      Points: 6,066
      Helping Hand
      Rank: Community MVP

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

      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
      
    • #236809
      Participant
      Topics: 1
      Replies: 4
      Points: 25
      Rank: Member

      Thank you gents, both are great options! 🙂

Viewing 5 reply threads
  • You must be logged in to reply to this topic.