locked
Opening Excel Sheet with ODBC Driver RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    I am trying to open an Excel sheet from a server where Office isn't installed. Although, we have Microsoft Excel Driver installed.

    I am trying to do something fairly simple: Import into Powershell the data from that Excel sheet.

    $Excel = New-Object -Com Excel.Application

$path= '"C:\Users\gpiche001\Desktop\UserList.xlsx'; 
$openfile= $Excel.workbooks.open($path); 
$Sheet= $openfile.worksheets.item(2); 
$arrExcelValues = @()

    I get the following error:

    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 understand that this is probably caused by Powershell not being able to open Excel. I was wondering how can I make it work by leveraging the MS Excel Driver?

    Thanks!
    • Edited by Gab Piche Wednesday, August 2, 2017 6:30 PM
    Wednesday, August 2, 2017 6:29 PM

Answers

  • Question
    Sign in to vote
    1
    Sign in to vote

    This is caused by Excel not being installed.

    The MS Excel driver requires the installations of the ACE drivers from the MSAccess runtime package.

    <#	
	.NOTES
	===========================================================================
	 Created with: 	SAPIEN Technologies, Inc., PowerShell Studio 2015 v4.2.81
	 Created on:   	3/6/2015 10:51 AM
	 Created by:   	James Vierra
	 Organization: 	Designed Systems & Services
	 Filename:     	
	===========================================================================
	.DESCRIPTION
		THis is a template derived from this blog post:
        	http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/15/how-can-i-write-to-excel-without-using-excel.aspx
    .LINK
		http://blogs.technet.com/b/heyscriptingguy/archive/2008/09/15/how-can-i-write-to-excel-without-using-excel.aspx
		http://1drv.ms/1waQ1LA
#>
Begin{
    [string]$fileName = 'C:\temp2\update_tester.xlsx'
    #	# old connection string won't work on Win 7 and later systems.
	#$connectionString='Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$fileName;Extended Properties=Excel 8.0'
	$connectionString= 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\temp2\update_tester.xlsx;Extended Properties="Excel 12.0;HDR=YES";Persist Security Info=False'
	$query='select * from [Sheet1$]'
	$conn=new-object System.Data.OleDb.OleDbConnection($connectionString)
}
Process{
	$conn.Open()
	$cmd=$conn.CreateCommand()
	$cmd.CommandText=$query
    $adpt= New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
    $dt= New-Object System.Data.DataTable
    $adpt.Fill($dt)
    $dt
    
}
End{
	$conn.Close()
}

    \_(ツ)_/

    • Marked as answer by Gab Piche Wednesday, August 2, 2017 7:05 PM
    Wednesday, August 2, 2017 6:42 PM