How can I add string and create new column in my csv file using PowerShell

Question

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

Santiago Squarzon · Accepted Answer · 2021-12-09 12:32:01Z

3

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
    }
}

edited Dec 9, 2021 at 12:32

answered Dec 9, 2021 at 4:23

Santiago Squarzon

65.5k5 gold badges26 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ziico Over a year ago

Hi @Santiago, thank you so much again for this but just wondering How can I NOT include "MSTeam" in the new column since it's not an actual Id and doesn't have email. Later I'll need to get AzureAD displayName using the new Column so even having "MSTeam" word in the new column might give me a problem/issue later.

Santiago Squarzon Over a year ago

@aaselab that's my bad, I misread, thought you wanted MSTeam there but without appending. See my edit.

ziico Over a year ago

sorry to bother you again but I was doing it the long process of what I wanted to do and only now I found out that there is an easy proccess so would you mind taking a look at my post once again stackoverflow.com/questions/70294010/… I would be really apprecited for your help.

Collectives™ on Stack Overflow

How can I add string and create new column in my csv file using PowerShell

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related