Change date format in multiple CSV files

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
Hal242
Posts: 10
Joined: Fri Jan 18, 2019 1:53 am

Change date format in multiple CSV files

Post by Hal242 » Fri Feb 15, 2019 4:46 pm

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...

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Change date format in multiple CSV files

Post by jvierra » Fri Feb 15, 2019 4:54 pm

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>[

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Change date format in multiple CSV files

Post by jvierra » Fri Feb 15, 2019 5:09 pm

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

Hal242
Posts: 10
Joined: Fri Jan 18, 2019 1:53 am

Re: Change date format in multiple CSV files

Post by Hal242 » Fri Feb 15, 2019 7:09 pm

Yes, it can do this... How do I go about getting PowerShell to convert all of my files into a single date format?

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Change date format in multiple CSV files

Post by jvierra » Fri Feb 15, 2019 7:27 pm

Just import the CSV and loop through it chaging each field to whatever format you want.

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

Hal242
Posts: 10
Joined: Fri Jan 18, 2019 1:53 am

Re: Change date format in multiple CSV files

Post by Hal242 » Mon Feb 18, 2019 6:30 pm

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?

User avatar
jvierra
Posts: 13507
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: Change date format in multiple CSV files

Post by jvierra » Mon Feb 18, 2019 7:01 pm

It is a result of Excel settings and has nothing to do with PowerShell or scripting or a CSV.

Locked