Sign in
Microsoft.com
 
Windows Server TechCenter
 
 
 
Windows Server TechCenter > Windows Server Forums > Windows PowerShell > help needed converting a csv
Ask a questionAsk a question
Search Forums:
 

Answerhelp needed converting a csv

  • Wednesday, November 04, 2009 12:55 PMElSuizo Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Hi,
    I have a csv containing 3 fields. The field "sPermissions" contains one or more values.
    This csv acts as input to a script. But the script can handle only one value per field.
    So I need some suggestions to convert this csv :

    displayName,samAccountName,sPermissions
    "John Doe",JD00,domain\helpdesk1%domain\helpdesk2%domain\helpdesk3
    "Brigitta Buh",BB00,domain\helpdesk1%domain\helpdesk3

    into this :

    displayName,samAccountName,sPermissions
    "John Doe",JD00,domain\helpdesk1
    "John Doe",JD00,domain\helpdesk2
    "John Doe",JD00,domain\helpdesk3
    "Brigitta Buh",BB00,domain\helpdesk1
    "Brigitta Buh",BB00,domain\helpdesk3

    thank you very much for help
    • Moved byMichael MainerMSFTWednesday, November 04, 2009 6:33 PMMove reason: moved to appropriate forum (Windows Powershell). (From:Development)
    •  
     

Answers

  • Wednesday, November 04, 2009 3:55 PMmjolinor Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    Powershell:

    $new_csv = @()
    $old_recs = import-csv "c:\somefolder\perms.csv"
     foreach ($old_rec in $old_recs){
      foreach ($perm in $old_rec.sPermissions.split("%"){
       $new_rec = "" | select displayName,samAccountName,sPermissions
       $new_rec.displayName = $old_rec.displayName
       $new_rec.samAccountname = $old_rec.samAccountName
       $new_rec.sPermissions = $perm
       $new_csv += $new_rec
      }
     } 
    $new_csv | export-csv "C:\somefolder\newperms.csv"
     
  • Wednesday, November 04, 2009 8:45 PMVadims PodansMVPUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    > $new_csv | export-csv "C:\somefolder\newperms.csv"

    it is a good practice to add -NoTypeInformation switch:

    $new_csv | export-csv "C:\somefolder\newperms.csv" -NoTypeInformation
    http://www.sysadmins.lv
     

All Replies

  • Wednesday, November 04, 2009 3:55 PMmjolinor Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    Powershell:

    $new_csv = @()
    $old_recs = import-csv "c:\somefolder\perms.csv"
     foreach ($old_rec in $old_recs){
      foreach ($perm in $old_rec.sPermissions.split("%"){
       $new_rec = "" | select displayName,samAccountName,sPermissions
       $new_rec.displayName = $old_rec.displayName
       $new_rec.samAccountname = $old_rec.samAccountName
       $new_rec.sPermissions = $perm
       $new_csv += $new_rec
      }
     } 
    $new_csv | export-csv "C:\somefolder\newperms.csv"
     
  • Wednesday, November 04, 2009 8:45 PMVadims PodansMVPUsers MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     Answer
    Sign In to Vote
    0
    Sign In to Vote
    > $new_csv | export-csv "C:\somefolder\newperms.csv"

    it is a good practice to add -NoTypeInformation switch:

    $new_csv | export-csv "C:\somefolder\newperms.csv" -NoTypeInformation
    http://www.sysadmins.lv
     
  • Wednesday, November 04, 2009 8:52 PMmjolinor Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote

    facepalm

     
  • Thursday, November 05, 2009 7:20 AMElSuizo Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    there is a missing closing ')' after expression part of foreach loop.

    foreach ($perm in $old_rec.sPermissions.split("%") ) << {


    But for now it works great !!!!

    Thank you very much
    Erwin
     
  • Thursday, November 05, 2009 11:57 AMmjolinor Users MedalsUsers MedalsUsers MedalsUsers MedalsUsers Medals
     
    Sign In to Vote
    0
    Sign In to Vote
    Sorry about that.  Actually think I made that harder than it needs to be:

    $new_csv = @()
    $old_recs = import-csv "c:\somefolder\perms.csv"
     foreach ($old_rec in $old_recs){
      foreach ($perm in $old_rec.sPermissions.split("%")){
       $new_rec = $oldrec
       $new_rec.sPermissions = $perm
       $new_csv += $new_rec
      }
     } 
    $new_csv | export-csv "C:\somefolder\newperms.csv" -notype