Powershell - select data from single CSV cell

Question

I need to take the 2nd value from a cell along an entire column which is a Samaccountname, each cell has many values separated by ";", the column has no title or header.

then I need to export to a new csv, those samaccountname values plus the property "Account expires" of each one in the next column.

can you help me?

CSV FORMAT

AAA;BBB;CCC;DDD

AAA2;BBB2;CCC2;DDD2

and so on... it´s just the first column I need to extract info from, about other columns I don´t care.

Theo · Accepted Answer · 2021-06-05 14:50:02Z

1

Ok, apparently your CSV looks like this:

AccountExpires;SamAccountname;Displayname;Company;Manager
date1;user1;name1;Co1;Boss1
date2;user2;name2;Co2;Boss2

Then:

# just take the SamAccountname column values and ignore the rest
(Import-Csv -Path 'D:\Test\Original.csv' -Delimiter ';').SamAccountname | ForEach-Object {
    # calculate the account expiration date
    $user = Get-ADUser -Filter "SamAccountName -eq '$_'" -Properties AccountExpirationDate, accountExpires -ErrorAction SilentlyContinue
    if ($user) {
        $expires = if ($user.accountExpires -gt 0 -and $user.accountExpires -ne 9223372036854775807) { $user.AccountExpirationDate }
                   else { 'Never Expires' }
        # output an object
        [PsCustomObject]@{
            SamAccountName = $_
            AccountExpires = $expires
        }
    }
    else {
        Write-Warning "User '$_' does not exist"
    }
} | Export-Csv -Path 'D:\Test\ExpiringAccounts.csv' -NoTypeInformation

Output ExpiringAccounts.csv will be something like this when opened in Notepad:

"SamAccountName","AccountExpires"
"user1","Never Expires"
"user2","6/5/2021 4:36 PM"

edited Jun 5, 2021 at 14:50

answered Jun 5, 2021 at 14:07

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

4 Comments

Salvador Over a year ago

Not really, all those values i just put there (AAA;BBB;CCC;DDD) are in a single cell, the cell below contains same type of information but different values. Of all the cells in the same column i must take the 2nd value, each value is separated by a ";". After i take that value i must script to get the Account expires property of each of those values and write them to a new CSV (just to not overwrite the reading file) with all the samaccount name values in a column, and all the accountexpires values in the next column. I hope i made myself clear this time.

Salvador Over a year ago

Sorry guys, i'm not used to CSV files, i'm talking about cells cause the file is opened in excel, but opening in notepad makes it clear that there is no cell It's listed as below AccountExpires; SamAccountname; Displayname; Company; Manager ------------------------------------------------------------------------------------------ date1;user1;name1;Co1;Boss1 ------------------------------------------------------------------------------------------- date2;user2;name2;Co2;Boss2 ... BTW: The first column (AccountExpires) has outdated values so i cannot take them to make the new output.

Theo Over a year ago

@Salvador All this you should have been mentioned in the question itself, not in comments because they are hard to read. Anyway, I have now changed my answer for you

Salvador Over a year ago

How can this be modified to save the samaccountname and accountexpirationdate as titles for the new file? so it can be used for the next execution, current code search for those values to run.

Collectives™ on Stack Overflow

Powershell - select data from single CSV cell

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related