locked
CSV Files Error: "String was not recognized as a valid DateTime." RRS feed

  • Question

  • I have an odd one. I have written a script that will work through a bunch of csv files which are being generated from a Nimble Infosight lab, determine the hours to be between 07:00 and 17:55 and give me the max, min and average of the read, write iops and read, write throughputs.

    It has worked previously fine.

    For some reason, there are certain csv files where i am getting an error saying the below. Out of a folder with 22-23 csv files, maybe 2-5 do not work now for some reason I cannot work out:

    Cannot convert value "13/04/2020 03:31" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    At C:\Temp\IOPsWeekdays.ps1:16 char:5
    +     (([datetime]$_.ts).Hour -gt 7 -and ([datetime]$_.ts).Hour -lt 17) ...
    +     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : InvalidArgument: (:) [], RuntimeException
        + FullyQualifiedErrorId : InvalidCastParseTargetInvocationWithFormatProvider

    Here is the script I am using:

    #run the below cmd in an elevated powershell cmd window to allow unsigned scripts to execute
    Set-ExecutionPolicy unrestricted

    #set path to pick up ,csv`s
    $Path = "C:\Users\blahblah\\Servername\volumepool\volume\April"

    $Files = Get-ChildItem -Path $Path -Filter '*.csv' | Select-Object FullName

    Write-Output  "Processing $($Files.count) Files"

    foreach ($File in $Files)
    {
        Write-Output $File.FullName
        $csv = Import-Csv -Path  $Files.FullName | 
        Where-Object{
        (([datetime]$_.ts).Hour -gt 7 -and ([datetime]$_.ts).Hour -lt 17) -or 
        (([datetime]$_.ts).Hour -eq 17 -and ([datetime]$_.ts).Minute -lt 55)
                    }
        $csv.read_iops | measure -Maximum
        $csv.read_iops | measure -Minimum
        $csv.read_iops | measure -Average
        $csv.write_iops | measure -Maximum
        $csv.write_iops | measure -Minimum
        $csv.write_iops | measure -Average
        $csv.read_mbps | measure -Maximum
        $csv.read_mbps | measure -Average
        $csv.write_mbps | measure -Maximum
        $csv.write_mbps | measure -Average
    }

    I can try and add a couple of csv files if this lets me add attachments later.

    Any help would be appreciated.

    Thanks

    Wednesday, June 24, 2020 7:59 AM

All replies

  • Working csv:

    ts,read_iops,write_iops,read_mbps,write_mbps
    10/04/2020 00:01,0,0,0,0

    Not working csv:

    ts,read_iops,write_iops,read_mbps,write_mbps
    13/04/2020 00:01,0,0,0,0

    As you can see, they look the same to me.. or am i missing something?

    Wednesday, June 24, 2020 8:30 AM
  • Your system time format is not correct for the format in the file.

    You have to parse the date like this:

    [datetime]::ParseExact('13/04/2020 03:31','dd/MM/yyyy hh:mm',$null)


    \_(ツ)_/

    • Proposed as answer by Vector BCO Wednesday, June 24, 2020 6:00 PM
    Wednesday, June 24, 2020 8:34 AM
  • Thanks for the response.

    So why would this work for 90% of the same csv's?

    What exactly do I need to do, I am not clear.

    Wednesday, June 24, 2020 10:07 AM
  • Thanks for the response.

    So why would this work for 90% of the same csv's?

    What exactly do I need to do, I am not clear.

    date 12.01 will be parsed successfuly but in this case you will get 1 december istead of 12 january

    jrv shown you how to do this parsing operations in a propoer way

    in case if u will have in yours csv dates in this format yyyy.MM.dd your first method will word without any modifications


    The opinion expressed by me is not an official position of Microsoft

    Wednesday, June 24, 2020 10:41 AM
  • Thanks, I understand that I need to change the format of the string to be interpreted correctly, but where do I put that in the script I have written?

    I have tried inserting that line in to various parts, but keep getting the same error.

    Wednesday, June 24, 2020 1:48 PM
  • Like this:

     Where-Object{
            (([datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null).Hour -gt 7 -and [datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null).Hour -lt 17)) -or
            (([datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null).Hour -eq 17 -and [datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null).Minute -lt 55))
        }


    --- Rich Matheisen MCSE&I, Exchange Ex-MVP (16 years)

    • Proposed as answer by Vector BCO Wednesday, June 24, 2020 6:00 PM
    Wednesday, June 24, 2020 2:47 PM
  • Yeah I had tried that and I get the error:

    Exception calling "ParseExact" with "3" argument(s): "String was not recognized as a valid DateTime."
    At C:\Temp\IOPsWeekdays.ps1:16 char:9
    +         (([datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null).Hour ...
    +         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : FormatException

    Thursday, June 25, 2020 12:31 PM
  • could you check value of $_.ts for object that failed?

    looks like you have null values or dates in some other format that you show us in the first comment :)


    The opinion expressed by me is not an official position of Microsoft

    Thursday, June 25, 2020 12:44 PM
  • You have to report the format being errored.  It looks like you have multiple formats in different files.

    Where-Object{
        Try{
             $dt = [datetime]::ParseExact($_.ts,'dd/MM/yyyy hh:mm',$null)
             ($dt.Hour -gt 7 -and $dt.Hour -lt 17) -or ($dt.Hour -eq 17 -and $dt.Minute -lt 55)
        }
        Catch{
            Write-Host Error $_.ts
        }
    }


    \_(ツ)_/

    Thursday, June 25, 2020 12:54 PM
  • Hi,
    Was your issue resolved? 
    If you resolved it using our solution, please "mark it as answer" to help other community members find the helpful reply quickly.
    If you resolve it using your own solution, please share your experience and solution here. It will be very beneficial for other community members who have similar questions.
    If no, please reply and tell us the current situation in order to provide further help.
    Best Regards,
    Yang Yang

    Please remember to mark the replies as answers if they help.
    If you have feedback for TechNet Subscriber Support, contact tnmff@microsoft.com.

    Friday, July 3, 2020 5:46 AM