Page 1 of 1

Change date format in multiple CSV files

Posted: Fri Feb 15, 2019 4:46 pm
by Hal242
Hello, I have over 2000 CSV files with a mix of date formats. I have been reading over the past week many online resources about how to update these files, but I am not having much success. Hopefully someone can help point me in the right direction... PowerShell is my first thought when it comes to solving this, but I am open to other options. I just 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 column D, 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.

Of course, I will need to make a loop to iterate through all of the files, but I am not sure where to begin to update all of these files. I am not an expert, but given some tips and time, I can usually make something work.

any useful advice is appreciated...

Re: Change date format in multiple CSV files

Posted: Fri Feb 15, 2019 4:54 pm
by jvierra
This is not an issue. PowerShell can convert hundreds of formats.

Code: Select all

PS D:\scripts> [datetime]'3-feb-1999'
Wednesday, February 3, 1999 12:00:00 AM

PS D:\scripts> [datetime]'2/13/1999 3:12'
Saturday, February 13, 1999 3:12:00 AM


PS D:\scripts>  [datetime]'12/13/1999'
Monday, December 13, 1999 12:00:00 AM

PS D:\scripts> [datetime]'3-March-1999'
Wednesday, March 3, 1999 12:00:00 AM
PS D:\scripts>[

Re: Change date format in multiple CSV files

Posted: Fri Feb 15, 2019 5:09 pm
by jvierra
Also note that you can create custom temlates:

Code: Select all

PS D:\scripts> $template = 'M-d-yyyy'
PS D:\scripts> $timeinfo = '1-12-2012'
PS D:\scripts> [DateTime]::ParseExact($timeinfo, $template, $null)
Thursday, January 12, 2012 12:00:00 AM

Re: Change date format in multiple CSV files

Posted: Fri Feb 15, 2019 7:09 pm
by Hal242
Yes, it can do this... How do I go about getting PowerShell to convert all of my files into a single date format?

Re: Change date format in multiple CSV files

Posted: Fri Feb 15, 2019 7:27 pm
by jvierra
Just import the CSV and loop through it chaging each field to whatever format you want.

$csv.Field = ([datetime]$csv.Field).ToString(<you format>)

Re: Change date format in multiple CSV files

Posted: Mon Feb 18, 2019 6:30 pm
by Hal242
I am still having trouble figuring this out... I am not an expert and am sincerely interested to understand it.

Here is what I have pieced together from my understanding and what I can find on the internet;

Example CSV data I am working with in myCSV.csv

Code: Select all

CompanyName,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
Attempt, which seems to affect the Date_Installed, but not exactly how I had hoped.

Code: Select all

import-csv "C:\myCSV.csv" | % {$_.Date_Installed = ([datetime]($_.Date_Installed)).ToString('MMMM.d.yy'); $_} | Export-Csv 'C:\outFile.csv' -NoTypeInformation
The above changes the Date_Installed data to a date like this April.9.16 but when I try to change the date format to something else, like MMM dd, yyyy I get interesting results; If I look at the outFile.csv in Notepad, it shows as expected, like this Apr 09, 2016 but looking at the cell in Excel, it shows like this 9-Apr-16 and if I select the cell in Excel, the formula bar shows it as 4/9/2016 - is this all a result of the Culture settings from the person who made the original file, or something else?

Is there a better way to get these dates converted?

Re: Change date format in multiple CSV files

Posted: Mon Feb 18, 2019 7:01 pm
by jvierra
It is a result of Excel settings and has nothing to do with PowerShell or scripting or a CSV.