locked
Convert Variant value to DateTime value RRS feed

  • Question

  • I have a powershell script that reads an excel spreadsheet and reads a cell value (which is 7/18/2018 in the spreadsheet), but when I read it and try to output the value, I get this:

    IsSettable          : True
    IsGettable          : True
    OverloadDefinitions : {Variant Value (Variant)}
    TypeNameOfValue     : System.Object
    MemberType          : ParameterizedProperty
    Value               : Variant Value (Variant) {get} {set}
    Name                : Value
    IsInstance          : True

    If I assign the variable to the datetime type in my code and then try to read the cell value, I get this error:

    Cannot convert the "Variant Value (Variant) {get} {set} " value of type
    "System.Management.Automation.PSParameterizedProperty" to type "System.DateTime".
    At C:\Scripts\RecordStats.ps1:67 char:4
    +             $cellValue = $worksheet.Cells.Item(1,2).Value
    +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
        + FullyQualifiedErrorId : RuntimeException

    Can anyone point me in the direction of how to do this?

    Wednesday, July 25, 2018 7:57 PM

All replies

  • Try "$cellValue = $worksheet.Cells.Item(1,2).Value2"

    OR:

    [datetime]$worksheet.Cells.Item(1,2).Value2


    \_(ツ)_/


    • Edited by jrv Wednesday, July 25, 2018 8:08 PM
    Wednesday, July 25, 2018 8:07 PM
  • Try "$cellValue = $worksheet.Cells.Item(1,2).Value2"

    OR:

    [datetime]$worksheet.Cells.Item(1,2).Value2


    \_(ツ)_/


    The first returns 43298 and the second returns an error:

    Cannot convert value "43298" to type "System.DateTime". Error: "String was not recognized as a valid DateTime."
    At C:\Scripts\RecordStats.ps1:67 char:4
    +             [datetime]$cellValue = $worksheet.Cells.Item(1,2).Value2
    +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : MetadataError: (:) [], ArgumentTransformationMetadataException
        + FullyQualifiedErrorId : RuntimeException

    Wednesday, July 25, 2018 8:42 PM
  • Clearly the cell does not contain a date  Ae you sure you have the correct cell?

    DO this to find out:

    Write-Host $worksheet.Cells.Item(1,2).Value2


    \_(ツ)_/

    Wednesday, July 25, 2018 8:47 PM
  • Here is how to get a datetime column:

    PS D:\scripts> $x = $wb.Worksheets[1].Cells[2,3].Value()
    PS D:\scripts> $x.GetType()

    IsPublic IsSerial Name                                     BaseType
    -------- -------- ----                                     --------
    True     True     DateTime                                 System.ValueType

    PS D:\scripts> $x

    Monday, January 1, 2018 12:00:00 AM


    \_(ツ)_/

    Wednesday, July 25, 2018 8:52 PM
  • I do have the correct cell...

    

    I modified the code such as this:

    			Write-Host $worksheet.Cells.Item(1,1).Value2
    			Write-Host $worksheet.Cells.Item(1,2).Value2
    			Write-Host $worksheet.Cells.Item(2,1).Value2
    			Write-Host $worksheet.Cells.Item(2,2).Value2
    

    And this is the results I get:

    PS C:\Scripts> .\RecordStats.ps1

    43298
    Proxy1
    3.39
    PS C:\Scripts>

    The properties of the cell with the date in it is formatted as a date field:


    Wednesday, July 25, 2018 8:58 PM
  • Here is how to get a datetime column:

    PS D:\scripts> $x = $wb.Worksheets[1].Cells[2,3].Value()
    PS D:\scripts> $x.GetType()

    IsPublic IsSerial Name                                     BaseType
    -------- -------- ----                                     --------
    True     True     DateTime                                 System.ValueType

    PS D:\scripts> $x

    Monday, January 1, 2018 12:00:00 AM


    \_(ツ)_/

    Using this code:

    			$cellValue = $worksheet.Cells.Item(1,2).Value()
    			$cellValue.GetType()
    			$cellValue
    

    I get this result:

    PS C:\Scripts> .\RecordStats.ps1

    IsPublic IsSerial Name                                     BaseType
    -------- -------- ----                                     --------
    True     True     DateTime                                 System.ValueType

    Date        : 7/17/2018 12:00:00 AM
    Day         : 17
    DayOfWeek   : Tuesday
    DayOfYear   : 198
    Hour        : 0
    Kind        : Unspecified
    Millisecond : 0
    Minute      : 0
    Month       : 7
    Second      : 0
    Ticks       : 636673824000000000
    TimeOfDay   : 00:00:00
    Year        : 2018
    DateTime    : Tuesday, July 17, 2018 12:00:00 AM


    Wednesday, July 25, 2018 9:05 PM
  • That is correct.  It is a datetime object now.

    What are you trying to get?


    \_(ツ)_/

    Wednesday, July 25, 2018 9:15 PM
  • I am trying to return a value that I can compare dates...

    Running this code:

    			$cellValue = $worksheet.Cells.Item(1,2).Value()
    			$cellValue.GetType()
    			$cellValue
    			
    			Write-Host $worksheet.Cells.Item(1,2).Value2
    			Write-Host $worksheet.Cells.Item(2,1).Value2
    			Write-Host $worksheet.Cells.Item(2,2).Value2
    

    produces this output:

    PS C:\Scripts> .\RecordStats.ps1

    IsPublic IsSerial Name                                     BaseType
    -------- -------- ----                                     --------
    True     True     DateTime                                 System.ValueType

    Date        : 7/17/2018 12:00:00 AM
    Day         : 17
    DayOfWeek   : Tuesday
    DayOfYear   : 198
    Hour        : 0
    Kind        : Unspecified
    Millisecond : 0
    Minute      : 0
    Month       : 7
    Second      : 0
    Ticks       : 636673824000000000
    TimeOfDay   : 00:00:00
    Year        : 2018
    DateTime    : Tuesday, July 17, 2018 12:00:00 AM

    43298
    Proxy1
    3.39

    I am still getting 43298 for a value, not 'Tuesday, July 17, 2018 12:00:00 AM' or '7/17/2018 12:00:00 AM'

    Thursday, July 26, 2018 12:37 PM
  • You have to use this form for dates.

    $wb.Worksheets[1].Cells[2,3].Value()

    Notice the "value()" is a method call and not a property,  It retrieves the value as the defined datatype.


    \_(ツ)_/

    Thursday, July 26, 2018 12:48 PM
  • I was able to get the value I needed by using this code:

    			$cellValue = $worksheet.Cells.Item(1,2).text
    			$cellValue
    

    Thanks for assisting me with my issue.

    Thursday, July 26, 2018 1:21 PM
  • That does not get you a datetime it gets text.  You wanted a datetime value.  "value()" gets a datetime object.


    \_(ツ)_/

    Thursday, July 26, 2018 1:24 PM
  • I was able to figure it out:

    $cellValue = $worksheet.Cells.Item($row,$column).Value()
    $cellDateTime = $cellValue.DateTime
    

    Thx again

    Thursday, July 26, 2018 4:01 PM