Change date format in multiple CSV files

Hello, I have over 2000 CSV files with a mix of date formats. I have been reading over the past 2 weeks many online resources about how to update these files, but I am having limited success. Hopefully someone can help point me in the right direction… I don’t want to manually update every CSV due to the volume and I get about 80 new files per month, so would like to write a script I can use for this.

The dates are always in the same column, but sometimes are custom format with d-mmmm-yyyy, sometimes custom format with m/d/yyyy h:mm, and still others are Date format *m/dd/yyyy. I need to make all of the CSV files consistent.

I am not an expert, but given some tips and time, I can usually make something work. Any useful advice is appreciated…

Here is some example data I am using while trying to get the core command to work.

Company_Name,Date_Installed,Product_Installed,Device_Type,Device_SN,OEM_Brand,OEM_Model,Processor_type
Company1,4/9/2016,Windows 7 Pro,Laptop,abc123,Lenovo,T440,Intel Core i5-4300M 2.60GHz
Company2,4/9/2016,Windows 7 Pro,Laptop,def456,Lenovo,T440,Intel Core i5-4300M 2.60GHz
Company3,4/9/2016,Windows 7 Pro,Laptop,ghi789,Lenovo,T440,Intel Core i5-4300M 2.60GHz

Here is what I have pieced together, which seems to affect the Date_Installed, but not exactly how I had hoped.

import-csv "C:\myCSV.csv" | % {$_.Date_Installed = ([datetime]($_.Date_Installed)).ToString('MMMM.d.yy'); $_} | Export-Csv 'C:\outFile.csv' -NoTypeInformation

I still need to change it so that it can loop through the folder and affect all of the CSV files it finds, but I can’t determine how to do that.

I imagine I can loop through the files using something like this;

Get-ChildItem C:\Normalized -name -include *.csv | ForEach-Object { #insert variation of the import-csv and export-csv command }

The trouble here is that I currently have hard-coded the file names and paths and am unsure how to export to a filename which is similar to the original file name, but perhaps appended with a value to keep it as a 2nd file instead of overwriting the original.

Any suggestions?

Harold,
welcome to the Powershell.org forum.
Ofcourse you can automate the process of scanning a particular folder for CSV files and process them accordingly. But unfortunately there is no simple way to deal with inconsistent date time formats. For example “4/9/2019” could be the 4th of September 2019 or the 9th of April 2019. So either you create a sophisticated advanced function who is able to determine the existing date time format or you will have to keep a manual step in this process. If you are sure about the general date file format (I mean the order of day month year) you could try to use regular expressions to bring it to a homogenous format to make it easier to process this date further on.

Thanks for the warm welcome and the info… so, it seems that I may not be able to count on the date format being reconfigured accurately, according to your comments. In this case, I can see why it might be more effective to perform some manual steps. I have over 2000 CSV files which I need to process… I suppose I could have completed this by now, given I started this over 2 weeks ago. Not a fun project, but a reliable date is more important and I don’t have more weeks to waste trying to figure out a sophisticated advanced function.

There is always more than one way to do things and I am not opposed to taking a different approach. I do happen to have all of the original files in folders named after the month, within folders named after the year. Is it a simple process to replace the date value with a date value created by the location where the file is saved? for example, if the CSV is saved to .\2019\Jan how can I replace the date value under Date_Installed with a date value for January 2019?

Please, don’t get me wrong. Technically it is possible to create an automated process. But I think it will not be easy in the beginning. The complexity increases with the amount of different date/time formats you have to deal with.

To answer your new question: If the name of the header is always “Date_Installed” it should be pretty forward to replace the values with something you create with a transformation of the path of the folder.

All this depends on your Powershell knowledge. :wink: Just as a small example - to analyze and cut the folder path into pieces you could use the -split operator.

‘C:\root\subdirectory\2019\jan’,‘C:\root\subdirectory\2019\feb’,‘C:\root\subdirectory\2019\mar’ |
ForEach-Object {
[PSCustomObject]@{
Year = $($_ -split ‘\’)[3]
Month = $($_ -split ‘\’)[4]
}
}

What would actually be the target date time format you like to have as the result of all this?

… and just to show that it is possible …

$Source = @(
‘01/01/2019’,
‘15-01-2019’,
‘23-12-2018’,
‘4/4/2018’,
‘6/mar/2017’,
‘15/jun/2016’,
‘9-04-2013’,
‘09_4_2012’,
‘09_okt_2012’
)
foreach ($Date in $Source) {
$Date -match ‘^(\d+)/_-/_-$’ | Out-Null
[PSCustomOBject]@{
Day = [INT]$($Matches[1])
Month = [INT]$($Matches[2] -replace ‘mar’,‘3’ -replace ‘jun’,‘6’ -replace ‘okt’,‘10’)
Year = [INT]$($Matches[3])
Date = Get-Date -Month $([INT]$($Matches[2] -replace ‘mar’,‘3’ -replace ‘jun’,‘6’ -replace ‘okt’,‘10’)) -Day $([INT]$($Matches[1])) -Year $([INT]$($Matches[3]))
}
}

Have a lot of fun! :wink: :smiley:

#region Input

$CSVFolderPath = 'D:\Sandbox\output\myCSVFolder'
$FieldName = 'Date_Installed' 

#endregion


#region Normalize 'Date_Installed'

$DoneFileList = $MissingFieldFileList = $BadDateFileList = @()

foreach ($CSVFile in (Get-ChildItem $CSVFolderPath -Filter *.csv).FullName) {
    "Processing file $CSVFile"
    $RecordList = Import-Csv $CSVFile

    if ($FieldName -in ($RecordList | Get-Member -MemberType NoteProperty).Name) { # Validate 'Date_Installed' field exists in CSV file
        foreach ($Record in $RecordList) {
            if ($Record.$FieldName -as [datetime]) {
                $Record.$FieldName = ([datetime]$Record.$FieldName).ToString('MMMM.d.yy')
            } else {
                Write-Warning "    Error: Bad data '$($Record.$FieldName)' found in '$FieldName' field in file '$CSVFile', unable to normalize this record.."
                $BadDateFileList += $CSVFile
            }            
        }

        if ($CSVFile -notin $BadDateFileList) { $DoneFileList += $CSVFile }

        $DesintationFile = $CSVFile.Replace('.csv','-Normalized.csv')
        # or in a different folder to prevent script from running against already normalized CSV's:
        # $DesintationFile = "c:\mynewfolder\$((Split-Path $CSVFile -Leaf).Replace('.csv','-Normalized.csv'))"
        $RecordList | Export-Csv $DesintationFile -NoType

    } else {
        Write-Warning "Error: '$FieldName' field does not exist in file '$CSVFile', skipping.."
        $MissingFieldFileList += $CSVFile
    }
}

$BadDateFileList = $BadDateFileList | select -Unique

#endregion


#region Final reporting

if ($DoneFileList) {
    "List of normalized files:"
    $DoneFileList 
    "#######################################"
}

if ($MissingFieldFileList) {
    "List of files missing '$FieldName' field:"
    $MissingFieldFileList 
    "#######################################"
}

if ($BadDateFileList) {
    "List of files with bad dates:"
    $BadDateFileList 
    "#######################################"
}

#endregion

@Olaf, that RegEx witchcraft again :slight_smile:

… wait! … what? … do I get this right - did you just call me a witch? :wink: :smiley: What’s wrong with regex? If you cannot count on the reliability of the other people to use the right format you have to try to catch them in some clever way to reduce the work you have to do. I tend to be lazy actually. :stuck_out_tongue:

Ok, I knew I came to the right place at PowerShell.org, but this is more help than expected :slight_smile:

Thank you both, very much for the time you have given to this issue. I will work on it today, and hopefully can make it work.