In my existing CSV file I have a column called "SharePoint ID" and it look like this

1.ylkbq
2.KlMNO
3.
4.MSTeam
6.
7.MSTEAM
8.LMNO83

and I'm just wondering how can I create a new Column in my CSV call "SharePoint Email" and then add "@gmail.com" to only the actual Id like "ylkbq", "KLMNO" and "LMNO83" instead of applying to all even in the blank space. And Maybe not add/transfer "MSTEAM" to the new Column since it's not an Id.

$file = "C:\AuditLogSearch\New folder\OriginalFile.csv"
$file2 = "C:\AuditLogSearch\New folder\newFile23.csv"

$add = "@GMAIL.COM"

$properties = @{
    Name       = 'Sharepoint Email'
    Expression = {
        switch -Regex ($_.'SharePoint ID') {
    
          #Not sure what to do here
        }
    }
}, '*' 
Import-Csv -Path $file | 
Select-Object $properties |
Export-Csv $file2 -NoTypeInformation

CodePudding user response：

Using calculated properties with Select-Object this is how it could look:

$add = "@GMAIL.COM"

$expression = {
    switch($_.'SharePoint ID')
    {
        {[string]::IsNullOrWhiteSpace($_) -or $_ -match 'MSTeam'}
        {
            # Null value or mathces MSTeam, leave this Null
            break
        }
        Default # We can assume these are IDs, append $add
        {
            $_.Trim()   $add
        }
    }
}

Import-Csv $file | Select-Object *, @{
    Name = 'SharePoint Email'
    Expression = $expression
} | Export-Csv $file2 -NoTypeInformation

Sample Output

Index SharePoint ID SharePoint Email
----- ------------- ----------------
1     ylkbq         [email protected]
2     KlMNO         [email protected]
3                   
4     MSTeam        
5                   
6     MSTEAM        
7     LMNO83        [email protected]

A more concise expression, since I misread the point, it can be reduced to just one if statement:

$expression = {
    if(-not [string]::IsNullOrWhiteSpace($_.'SharePoint ID') -and $_ -notmatch 'MSTeam')
    {
        $_.'SharePoint ID'.Trim()   $add
    }
}