1

Good Morning/Afternoon/Evening

I’m having an issue where my script is almost working too well.

I need a column of dates in .csv files to be in format yyyyMMdd. This works fine, except for when that data is already in yyyyMMdd format, at which point it turns blank.

I’m not sure how to have my script check if the date is in format already, and skip if it is. What I have right now is as follows:

If($Properties -contains 'DOB'){
$DOB = Import-Csv $File
$DOB | ForEach-Object{$_.DOB = Get-Date -date ($_.'DOB') -f 'yyyyMMdd'}
$DOB | ConvertTo-Csv -NoTypeInformation | ForEach-Object{$_ -replace '["()$]', ''} | Out-File -FilePath $File -Encoding ascii -Force
}

$Properties is an array of the column headers for the $file.

3 Spice ups
2

how about

$DOB | ForEach-Object{$_.DOB = Get-Date -date ($_.'DOB').ToString("yyyyMMdd")}
1 Spice up
3

That gave me this error:

Cannot find an overload for "ToString" and the argument count: "1".
At .ps1:355 char:23
+ ... rEach-Object{$_.DOB = Get-Date -date ($_.'DOB').ToString("yyyyMMdd")}

To clarify: The code I posted in the original post did work, except for when the date is already in the correct format already.
4

Looks you will only need to convert to yyyyMMdd whose date format is not valid. check with this .

$d = Import-Csv C:\File.csv| ForEach-Object{$_.DOB }
If($d -match '\d{4}\d{2}\d{2}'){
Write-Host "date is valid $d "
}
else{
Write-Host "Invalid $d"
}

Tagging Gungnir here for more

@gungnir

5

You could add an If statement to change the format only if the date is not in the correct format. EDIT: updated the If condition.

If($Properties -contains 'DOB'){
$DOB = Import-Csv $File
$DOB | ForEach-Object
{
    If ( $_.DOB -ne (Get-Date -Date ($_.'DOB') -Format yyyMMdd) )
    {
        $_.DOB = Get-Date -date ($_.'DOB') -f 'yyyyMMdd'
    }
}
$DOB | ConvertTo-Csv -NoTypeInformation | ForEach-Object{$_ -replace '["()$]', ''} | Out-File -FilePath $File -Encoding ascii -Force
}
6

yyyyMMdd isn’t recognized as a valid datetime string, likely due to conflicts with formats such as ddMMyyyy and MMddyyyy.

Do other formats like these exist in your CSV file? If not then you can use Jiten’s method to skip over converting these date strings.

1 Spice up
7

As far as the other data formats, not really… this code has worked for everything that’s come across so far, aside from if it was already in yyyyMMdd format which is what we need for our system.

In light of that, I’ve found somewhat of a solution:

$DOB | ForEach-Object{
    try{$_.DOB = Get-Date -date ($_.DOB) -Format 'yyyyMMdd'}
    catch{}
}

I know this may not work entirely, but it’s at least working with what I’ve come across so far.

1 Spice up