I have a CSV file with the following data
"Date","Time","Name","SourceIP","DestinationIP"
"Sep 1","03:55:57","mmt-5","172.16.48.158","22.22.22.22"
"Sep 1","03:55:57","mmt-5","172.16.48.158","22.22.22.22"
"Sep 1","03:55:57","mmt-5","172.16.48.158","22.22.22.22"
"Sep 1","03:55:57","mmt-5","172.16.48.158","22.22.22.22"
I would like to convert the date into something more usable like 09-01-2016 (the year would be the current year). How can I accomplish this?
That's easy using the [DateTime] class's ParseExact method. You supply the string that is a date, you tell it how the date is formatted, and you provide a culture or provider or something, but I always just use $null.
[datetime]::ParseExact("Sep 1","MMM d",$null)
That comes out to:
Thursday, September 1, 2016 12:00:00 AM
So you could just do something like:
$Array | ForEach{[datetime]::ParseExact($_.Date,"MMM d",$null)}
And that would convert each entry's Date property to a valid [datetime] object. Then you just format it however you want:
$Array | ForEach{[datetime]::ParseExact($_.Date,"MMM d",$null).ToString("M-d-yyyy")}
That would output:
9-1-2016
Or for the exact thing you asked for use "MM-dd-yyyy" to get 09-01-2016.
Edit: Thanks to wOxxOm for educating me about the third parameter's necessity when dealing with non-localized date formats! So, if this needs to be localized for other cultures, you will need to include that last parameter. That can be done as such:
$Culture = [cultureinfo]::GetCultureInfoByIetfLanguageTag('en-US')
$Array | ForEach{[datetime]::ParseExact($_.Date,"MMM d",$Culture).ToString("MM-dd-yyyy")}
Edit2: Ok, to replace your current Date field with this information you could pass the array to the Select command, and create a new Date property on the fly, and discard the original, then pass that to Export-CSV:
$Array | Select *,@{l='Date';e={[datetime]::ParseExact($_.Date,"MMM d",$null).ToString("M-d-yyyy")}} -Exclude Date | Export-CSV D-Sample-2.csv -NoType
$null on OS with a different system locale from the date being parsed e.g. [cultureinfo]::GetCultureInfoByIetfLanguageTag('en-US')
$Array = import-csv D-sample-1.csv $Array | ForEach{[datetime]::ParseExact($_.Date,"MMM d",$null).ToString("M-d-yyyy")} $Array | export-csv D-sample-2.csv -notype
import-csv D-sample-1.csv $Array | ForEach{[datetime]::ParseExact($_.Date,"MMM d",$null).ToString("M-d-yyyy")} export-csv D-sample-2.csv -notype