Convert UK to US date format in CSV

Tagged: 

This topic contains 5 replies, has 4 voices, and was last updated by Profile photo of Will Anderson Will Anderson 1 year, 11 months ago.

  • Author
    Posts
  • #21378
    Profile photo of Nick B
    Nick B
    Participant

    Hi,

    I have a CSV file with 10 columns (the last column is the date)
    Currently the date is DD/MM/YYYY and I need to change this to the US format which is MM/DD/YYYY]

    What's the best way to do this? Should I slice the day month and year into a variable and then reorder it or is there an easier way?

    Thanks!
    Nick

  • #21382
    Profile photo of Will Anderson
    Will Anderson
    Keymaster
    Get-Date -Format dd/mm/yyyy

    And just because that answer seems incredibly short – there's actually an article linked from the Get-Date help on TechNet that gives you a full list of format types you can use. Enjoy!

    http://msdn.microsoft.com/en-us/library/system.globalization.datetimeformatinfo(VS.85).aspx

  • #21383
    Profile photo of Nick B
    Nick B
    Participant

    Thanks Will,
    Sorry I should have probably been clearer – the dates in the csv file are different dates on each line and none of them are today's date so I need to get the date already in the file and then convert, not just put today's date 🙂

  • #21384
    Profile photo of Richard Siddaway
    Richard Siddaway
    Moderator

    Unfortunately there isn't an easy way to do this. Your CSV file holds the date as a string. You need to convert that to a date. If you try this
    £> $sduk = '25/12/2014'
    £> $d = [datetime]$sduk
    Cannot convert value "25/12/2014" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    At line:1 char:1
    + $d = [datetime]$sduk
    + ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : InvalidArgument: (:) [], RuntimeException
    + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider

    where the date is 25 December 2014 in UK format it will fail.

    .NET expects the string in US format

    £> $sdus = '12/25/2014'
    £> $d = [datetime]$sdus
    £> $d

    25 December 2014 00:00:00

    You could do something like this

    £> $sd = '25/12/2014' -split '/'
    £> $sd
    12
    25
    2014
    £> $d = Get-Date -Day $sd[0] -Month $sd[0] -Year $sd[2]
    £> $d

    25 December 2014 18:25:56

  • #21386
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Casting a string to a datetime assumes invariant (basically US) format, but calling [datetime]::Parse() allows you to specify a culture (as does the ToString() method on datetime objects). For example:

    $ukString = '25/12/2014'
    
    $ukCulture = [Globalization.CultureInfo]'en-GB'
    $usCulture = [Globalization.CultureInfo]'en-US'
    
    $datetime = [datetime]::Parse($ukString, $ukCulture)
    
    $usString = $datetime.ToString('d', $usCulture)
    
  • #21394
    Profile photo of Will Anderson
    Will Anderson
    Keymaster

    Whoops! I totally misread your original post. My bad Nick. 🙂

You must be logged in to reply to this topic.