How to convert .xls to .csv using Powershell without Excel installed

Question

Is there a way to convert .xls to .csv without Excel being installed using Powershell?

I don't have access to Excel on a particular machine so I get an error when I try:

New-Object -ComObject excel.application

New-Object : Retrieving the COM class factory for component with CLSID {00000000-0000-0000-0000-000000000000} failed due to the following error: 80040154 Class not registered (Exception from HRESULT: 0x80040154 (REGDB_E_CLASSNOTREG)).

I'm not proficient enough with PowerShell to provide a full answer, but I think you can connect to the Excel file as an Odbc data source, even without Excel installed, and dump the results of a select query from that datasource to csv. The trick is knowing the name of the sheet(s). Also, what do you want to do if there is more than worksheet? — Joel Coehoorn
Found an example of what Joel was talking about here codeproject.com/Articles/670082/… and another one blogs.technet.com/b/heyscriptingguy/archive/2008/09/11/… — Matt
This is a possible duplicate of powershell excel access without installing Excel — Florian Feldhaus

Matt Matt · Accepted Answer · 2015-01-16T05:26:57

Forward

Depending on what you already have installed on your system you might need the Microsoft Access Database Engine 2010 Redistributable for this solution to work. That will give you access to the provider: "Microsoft.ACE.OLEDB.12.0"

Disclaimer: Not super impressed with the result and someone with more background could make this answer better but here it goes.

Code

$strFileName = "C:\temp\Book1.xls"
$strSheetName = 'Sheet1$'
$strProvider = "Provider=Microsoft.ACE.OLEDB.12.0"
$strDataSource = "Data Source = $strFileName"
$strExtend = "Extended Properties='Excel 8.0;HDR=Yes;IMEX=1';"
$strQuery = "Select * from [$strSheetName]"

$objConn = New-Object System.Data.OleDb.OleDbConnection("$strProvider;$strDataSource;$strExtend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($strQuery)
$sqlCommand.Connection = $objConn
$objConn.open()

$da = New-Object system.Data.OleDb.OleDbDataAdapter($sqlCommand)
$dt = New-Object system.Data.datatable
[void]$da.fill($dt)

$dataReader.close()
$objConn.close()

$dt

Create an ODBC connection to the excel file $strFileName. You need to know your sheet name and populate $strSheetName which helps build $strQuery. When then use several objects to create a connection and extract the data from the sheet as a System.Data.DataTable. In my test file, with one populated sheet, I had two columns of data. After running the code the output of $dt is:

letter number
------ ------
a           2
d          34
b           0
e           4

You could then take that table and then ExportTo-CSV

$dt | Export-Csv c:\temp\data.csv -NoTypeInformation

This was built based on information gathered from:

How to convert .xls to .csv using Powershell without Excel installed

1 Answers