It could be that the PowerShell session is using the SqlServer provider by default. Please try to override it use Set-Location c:
Convert sharepoint online excel spreadsheet to csv using powershell
Hi,
We have an excel (xlsx) file that is saved to the documents folder on an online sharepoint location, is there a way to read that file into a memory stream, connect to a particular sheet and then read the contents of that sheet and put into variables so can either a) output into an sql query or b) resave into a csv file for reading and outputting into an sql query, so can be inserted into a sql table
i know you can download and save the file, then convert it but I'm unable to save the file for security reasons so need to do in memory
I also cannot use openxml as this will not be installed on the server running the script
thanks
-
MichaelHan-MSFT 18,016 Reputation points
2021-07-28T09:40:38.367+00:00
3 additional answers
Sort by: Most helpful
-
MichaelHan-MSFT 18,016 Reputation points
2021-07-20T02:27:52.607+00:00 Hi @Walkerx ,
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-uplAnother 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. -
Walkerx 141 Reputation points
2021-07-20T06:00:11.717+00:00 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.
-
Walkerx 141 Reputation points
2021-07-27T09:45:44.21+00:00 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