Invalid Operation when trying to open file stored on a PNP.Sharepoint connected PSDrive

[cmcknz77]

I have PSDrive (HDT:) connected to a sharepoint site that contains a document library (Local Site Information).
I'm running this in the newly released Powershell 7.5
When I try to use Import-Excel to open a .xlsx file in that library I am presented with a series of errors:

Obviously there are some redactions and emphasis added to the screenshot but it strikes me that Import-Excel is looking to the root of the C: drive for the file to open instead of the root of the provided PSDrive that was opened using the Connect-Pnponline -Url $URL -interactive -CreateDrive -DriveName HDT cmdlet from the PNP.Sharepoint module.

Any ideas?

[dfinke]

if you copy the file locally from the sharepoint site, does import-excel work?

[cmcknz77]

if you copy the file locally from the sharepoint site, does import-excel work?

Absolutely it does.
I've done a little further digging. I think that it's due to the difference in the objects returned by Get-Item when the file is stored on the local filesystem

versus when the file is stored on a PSDrive connected to Sharepoint Online.

So, even though the path is resolving correctly on line 114, New-Object is failing to read the file from the provided path.

[cmcknz77]

PS HDT:\sites\HDT\> $path = "HDT:\sites\HDT\Local Site Information\ASITE\ASITE-APs.xlsx"
PS HDT:\sites\HDT\> Resolve-Path $path

Path
----
HDT:\sites\HDT\Local Site Information\ASITE\ASITE-APs.xlsx


PS HDT:\sites\HDT\> Resolve-Path $path | fl *


Drive        : HDT
Provider     : SharePointPnPPowerShellOnline\SharePoint
ProviderPath : \sites\HDT\Local Site Information\ASITE\ASITE-APs.xlsx
Path         : HDT:\sites\HDT\Local Site Information\ASITE\ASITE-APs.xlsx

PS HDT:\sites\HDT\> (get-psprovider 'SharePoint') | fl *


ImplementingType : PnP.PowerShell.Commands.Provider.SPOProvider
HelpFile         : PnP.PowerShell.Online.Commands.dll-Help.xml
Name             : SharePoint
PSSnapIn         :
ModuleName       : SharePointPnPPowerShellOnline
Module           : SharePointPnPPowerShellOnline
Description      :
Capabilities     : ShouldProcess
Home             :
Drives           : {HDT}

[cmcknz77]

Versus a Local Path

PS HDT:\sites\HDT\> $localpath = "C:\Users\mytestuser\Downloads\ASITE-APs.xlsx"
PS HDT:\sites\HDT\> Resolve-Path $localpath | fl *


Drive        : C
Provider     : Microsoft.PowerShell.Core\FileSystem
ProviderPath : C:\Users\mytestuser\Downloads\ASITE-APs.xlsx
Path         : C:\Users\mytestuser\Downloads\ASITE-APs.xlsx



PS HDT:\sites\HDT\> (Get-PSProvider 'FileSystem') | fl *


ImplementingType : Microsoft.PowerShell.Commands.FileSystemProvider
HelpFile         : System.Management.Automation.dll-Help.xml
Name             : FileSystem
PSSnapIn         : Microsoft.PowerShell.Core
ModuleName       : Microsoft.PowerShell.Core
Module           :
Description      :
Capabilities     : Filter, ShouldProcess, Credentials
Home             : C:\Users\mytestuser
Drives           : {C}

Is there a reason why you're choosing to use the "ProviderPath" attribute instead of the "Path" attribute on line 116 of https://github.com/dfinke/ImportExcel/blob/master/Public/Import-Excel.ps1 ? Would changing that replaced attribute to 'Path' instead of 'ProviderPath' allow the rest of the function to cover use case in both scenarios?

[dfinke]

hmm. Don't know off the top of my head. Changing that would require testing all those scenarios.

Would the workaround of copying the file locally, update and save back to SP work?

[cmcknz77]

For the moment, good sir, it's gonna have to :-)
I went a little further and tried to open a stream to the confirmed file-path on SharepointOnline:
I got the following:

PS C:> $path = "HDT:\sites\HDT\Local Site Information\ASITE\ASITE-APs.xlsx"
PS C:> $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $Path, 'Open', 'Read', 'ReadWrite'
New-Object : Exception calling ".ctor" with "4" argument(s): "The given path's format is not supported."
At line:1 char:11

• $stream = New-Object -TypeName System.IO.FileStream -ArgumentList $Pa ...

•       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  

  • CategoryInfo : InvalidOperation: (:) [New-Object], MethodInvocationException
  • FullyQualifiedErrorId : ConstructorInvokedThrowException,Microsoft.PowerShell.Commands.NewObjectCommand

I'll let you know if I find anything out but I'll work around it in the meantime.

[dfinke]

I don't have access to SP so I would not be able to test this scenario.