Convert sharepoint online excel spreadsheet to csv using powershell

Walkerx 141 Reputation points
2021-07-19T08:26:50.043+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,690 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,607 questions
SharePoint Development
SharePoint Development
SharePoint: A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.Development: The process of researching, productizing, and refining new or existing technologies.
2,664 questions
0 comments
Accepted answer
  1. MichaelHan-MSFT 18,016 Reputation points
    2021-07-28T09:40:38.367+00:00

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

    1 person found this answer helpful.

3 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. 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-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.

    0 comments
  2. 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.

  3. Walkerx 141 Reputation points
    2021-07-27T09:45:44.21+00:00

    @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

    0 comments