How to remove table header in Powershell and convert to Date type?

Question

Hey All I am trying to create a script to extract data from excel and the insert it into SQL.

The problem comes in when i extract date values and they become part of a table and whenever I try to use The following code I get the error at the bottom of this post. to extract and convert the excel "Relative Date" Number which is the 5-digit number that gets calculated from a set date.

The Code:

for ($i = 0; $i -lt 6; $i++)
  {
  for ($i2 = 0;$i2 -lt 10; $i2++)
    {

    $ExcelDate = Import-Excel -Path $path -StartColumn 2 -EndColumn 2 -StartRow 7 -EndRow 20 
    -WorksheetName $WeekDays[$i]

        [DateTime]::FromOADate($ExcelDate[$i2])
     }   
   }

The Error:

Cannot convert argument "d", with value: "@{Date Del.=44293}", for 
"FromOADate" to type "System.Double": "Cannot convert the "@{Date Del.=44293}" 
value of type "System.Management.Automation.PSCustomObject" to type 
"System.Double"."
At H:\My Files\Jobs\Projects\In Progress\Killsheet Data Import\Importing data 
test v1.ps1:75 char:9
+         [DateTime]::FromOADate($ExcelDate[$i2])
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodException
    + FullyQualifiedErrorId : MethodArgumentConversionInvalidCastArgument

I need help with changing the @{date Del.=44293} into a "String Value" of "44293"

Answer 1

The Import-Excel cmdlet return an array of PSCustomObject types. The cell values are stored as properties on the objects and the name of the property is set to the header name of your column. So to access the value you need to write $ExcelDate[$i2].NameOfColumn.

You should notice that Import-Excel usually manages to convert Date columns into DateTime for you so you shouldn't need to convert it using FromOADate.

If you're unsure what properties are available on a Powershell object you can check this using the cmdlet Get-Member. For example $ExcelDate[$i2] | Get-Member for me outputs:

   TypeName: System.Management.Automation.PSCustomObject

Name                MemberType   Definition
----                ----------   ----------
Equals              Method       bool Equals(System.Object obj)
GetHashCode         Method       int GetHashCode()
GetType             Method       type GetType()
ToString            Method       string ToString()
MyExampleDateColumn NoteProperty datetime MyExampleDateColumn=2021-04-03 00:00:00