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...
Change date format in multiple CSV files
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.
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.
Re: Change date format in multiple CSV files
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
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
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
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
Attempt, which seems to affect the Date_Installed, but not exactly how I had hoped.
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?
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
Code: Select all
import-csv "C:\myCSV.csv" | % {$_.Date_Installed = ([datetime]($_.Date_Installed)).ToString('MMMM.d.yy'); $_} | Export-Csv 'C:\outFile.csv' -NoTypeInformation
Is there a better way to get these dates converted?