none
import date from csv - format error RRS feed

  • Question

  • Hi All,

    (PowerShell question)

    I have a csv file which has dates stored in the format dd/MM/yyyy hh:mm

    I want to call them into my script so I use

    $a = @(import-csv "C:\test.csv")
    [datetime]$date = $a[0].date

    this works fine until I hit say the 13/03/2015 10:00 (13th Match 2015). It throws the error that it cannot be converted into datetime. It looks like its a formatting issue. Going forward I can engineer the solution to record the date differently (MM/dd/yyyy hh:mm) but i'd like to know if there is a way to get round this.

    Thanks

    Joe

    Monday, March 16, 2015 2:23 PM

Answers

  • Full example based on your CSV data:


    function Get-DateTime {
      param(
        [String] $dateTimeString
      )
      $dateTime = new-object DateTime
      if ( [DateTime]::TryParseExact($dateTimeString,
        'dd/MM/yyyy HH:mm',
        [Globalization.CultureInfo]::InvariantCulture,
        [Globalization.DateTimeStyles]::None,
        [Ref] $dateTime) ) {
        $dateTime
      }
    }
    
    import-csv "test.csv" | foreach-object {
      Get-DateTime $_.Date
    }
    


    -- Bill Stewart [Bill_Stewart]


    Friday, April 10, 2015 3:10 PM
    Moderator

All replies

  • Post the first few lines of your CSV file.

    -- Bill Stewart [Bill_Stewart]

    Monday, March 16, 2015 2:39 PM
    Moderator
  • Date
    13/03/2015 15:48
    14/03/2015 15:48
    15/03/2015 15:48
    16/03/2015 15:48
    17/03/2015 15:48
    18/03/2015 15:48
    19/03/2015 15:48
    20/03/2015 15:48
    21/03/2015 15:48
    22/03/2015 15:48
    23/03/2015 15:48
    24/03/2015 15:48
    25/03/2015 15:48
    26/03/2015 15:48
    27/03/2015 15:48
    28/03/2015 15:48
    29/03/2015 15:48
    30/03/2015 15:48
    31/03/2015 15:48
    01/04/2015 15:48
    02/04/2015 15:48
    03/04/2015 15:48
    04/04/2015 15:48
    05/04/2015 15:48
    06/04/2015 15:48
    07/04/2015 15:48
    08/04/2015 15:48
    09/04/2015 15:48
    10/04/2015 15:48
    11/04/2015 15:48
    12/04/2015 15:48
    13/04/2015 15:48
    14/04/2015 15:48
    15/04/2015 15:48
    16/04/2015 15:48
    17/04/2015 15:48
    18/04/2015 15:48
    19/04/2015 15:48
    20/04/2015 15:48
    21/04/2015 15:48
    22/04/2015 15:48
    23/04/2015 15:48
    24/04/2015 15:48
    25/04/2015 15:48
    Monday, March 16, 2015 2:44 PM
  • That is not CSV. The first line has the word "Date", and then you have date, newline, time, newline, date, newline, time, newline etc.

    Is that really the format of your file or did your paste not work correctly?


    -- Bill Stewart [Bill_Stewart]

    Monday, March 16, 2015 2:48 PM
    Moderator
  • might be a paste error....

    Date is the first cell, then a date/time for the rest of the cells...

    so..

    Date
    13/03/2015  15:48:00
    14/03/2015  15:48:00
    15/03/2015  15:48:00
    16/03/2015  15:48:00
    17/03/2015  15:48:00
    etc..

    so my import code, [x] is the cell number and .date refers to the date column. (not trying to teach you to suck eggs, I know you'd have worked that out :) )

    $a = @(import-csv "C:\test.csv")
    [datetime]$date = $a[0].date

    if I have a cell reading 12/03/2015 when I run my script $date PS reads this as 3rd December 2015 when it is 12th March


    • Edited by JOEs_SG Monday, March 16, 2015 2:59 PM typo
    Monday, March 16, 2015 2:58 PM
  • This might give you a better idea of what i'm doing, a bit more of my code...

    $Results = (Get-Content 'C:\test.csv' | Measure-Object) $Lines = ($Results.Count -1) $lines $n = 0 While ($lines -gt "0"){ $a = @(Import-Csv 'C:\test.csv') $Date = $a[$n].Date $lines-- $n++ }



    • Edited by JOEs_SG Monday, March 16, 2015 3:05 PM typo
    Monday, March 16, 2015 3:04 PM
  • Please paste the exact first three lines of your CSV file. Don't interpret.

    -- Bill Stewart [Bill_Stewart]

    Monday, March 16, 2015 3:17 PM
    Moderator
  • Date
    13/03/2015 15:48
    14/03/2015 15:48
    15/03/2015 15:48
    16/03/2015 15:48
    17/03/2015 15:48
    18/03/2015 15:48
    19/03/2015 15:48
    20/03/2015 15:48
    21/03/2015 15:48
    22/03/2015 15:48
    Monday, March 16, 2015 3:24 PM
  • Date
    13/03/2015 15:48
    14/03/2015 15:48
    15/03/2015 15:48
    16/03/2015 15:48
    17/03/2015 15:48
    18/03/2015 15:48
    19/03/2015 15:48
    20/03/2015 15:48
    21/03/2015 15:48
    22/03/2015 15:48
    I used Notpad++ instead of excel this time
    Monday, March 16, 2015 3:24 PM
  • So based on that date/time format, you can use the TryParseExact static method of the DateTime object to tell PowerShell precisely what to do. Here is a short example:


    $dateTimeString = "13/03/2015 15:48:00"
    $dt = new-object DateTime
    if ( [DateTime]::TryParseExact($dateTimeString,
         'dd/MM/yyyy HH:mm:ss',
         [Globalization.CultureInfo]::InvariantCulture,
         [Globalization.DateTimeStyles]::None,
         [Ref] $dt)
      ) {
      $dt
    }
    


    -- Bill Stewart [Bill_Stewart]

    Monday, March 16, 2015 3:38 PM
    Moderator
  • It is also easy if you just pic a culture that matches the source of the timestring.

    $cult=[Globalization.CultureInfo]::CreateSpecificCulture('fr-FR')
    $csv|%{[datetime]::Parse($_.Date,$cult)}


    ¯\_(ツ)_/¯

    Monday, March 16, 2015 6:36 PM
  • Full example based on your CSV data:


    function Get-DateTime {
      param(
        [String] $dateTimeString
      )
      $dateTime = new-object DateTime
      if ( [DateTime]::TryParseExact($dateTimeString,
        'dd/MM/yyyy HH:mm',
        [Globalization.CultureInfo]::InvariantCulture,
        [Globalization.DateTimeStyles]::None,
        [Ref] $dateTime) ) {
        $dateTime
      }
    }
    
    import-csv "test.csv" | foreach-object {
      Get-DateTime $_.Date
    }
    


    -- Bill Stewart [Bill_Stewart]


    Friday, April 10, 2015 3:10 PM
    Moderator
  • Full example based on your CSV data:


    function Get-DateTime {
      param(
        [String] $dateTimeString
      )
      $dateTime = new-object DateTime
      if ( [DateTime]::TryParseExact($dateTimeString,
        'dd/MM/yyyy HH:mm',
        [Globalization.CultureInfo]::InvariantCulture,
        [Globalization.DateTimeStyles]::None,
        [Ref] $dateTime) ) {
        $dateTime
      }
    }
    
    import-csv "test.csv" | foreach-object {
      Get-DateTime $_.Date
    }
    


    -- Bill Stewart [Bill_Stewart]


    Bill, Sorry it took so long to make as an answer, this one must have escaped me as the problem went away :)

    thanks for your help as always!

    Joe

    Thursday, April 16, 2015 2:30 PM