Convert sharepoint online excel spreadsheet to csv using powershell

Question

Convert sharepoint online excel spreadsheet to csv using powershell

Walkerx 141

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

0 comments

Answer accepted by question author

3 additional answers

Your answer

Answer 1

MichaelHan-MSFT 18,136

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

Walkerx 141 Reputation points

2021-07-28T11:23:21.627+00:00

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
MichaelHan-MSFT 18,136 Reputation points

2021-07-29T01:23:37.303+00:00

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

Answer 2

@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

Answer 3

Walkerx 141

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 18,136 Reputation points

2021-07-26T06:02:49.117+00:00

@Walkerx ,
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.
Walkerx 141 Reputation points

2021-07-26T06:06:42.847+00:00

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
MichaelHan-MSFT 18,136 Reputation points

2021-07-26T06:13:33.717+00:00

Yes, it's the same with csv files.

Answer 4

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-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.

Share via

Convert sharepoint online excel spreadsheet to csv using powershell

3 additional answers

Your answer