Invoke-ExcelQuery doesn't pull in excess of 10 fields?

[helpdeskaleer]

Import-Module Import-Excel
$q = 'SELECT * FROM [duhsheet$A3:P11]'
Invoke-ExcelQuery .\some_sheet.xlsx $q

It only returns 16x columns; it should return like 20+ columns.

If I try to access it for instance SELECT F17 it breaks with the following error:

Exception calling "Fill" with "1" argument(s): "No value given for one or more required parameters."
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.5.1\Public\Read-OleDbData.ps1:51 char:5
+     $null = $DataAdapter.Fill($DataTable)
+     ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

Does this maybe have something to do with some kind of OLEDB connection string that I am not passing to the Invoke-Query method?

[matt-bloomfield]

Hi, welcome to the forum

@helpdeskaleer

$q = 'SELECT * FROM [duhsheet$A3:P11]'

A to P = 16 columns.

Your query will return 16 columns (A to P) and 9 rows (3 to 11 inclusive).

What code are you using to select F17 and what’s your expected result? For example,

Invoke-ExcelQuery .\some_sheet.xlsx $q | Select-Object F17

should return rows 3 to 11 of column R.

[helpdeskaleer]

Thanks!

Is there a hash or function I can call somewhere to get a number instead?

[matt-bloomfield]

Sorry, I don’t understand the question.

[helpdeskaleer]

Something where I can use P as an input and get back 16 instead of counting all the time.

[matt-bloomfield]

Something like this?

$Alphabet = (65..90 | Foreach-Object { [Char]$_ }) -join ''
$Alphabet.IndexOf('P') + 1