question

Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 asked MichaelHan-MSFT edited

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

sql-server-generaloffice-sharepoint-onlinesharepoint-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MichaelHan-MSFT avatar image
1 Vote"
MichaelHan-MSFT answered MichaelHan-MSFT commented

It could be that the PowerShell session is using the SqlServer provider by default. Please try to override it use Set-Location c:

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

A simple command like that worked :) not one of the resources i found on google mentioned that

II've tested a couple of files and it seems to now be working

thank you very much

0 Votes 0 ·

Glad that helped you. You could accept my answer via the "Accept Answer" button to close this thread :)

0 Votes 0 ·
MichaelHan-MSFT avatar image
0 Votes"
MichaelHan-MSFT answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered MichaelHan-MSFT commented

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.



· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@Walkerx-7944,
As far as i know, using openXML is the only way to read the content in memory stream. There are no other ways.

Otherwise you have to download the file to a temporary path in local server, then read the content in it.

0 Votes 0 ·

thanks, is that the same with a standard csv file, that to read in memory i need to use openxml as managed to get users to save the file in csv format

0 Votes 0 ·

Yes, it's the same with csv files.

1 Vote 1 ·
Walkerx-7944 avatar image
0 Votes"
Walkerx-7944 answered MichaelHan-MSFT converted comment to answer

@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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.