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:
|
1 2 |
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)).
Answer:
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
|
1 2 3 4 5 6 7 8 9 10 11 12 |
$strFileName = "C:\temp\Book1.xls" $strSheetName = 'Sheet1 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:------ ------ a 2 d 34 b 0 e 4 |
You could then take that table and then ExportTo-CSV
|
1 2 |
$dt | Export-Csv c:\temp\data.csv -NoTypeInformation |
This was built based on information gathered from:
Source:
How to convert .xls to .csv using Powershell without Excel installed by stackoverflow.com licensed under CC BY-SA | With most appropriate answer!
$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:
|
1 |
You could then take that table and then ExportTo-CSV
|
1 |
This was built based on information gathered from: