Powershell import-csv to change date format

user3398681 0
Viewed 37

I'm trying to convert a date through Powershell import-csv and need some help please! I've simplified the content of the csv file to give an example which appears as:

"ServerName","InstallDate"
"SRV1","20220506"

Then I use Powershell command:

Import-Csv 'c:\results\data.csv' | select servername, installdate

The import date is currently in the form of year/month/date and I need it converting to month/date/year eg 05062022 based on sample data above.

I've experimented with [datetime]::ParseExtact but am getting nowhere with it, can anyone help with this? Thanks

time date powershell csv
1 Answers

Assuming all values in the InstallDate column are populated and follow the same format then this should work:

Import-Csv path\to\csv | ForEach-Object {
    $_.InstallDate = [datetime]::ParseExact($_.InstallDate, 'yyyyMMdd', [cultureinfo]::InvariantCulture).ToString('MMddyyyy')
    $_
} | Select-Object ServerName, InstallDate

Here is an example for testing using hardcoded CSV in a string:

@'
"ServerName","InstallDate"
"SRV1","20220506"
"SRV2","20220507"
'@ | ConvertFrom-Csv | ForEach-Object {
    $_.InstallDate = [datetime]::ParseExact($_.InstallDate, 'yyyyMMdd', [cultureinfo]::InvariantCulture).ToString('MMddyyyy')
    $_
}

In case there could be empty values in the column you can add a simple condition to check if String.IsNullOrWhiteSpace, and if it is, ignore that line:

Import-Csv path\to\csv | ForEach-Object {
    # if the value on `InstallDate` property is empty or white space
    if([string]::IsNullOrWhiteSpace($_.InstallDate)) {
        # return this object as-is without any modifications,
        # and go to the next item
        return $_
    }

    # here we assume the property is populated, so we can update it
    $_.InstallDate = [datetime]::ParseExact($_.InstallDate, 'yyyyMMdd', [cultureinfo]::InvariantCulture).ToString('MMddyyyy')
    $_
} | Select-Object ServerName, InstallDate
Santiago Squarzon0
Related