0

I have been trying to find a solution to this problem, I am creating a CSV from a larger File, selecting specific columns, and I have received great support from here, and that task is being done, but My CSV file contains values that i would like to replace, for example, the column 'User Name', I only would like to keep 1 user, meaning delete everything after the "," and I got a Regex that looks like
(,.*$)|(not applicable)|(sscope)

That's what i would like to replace with empty string, but I am unable to get that to work, it deletes everything sometimes.

The value in the User Name column looks like this

User Name

B2 cell : not applicable
B3 cell : sscope, sscope
B4 cell : sscope
B5 cell : sscope, sscope, hernhng002, coanbvf001, clacbff01, polsbvcw04, taylor38, milthy12, hic6yth31, carruhgy58, grabngh09, starytht37, milytht937
B6 cell : tamyhg4647adm
B7 cell : moreduj4664

Question 2: How do I add a column that is coming from a different CSV file based on the common key (name column in both) but only 1 file has the memory column and i want to add it to the fist file. Thanks



. D:\Data\BF_Scripts\Write-Log.ps1


    $filePath = "D:\Data\Input\bigFixDataNew.csv"

    #$filePath = "D:\Data\Input\BF_Test_Dec2019.csv"

    $System = "D:\Data\Output\System.csv"


    $desiredColumnsSystem = @{ expression = {$_.'Internal Computer ID'}; label = 'RESOURCEID' },
    @{ expression = {$_.'Computer Name'}; label = 'NAME'},
    @{ expression = {$_.'DNS Name'}; label = 'DOMAIN'},
    @{ expression = {$_.'DNS Name'}; label = 'USER_DOMAIN'},
    @{ expression = {$_.'User Name'}; label = 'USER_NAME'}

    $Regex = '(,.*$)|(, sscope)|(sscope)'

    Try {
        Write-Log -Message 'Starting Creation of System CSV...'

        Import-Csv $filePath | Select-Object $desiredColumnsSystem | Sort RESOURCEID -Unique |
        Export-Csv -Path $System –NoTypeInformation



        $content = Get-Content $System 

        $content |  ForEach-Object {$_ -replace $Regex,''}  | Set-Content $System 

        $size = ((Get-Item $system).length/1KB)
        $lastTouchedDate = (Get-Item $system).LastWriteTime



        Write-Log -Message  "Created the System CSV Successfully !! The size of $system is $size KB and the last write time was $lastTouchedDate"
    }

    catch
    {
        Write-Log -Message $_.Exception.Message
    }
Theo
  • 57,719
  • 8
  • 24
  • 41
Roi Samagoue
  • 45
  • 7
  • 1
    I think it would help if you can show us the first 3 or 4 lines of the actual input file `D:\Data\Input\bigFixDataNew.csv`. Of course, you need to sanitize that so we don't see real usernames or other secrets. At the moment, I don't think I'm looking at something like a CSV file at all.. – Theo Jan 04 '20 at 20:28
  • 1
    Instead of `Get-Content ... | modify line | Set-Content`, on a the (`$System`) csv file, you better do: `Import-Csv ... | modify property | Export Csv ...`. For the other question (next time you should do separate questions), see: [In Powershell, what's the best way to join two tables into one?](https://stackoverflow.com/a/1848871/1701026) – iRon Jan 04 '20 at 20:49
  • Hello @Theo, This csv file produces multiple csvs based on the desired Columns, the System Csv contains the user name that needs to be cleaned up – Roi Samagoue Jan 06 '20 at 16:31
  • apologies @Theo , I am trying to attach the csv file, however, the following change produce almost what i need, except it is replacing everyting with empty string rather than replacing the substring 
     $Regex = '()|(, sscope)|(sscope)|(,.*$)'

 Try {
  Write-Log -Message 'Starting Creation of System CSV...'

  Import-Csv $filePath | Select-Object $desiredColumnsSystem | Sort RESOURCEID -Unique | ForEach-Object {
    If ($_.USER_NAME -match $Regex) {
        $_.USER_NAME = ''
    }
    $_
} |
  Export-Csv -Path $System –NoTypeInformation
    – Roi Samagoue Jan 06 '20 at 17:34

1 Answers1

0

This seems to have done it, Again thanks to you all

Import-Csv $filePath | Select-Object $desiredColumnsSystem | Sort RESOURCEID -Unique | ForEach-Object {
            If ($_.USER_NAME -match $Regex) {
                $_.USER_NAME = $_.USER_NAME -replace $Regex,''
            }
            $_
        } |
        Export-Csv -Path $System –NoTypeInformation
Roi Samagoue
  • 45
  • 7