CODEWITHSUNDEEP
powershellcsv
Eric
Eric

Reputation: 941

Powershell Convert Date format in a CSV

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?

Upvotes: 1

Views: 2634

Answers (1)

TheMadTechnician
TheMadTechnician

Reputation: 36277

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

Upvotes: 2

Related Questions