Find/Replace Data in .CSV file

Tagged: , , ,

This topic contains 3 replies, has 3 voices, and was last updated by Profile photo of Colter Colter 1 month ago.

  • Author
    Posts
  • #57073
    Profile photo of Colter
    Colter
    Participant

    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
    Profile photo of Olaf Soyk
    Olaf Soyk
    Participant

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

  • #57116
    Profile photo of Rob Simmers
    Rob Simmers
    Participant

    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
    Profile photo of Colter
    Colter
    Participant

    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])]}},
    

You must be logged in to reply to this topic.