CODEWITHSUNDEEP
powershellcsv
Sprengdilly
Sprengdilly

Reputation: 65

Formula csv powershell? is it possible?

I have a formula that I would like to put in the "Unique Account Name" column. I know that a CSV file is data only, I was wondering what the best way is to add a formula to the Unique Account name tab and to auto populate the rest of the column (if possible). Not sure if this is even possible or not and I wanted some clarification.

enter image description here

I am trying to implement this under the unique account Name. IF(G2="Domain",E2&" - "&C2,A2 & " - "&C2)

My code:

 Import-Csv 'U:\Local Group Members.csv' | where-Object {($_.Name0 -eq "administrators") -and ($_.caption0 -match "Microsoft Windows 10 Enterprise|Microsoft Windows 7 Enterprise|Microsoft Windows 7 Professional|Microsoft Windows 8 Enterprise|Microsoft Windows 8 Pro|Microsoft Windows 8.1 Enterprise|Microsoft Windows 8.1 Pro")} |
Select-Object "Netbios_name0", "Name0", "Account0","category0","Domain0","Unique Account Name","caption0", "Excluded" |
Export-Csv -notypeinformation U:\LGMbestone.csv

My question is, is it possible to add the formula to this file? Or would I have to export it as a different file type and make a new script?

I am new to PowerShell.

End result I would like for that column to be like

Upvotes: 2

Views: 768

Answers (1)

Mark Wragg
Mark Wragg

Reputation: 23355

Assuming your CSV file already has a blank 'Unique Account Name' column and replicating the Excel formula that you shared in the comments, something like this might be what you're looking for:

Import-CSV U:\Local Group Members.csv | ForEach-Object {

    If ($_.Caption0 -eq 'Domain') { 
        $_.UniqueAccountName = "$($_.Domain0) - $($_.Account0)" 
    } Else { 
        $_.UniqueAccountName = "$($_.Nebios_name0) - $($_.Account0)" 
    }

    Write-Output $_

} | Export-Csv -notypeinformation U:\LGMbestone.csv

If your input CSV file doesn't already have that column to start with, you could do something like this (to create it in the object):

Import-CSV U:\Local Group Members.csv | ForEach-Object {

    If ($_.Caption0 -eq 'Domain') { 
        $UniqueAccountName = "$($_.Domain0) - $($_.Account0)" 
    } Else { 
        $UniqueAccountName = "$($_.Nebios_name0) - $($_.Account0)" 
    }

    $_ | Add-Member -MemberType NoteProperty -Name 'UniqueAccountName' -Value $UniqueAccountName -PassThru

} | Export-Csv -notypeinformation U:\LGMbestone.csv

It's hard to say if these exactly match your requirements without having some sample data from both CSV files and a clearer explanation of what you expect the end result to look like.

Upvotes: 4

Related Questions