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?