Is it possible to get variable and where-object functions to work in import-excel?

[evolutionxtinct]

Discussed in #1255

^{Originally posted by evolutionxtinct September 23, 2022}
Hello, i'm trying to utilize the -WorksheetName parameter, and it doesn't seem to like utilizing a $variable and it doesn't allow me to utilize where-object to search for a name in a tab.

I use this functionality for terminations and things like that, but they utilize a excel file per year, but the tabs stay the same, sadly they put it in this format: "Year Term" so I can't just look for worksheetname "Term" I have to look for something that contains the word "Term" in the parameter. Is it possible to get this worked in as it would be huge for us, thanks!

It comes up w/ the following error:

Import-Excel does not support reading this extension type
At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.1\Public\Import-Excel.ps1:111 char:21
throw "Import-Excel does not support reading this extensi ...
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : OperationStopped: (Import-Excel do...extension type :String) [], RuntimeException
FullyQualifiedErrorId : Import-Excel does not support reading this extension type

[dfinke]

Please post the script/one liner that shows this

[evolutionxtinct]

Actually so this is what I was using but it seems to not work not sure what i'm doing wrong, I thought it was working: $varTerminatedUsers = Import-Excel "$USER_LIST" -ImportColumns @(2, 1, 7) -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true | Where-Object {$_.WorksheetName -contains "Term"} On Fri, Sep 23, 2022 at 11:37 AM Joe Smith ***@***.***> wrote:

…

....... I humbly request for you to ignore my issue, it seems to have been PEBKAC...... IDK why either didn't work not sure but now both options seem to be working...... I'm sorry for the waste of time. On Fri, Sep 23, 2022 at 11:29 AM Doug Finke ***@***.***> wrote: > Please post the script/one liner that shows this > > — > Reply to this email directly, view it on GitHub > <#1256 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AH5HFN3FBNZAAEUCSKD5UUDV7XSJDANCNFSM6AAAAAAQUDBPSA> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[evolutionxtinct]

Doug - Should I be able to pull a where-object from the Worksheetname property? Or am I doing something wrong? On Fri, Sep 23, 2022 at 11:39 AM Joe Smith ***@***.***> wrote:

…

Actually so this is what I was using but it seems to not work not sure what i'm doing wrong, I thought it was working: $varTerminatedUsers = Import-Excel "$USER_LIST" -ImportColumns @(2, 1, 7) -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true | Where-Object {$_.WorksheetName -contains "Term"} On Fri, Sep 23, 2022 at 11:37 AM Joe Smith ***@***.***> wrote: > ....... I humbly request for you to ignore my issue, it seems to have > been PEBKAC...... IDK why either didn't work not sure but now both options > seem to be working...... I'm sorry for the waste of time. > > On Fri, Sep 23, 2022 at 11:29 AM Doug Finke ***@***.***> > wrote: > >> Please post the script/one liner that shows this >> >> — >> Reply to this email directly, view it on GitHub >> <#1256 (comment)>, >> or unsubscribe >> <https://github.com/notifications/unsubscribe-auth/AH5HFN3FBNZAAEUCSKD5UUDV7XSJDANCNFSM6AAAAAAQUDBPSA> >> . >> You are receiving this because you authored the thread.Message ID: >> ***@***.***> >> >

[evolutionxtinct]

What i'm hoping to be able to do, is every year our HR has a new list of terminations that happen every month, I am trying to keep from having someone update this script every year when the year # changes, so was hoping that I could just find a worksheetname w/ the contents of "Term" in it. This is why I was hoping to be able to utilize where-object to search for a pattern on the worksheet names.

…

On Fri, Sep 23, 2022 at 3:55 PM Joe Smith ***@***.***> wrote: Doug - Should I be able to pull a where-object from the Worksheetname property? Or am I doing something wrong? On Fri, Sep 23, 2022 at 11:39 AM Joe Smith ***@***.***> wrote: > Actually so this is what I was using but it seems to not work not sure > what i'm doing wrong, I thought it was working: > > $varTerminatedUsers = Import-Excel "$USER_LIST" -ImportColumns @(2, 1, 7) > -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true | Where-Object > {$_.WorksheetName -contains "Term"} > > On Fri, Sep 23, 2022 at 11:37 AM Joe Smith ***@***.***> > wrote: > >> ....... I humbly request for you to ignore my issue, it seems to have >> been PEBKAC...... IDK why either didn't work not sure but now both options >> seem to be working...... I'm sorry for the waste of time. >> >> On Fri, Sep 23, 2022 at 11:29 AM Doug Finke ***@***.***> >> wrote: >> >>> Please post the script/one liner that shows this >>> >>> — >>> Reply to this email directly, view it on GitHub >>> <#1256 (comment)>, >>> or unsubscribe >>> <https://github.com/notifications/unsubscribe-auth/AH5HFN3FBNZAAEUCSKD5UUDV7XSJDANCNFSM6AAAAAAQUDBPSA> >>> . >>> You are receiving this because you authored the thread.Message ID: >>> ***@***.***> >>> >>

[dfinke]

Need to see some data in the Excel file for context

[dfinke]

ok, I think I understand better. Import-Excel "$USER_LIST" defaults to reading the first worksheet in the xlsx, it doesn't read all of them. The Import-Excel doesn't return the worksheet name.

$xlfile = '.\test.xlsx'
Get-ExcelFileSummary $xlfile | Where-Object {
    $_.WorksheetName -match 'Sheet'
} | ForEach-Object {
    Import-Excel -path $xlfile -WorksheetName $_.WorksheetName 
}

[evolutionxtinct]

Doug -- The only problem though is this is just a test XLSX w/ 1 tab w/ a specific name, in the production one it has multiple tabs, just this is the only tab i'm looking for is one that contains "Term"in it. Attached is my file, can you try to get it working w/ -contains for the word "term"

…

On Tue, Sep 27, 2022 at 11:34 AM Doug Finke ***@***.***> wrote: ok, I think I understand better. Import-Excel "$USER_LIST" defaults to reading the first worksheet in the xlsx, it doesn't read all of them. The Import-Excel doesn't return the worksheet name. $xlfile = '.\test.xlsx'Get-ExcelFileSummary $xlfile | Where-Object { $_.WorksheetName -match 'Sheet' } | ForEach-Object { Import-Excel -path $xlfile -WorksheetName $_.WorksheetName } — Reply to this email directly, view it on GitHub <#1256 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH5HFN5SJBDGFV243DSVPLDWAMVY5ANCNFSM6AAAAAAQUDBPSA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[evolutionxtinct]

So to understand, is import-excel not able to return the worksheetname? On Tue, Sep 27, 2022 at 11:36 AM Joe Smith ***@***.***> wrote:

…

Doug -- The only problem though is this is just a test XLSX w/ 1 tab w/ a specific name, in the production one it has multiple tabs, just this is the only tab i'm looking for is one that contains "Term"in it. Attached is my file, can you try to get it working w/ -contains for the word "term" On Tue, Sep 27, 2022 at 11:34 AM Doug Finke ***@***.***> wrote: > ok, I think I understand better. Import-Excel "$USER_LIST" defaults to > reading the first worksheet in the xlsx, it doesn't read all of them. The > Import-Excel doesn't return the worksheet name. > > $xlfile = '.\test.xlsx'Get-ExcelFileSummary $xlfile | Where-Object { > $_.WorksheetName -match 'Sheet' > } | ForEach-Object { > Import-Excel -path $xlfile -WorksheetName $_.WorksheetName > } > > — > Reply to this email directly, view it on GitHub > <#1256 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AH5HFN5SJBDGFV243DSVPLDWAMVY5ANCNFSM6AAAAAAQUDBPSA> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[dfinke]

Right, that's why you want to use Get-ExcelFileSummary, and pass the worksheets to process from there.

[evolutionxtinct]

What module is that CMDLET offered in? Thanks!

…

On Tue, Sep 27, 2022 at 11:39 AM Doug Finke ***@***.***> wrote: Right, that's why you want to use Get-ExcelFileSummary, and pass the worksheets to process from there. — Reply to this email directly, view it on GitHub <#1256 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AH5HFNZHJDNWPBYGDG7FZSDWAMWMLANCNFSM6AAAAAAQUDBPSA> . You are receiving this because you authored the thread.Message ID: ***@***.***>

[dfinke]

Those are all in the ImportExcel module

[evolutionxtinct]

nevermind found it some reason thought it wasn't a part of this module. On Tue, Sep 27, 2022 at 11:43 AM Joe Smith ***@***.***> wrote:

…

What module is that CMDLET offered in? Thanks! On Tue, Sep 27, 2022 at 11:39 AM Doug Finke ***@***.***> wrote: > Right, that's why you want to use Get-ExcelFileSummary, and pass the > worksheets to process from there. > > — > Reply to this email directly, view it on GitHub > <#1256 (comment)>, > or unsubscribe > <https://github.com/notifications/unsubscribe-auth/AH5HFNZHJDNWPBYGDG7FZSDWAMWMLANCNFSM6AAAAAAQUDBPSA> > . > You are receiving this because you authored the thread.Message ID: > ***@***.***> >

[evolutionxtinct]

So got it working by itself, but get another weird error with the module when runni t through a ForEach loop: *You cannot call a method on a null-valued expression.At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.1\Public\Import-Excel.ps1:251 char:40+ foreach ($entry in $xlbook.GetEnumerator()) {+ ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull Failed importing the Excel workbook 'C:\dump\test_terminations.xlsx' with worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetName': Worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetName' not found, the workbook only contains the worksheets '2022 Terms'. If you only wish to select the first worksheet, please remove the '-WorksheetName' parameter.At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.1\Public\Import-Excel.ps1:244 char:21+ ... catch { throw "Failed importing the Excel workbook '$Path' with w ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Failed importin...ame' parameter.:String) [], RuntimeException + FullyQualifiedErrorId : Failed importing the Excel workbook 'C:\dump\test_terminations.xlsx' with worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Colu mns=7; Address=A1:G7; Path=c:\dump}.WorksheetName': Worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetNam e' not found, the workbook only contains the worksheets '2022 Terms'. If you only wish to select the first worksheet, please remove the '-WorksheetName' parameter.* I actually added a 2nd sheet to get around this but it still doesn't work. I'm utilizing a ForEach loop basically this will pull the User First/Last & Email address, and i'm utilizing the email address for pulling the correct AD User. When I run a: ForEach($USER in $varTerminatedUsers) { ...} The $USER comes up w/ 0 instead of a value. When I static assign the worksheetname and try to not do this which-object case, I don't have the problem. I can strip out my script but it'll take some time. I'm now looking at a option to just get the bare string result of the worksheetname to see if that works, because when I utilize this method: $varExcelSheet = "2022 Terms" #$varTerminatedUsers = Import-Excel "$USER_LIST" -WorksheetName "$varExcelSheet" -ImportColumns @(2, 1, 7) -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true This works, I just need to grab just the name and i'll be golden but i'm not good enough at this to figure it out, sorry if this is difficult. On Tue, Sep 27, 2022 at 11:44 AM Joe Smith ***@***.***> wrote:

…

nevermind found it some reason thought it wasn't a part of this module. On Tue, Sep 27, 2022 at 11:43 AM Joe Smith ***@***.***> wrote: > What module is that CMDLET offered in? Thanks! > > On Tue, Sep 27, 2022 at 11:39 AM Doug Finke ***@***.***> > wrote: > >> Right, that's why you want to use Get-ExcelFileSummary, and pass the >> worksheets to process from there. >> >> — >> Reply to this email directly, view it on GitHub >> <#1256 (comment)>, >> or unsubscribe >> <https://github.com/notifications/unsubscribe-auth/AH5HFNZHJDNWPBYGDG7FZSDWAMWMLANCNFSM6AAAAAAQUDBPSA> >> . >> You are receiving this because you authored the thread.Message ID: >> ***@***.***> >> >

[evolutionxtinct]

I was able to do this: $worksheet = ($ExcelFileSummary.WorksheetName).ToString() $varTerminatedUsers = Import-Excel "$USER_LIST" -WorksheetName "$worksheet" -ImportColumns @(2, 1, 7) -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true and it now works when I pull just the string, not sure why the difference hopefully this helps explain my situation, thanks!

…

On Tue, Sep 27, 2022 at 2:18 PM Joe Smith ***@***.***> wrote: So got it working by itself, but get another weird error with the module when runni t through a ForEach loop: *You cannot call a method on a null-valued expression.At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.1\Public\Import-Excel.ps1:251 char:40+ foreach ($entry in $xlbook.GetEnumerator()) {+ ~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (:) [], RuntimeException + FullyQualifiedErrorId : InvokeMethodOnNull Failed importing the Excel workbook 'C:\dump\test_terminations.xlsx' with worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetName': Worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetName' not found, the workbook only contains the worksheets '2022 Terms'. If you only wish to select the first worksheet, please remove the '-WorksheetName' parameter.At C:\Program Files\WindowsPowerShell\Modules\ImportExcel\7.8.1\Public\Import-Excel.ps1:244 char:21+ ... catch { throw "Failed importing the Excel workbook '$Path' with w ...+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : OperationStopped: (Failed importin...ame' parameter.:String) [], RuntimeException + FullyQualifiedErrorId : Failed importing the Excel workbook 'C:\dump\test_terminations.xlsx' with worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Colu mns=7; Address=A1:G7; Path=c:\dump}.WorksheetName': Worksheet '@{ExcelFile=test_terminations.xlsx; WorksheetName=2022 Terms; Rows=7; Columns=7; Address=A1:G7; Path=c:\dump}.WorksheetNam e' not found, the workbook only contains the worksheets '2022 Terms'. If you only wish to select the first worksheet, please remove the '-WorksheetName' parameter.* I actually added a 2nd sheet to get around this but it still doesn't work. I'm utilizing a ForEach loop basically this will pull the User First/Last & Email address, and i'm utilizing the email address for pulling the correct AD User. When I run a: ForEach($USER in $varTerminatedUsers) { ...} The $USER comes up w/ 0 instead of a value. When I static assign the worksheetname and try to not do this which-object case, I don't have the problem. I can strip out my script but it'll take some time. I'm now looking at a option to just get the bare string result of the worksheetname to see if that works, because when I utilize this method: $varExcelSheet = "2022 Terms" #$varTerminatedUsers = Import-Excel "$USER_LIST" -WorksheetName "$varExcelSheet" -ImportColumns @(2, 1, 7) -StartRow 2 -NoHeader:$True -DataOnly:$true -Raw:$true This works, I just need to grab just the name and i'll be golden but i'm not good enough at this to figure it out, sorry if this is difficult. On Tue, Sep 27, 2022 at 11:44 AM Joe Smith ***@***.***> wrote: > nevermind found it some reason thought it wasn't a part of this module. > > On Tue, Sep 27, 2022 at 11:43 AM Joe Smith ***@***.***> > wrote: > >> What module is that CMDLET offered in? Thanks! >> >> On Tue, Sep 27, 2022 at 11:39 AM Doug Finke ***@***.***> >> wrote: >> >>> Right, that's why you want to use Get-ExcelFileSummary, and pass the >>> worksheets to process from there. >>> >>> — >>> Reply to this email directly, view it on GitHub >>> <#1256 (comment)>, >>> or unsubscribe >>> <https://github.com/notifications/unsubscribe-auth/AH5HFNZHJDNWPBYGDG7FZSDWAMWMLANCNFSM6AAAAAAQUDBPSA> >>> . >>> You are receiving this because you authored the thread.Message ID: >>> ***@***.***> >>> >>

[dfinke]

Great. How did you create the $ExcelFileSummary?