Help converting csv to xls for online sharepoint viewing through o365

Mike Satterfield
Mike Satterfield used Ask the Experts™
on
I am trying to convert a csv file into a xls through a powershell script so that it will be viewable on sharepoint 365, without users having to convert the file, or open it in a thick client.

Any help is greatly apprenticed.

thanks

I am attaching a copy of one of the csv files i am working with.
SCORECARD-G3-MATH-RS.csv
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jose Gabriel Ortega CastroTop Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018

Commented:
cd "D:\Libs\Desktop\Scripts\EE\ee8"
$ImportedData=Import-Csv .\SCORECARD-G3-MATH-RS.csv
$objMembers = ($ImportedData| select * -First 1 ).psobject.Members | where {$_.membertype -ne "Method"} | select -ExpandProperty name

#Create excel COM object
$excel = New-Object -ComObject excel.application

#Make Visible
$excel.Visible = $True
$excel.DisplayAlerts = $False

#Add a workbook
$workbook = $excel.Workbooks.Add()

#Connect to first worksheet to rename and make active
$serverInfoSheet = $workbook.Worksheets.Item(1)
$serverInfoSheet.Activate() | Out-Null

$serverInfoSheet.Name = 'Info'

#Create a Title for the first worksheet
$row = 1; $Column = 1

foreach($prop in $objMembers){
    $serverInfoSheet.Cells.Item($row,$column)= $prop
    $Column++;
}

$row++
foreach($item in $ImportedData){
    $column=1;
    foreach($val in $Item.psobject.properties){
        $serverInfoSheet.Cells.Item($row,$column)= $val.value
        $column++
    }
    $row++
}

$workbook.SaveAs("$PSScriptRoot\here.xlsx")

#Quit the application
$excel.Quit()

[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()

[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workSheet)
#Release COM Object
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$excel) | Out-Null

Remove-Variable -Name excel

Open in new window

Author

Commented:
Thank you for your response, however when i try to run the script within mine i get the following error:

Exception calling "ReleaseComObject" with "1" argument(s): "Object reference not set to an instance of an object."
At C:\FTPRoot\PowerBI\ProcessBI.ps1:927 char:1
+ [System.Runtime.Interopservices.Marshal]::ReleaseComObject($workSheet ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Line 927 is:
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workSheet)

Thank you,
Top Rated Freelancer on MS Technologies
Awarded 2018
Distinguished Expert 2018
Commented:
You should use this inside a computer that has Office installed.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial