Find/Replace Data in .CSV file

Welcome Forums General PowerShell Q&A Find/Replace Data in .CSV file

This topic contains 3 replies, has 3 voices, and was last updated by

 
Participant
1 year, 11 months ago.

  • Author
    Posts
  • #57073

    Participant
    Points: 0
    Rank: Member

    Hi Folks,

    I have a .csv file with an output that looks likes this:

    PS> Import-Csv C:\data.csv | Format-Table -AutoSize
    
    USERID DOMAIN ADJ CBM CCA CME DCM MQU POE PSM
    ------ ------ --- --- --- --- --- --- --- ---
    IEA0F  AA     .   7   1   5   5   1   7   1  
    IEA0N  AA     .   7   .   5   5   1   7   1  
    IEB5J  AA     .   .   .   .   .   .   .   .  
    IEDPD  AA     .   7   1   5   5   1   7   1  
    IEDVP  AA     .   7   1   5   5   1   7   .  
    IED9B  AA     1   7   1   5   5   1   7   1  
    IEEDR  AA     .   7   1   5   5   1   7   .
    

    For a report I need to generate, I need to remove all "." and replace any numerical value in each column with the column's header. So that it looks like this:

    USERID DOMAIN ADJ   CBM   CCA   CME   DCM   MQU   POE   PSM
    ------ ------ ---   ---   ---   ---   ---   ---   ---   ---
    IEA0F  AA           CBM   CCA   CME   DCM   MQU   POE   PSM  
    IEA0N  AA           CBM         CME   DCM   MQU   POE   PSM  
    IEB5J  AA     
    IEDPD  AA           CBM   CCA   CME   DCM   MQU   POE   PSM  
    IEDVP  AA           CBM   CCA   CME   DCM   MQU   POE   PSM 
    IED9B  AA     ADJ   CBM   CCA   CME   DCM   MQU   POE   PSM  
    IEEDR  AA           CBM   CCA   CME   DCM   MQU   POE   
    

    How can I accomplish this?

    Thanks!

  • #57077

    Participant
    Points: 8
    Rank: Member

    You will need a Foreach-Loop and a conditional Replace in that loop

  • #57116

    Participant
    Points: 1
    Rank: Member

    You can do this using the same method from your previous post:

    $data = Import-CSV C:\Users\Rob\Desktop\Archive\test.csv -Header User,Location,AccessFull
    
    $data | 
    Select User,
           Location,
           @{Name="Access1";Expression={($_.AccessFull[0]).Replace(".","")}},
           @{Name="Access2";Expression={($_.AccessFull[1]).Replace(".","")}},
           @{Name="Access3";Expression={($_.AccessFull[2]).Replace(".","")}},
           @{Name="Access4";Expression={($_.AccessFull[3]).Replace(".","")}},
           @{Name="Access5";Expression={($_.AccessFull[4]).Replace(".","")}},
           @{Name="Access6";Expression={($_.AccessFull[5]).Replace(".","")}}
    

    The Expression is a scriptblock, so you can execute code like an if block, call a function or use a method like Replace. You can also do a hash table lookup like this:

    $data = Import-CSV C:\Users\Rob\Desktop\Archive\test.csv -Header User,Location,AccessFull
    
    $hash = @{
        "." = "None"
        "1" = "MAX"
        "2" = "MED"
        "3" = "MIN"
    }
    
    $data | 
    Select User,
           Location,
           @{Name="Access1";Expression={$hash[[string]($_.AccessFull[0])]}},
           @{Name="Access2";Expression={$hash[[string]($_.AccessFull[1])]}},
           @{Name="Access3";Expression={$hash[[string]($_.AccessFull[2])]}},
           @{Name="Access4";Expression={$hash[[string]($_.AccessFull[3])]}},
           @{Name="Access5";Expression={$hash[[string]($_.AccessFull[4])]}},
           @{Name="Access6";Expression={$hash[[string]($_.AccessFull[5])]}}
    

    Output:

    User     : IEA0A
    Location : AA
    Access1  : None
    Access2  : MED
    Access3  : MAX
    Access4  : MAX
    Access5  : MIN
    Access6  : MAX
    
    User     : IEH6F
    Location : AC10
    Access1  : None
    Access2  : None
    Access3  : None
    Access4  : None
    Access5  : MAX
    Access6  : None
    
  • #57151

    Participant
    Points: 0
    Rank: Member

    Thanks Rob. I created a separate hash variable for each access column and got the output I needed.

    $hash1  = @{
        "." = ""
        "1" = "ADJ"
        "2" = "ADJ"
        "3" = "ADJ"
        "4" = "ADJ"
        "5" = "ADJ"
        "6" = "ADJ"
        "7" = "ADJ"
        "8" = "ADJ"
        "9" = "ADJ"
               }
    $data | select userid,
                   domain,
                   @{Name="ADJ";Expression={$hash1[[string]($_.Access[0])]}},
    

The topic ‘Find/Replace Data in .CSV file’ is closed to new replies.