Find/Replace Data in .CSV file

PowerShell Help
1

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!

2

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

3

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
4

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