question

Hi @Walkerx-7944,

We can read excel file into a memory stream. But to read the data of the excel sheet in a memory stream, you have to use open xml to achieve this.
You could refer to this post: https://sharepoint.stackexchange.com/questions/280903/powershell-read-excel-file-data-in-document-library-and-then-modify-it-and-upl

Another workaroud is opening the excel file in powershell, this requires excel installed. Below is my sample:

 $ExcelObject = New-Object -ComObject Excel.Application
 $ExcelWorkBook = $ExcelObject.Workbooks.Open("https://<tenant>.sharepoint.com/sites/test/Shared%20Documents/Book.xlsx")
 $ExcelWorkSheet = $ExcelWorkBook.Sheets.Item(1)
 #Read  the A:1 cell
 $ExcelWorkSheet.Cells.Item(1,1).Text

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Thanks,

I had already looked at that link, but because I'm unable to install OpenXML on the server that would be running the script I am unable to implement this solution

I was able to originally download the required file to a network path, load this, but every time I used the option to save the file ($excel.SaveAs($newurl,42) ) it failed either to the network path or back to the sharepoint location.

@MichaelHan-MSFT

Thanks for the help on this, i've managed to get stage further and got the file imports working for csv when testing, but when run as an automated process the import-csv is failing

I have variable setup called TargetFile which is unc path to file

I can then do a Test-Path $TargetFile and this returns that the file is available, but when I use the import-csv I get an error 'ReportWrongProviderType is not valid, remove operation ReportWrongProviderType' but that is not in the command

I use the following command to import the data
$filedata=import-csv $TargetFile |Select "Header1","Header2","Header3|"
then cycle through each row with
foreach ($rowdata in $filedata)
{$resulta=$rowdata.[0].Header1
}

This all works fine if run through powershell ise, but i'm running this as an automated sql agent job