Odd behaviour on the return of Import-Excel function

[DykVanDyke]

Hello,

When we import an Excel file containing only 1 row of headers or 1 row of headers and 1 row of data, the function Import-Excel behaves in an odd manner, making the task of controling the size of the imported array of data a bit difficult.

With the attached file test.xlsx, you can test yourself the 2 cases for which the return of the function is a bit odd:

testing scenario 1: sheet 'no data'

$rows = Import-Excel -Path .\test.xlsx -WorksheetName 'no data'

In this case, the return of the function is an Object[] with 2 rows! The 1st is empty and the 2nd contains the headers.

testing scenario 2: sheet '1 row'

$rows = Import-Excel -Path .\test.xlsx -WorksheetName '1 row'
In this case, the return of the function is PSCustomObject.

For sheets with 2 or more rows, we get again returned objects of type Object[], which is the expected behaviour.

My questions/requests:

1. Couldn't we have the function returning always the type Object[] even for scenario 2?

2. In scenario 1, couldn't we have the function returning simply an empty object[] for which we simply would test the property count in order to check the empty contents?

test.xlsx

[dfinke]

@jhoneill I thought we had resolve this? Maybe something similar?

[uSlackr]

looks somewhat like this one and this one

[jhoneill]

Importing a one row sheet doesn't have a properly defined result. What it's doing at the moment certainly looks wrong.

Commands don't return object[].
The return object
or object, object
orobject, object, object
if you do $x = 1 PowerShell gives you an int
if you do $x = 1,2,3 PowerShell gives you an array.
Even if you do return $result and
$y = get-content foo.txt will be a string if foo is one line, and an array if foo is many lines.

Generally you can do $singleItem | forEach-object or foreach ($item in $singleItem) and all is fine. PowerShell means you don't have to worry (much) about types and singletons vs arrays.

[dfinke]

@DykVanDyke Yeah, this is a bug, need to figure out to remedy it. Optimally without introducing breaking changes.

[dfinke]

@DykVanDyke The fix is ready. If there are no rows, it issues a warning, and a $null result is retuned. Patterned after ConvertFrom-Csv.

You can grab the update module here and try it out before it is pushed to the gallery:

https://dougfinke.visualstudio.com/ImportExcel/_build/results?buildId=617&view=artifacts&type=publishedArtifacts