Marc Lognoul's IT Infrastructure Blog

Cloudy with a Chance of On-Prem

PowerShell: Reading an Excel Sheet using ADO.Net

Leave a comment

Nothing new here, just a repost by popular demand… 
Many examples on the Internet show how to use the Office Automation COM object to achieve this. But under some circumstances, this is not possible because Excel is simply not installed locally.


Lets instantiate the objects we need:
$OleDbConn = New-Object "System.Data.OleDb.OleDbConnection"
$OleDbCmd = New-Object "System.Data.OleDb.OleDbCommand"
$OleDbAdapter = New-Object "System.Data.OleDb.OleDbDataAdapter"
$DataTable = New-Object "System.Data.DataTable"


Set the connection string and connect. Please pay attention to the syntax, otherwise, you’ll get cryptic errors such as “Could not find installable ISAM”. Also, the file should not be locked exclusively
$OleDbConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:UserslognoulmDesktopservercfg.xls;Extended Properties=""Excel 8.0;HDR=YES"""
$OleDbConn.Open()


Optionally, to check that the connection is open, display the “State” property:
$OleDbConn.State


Now let’s construct a SQL query. Syntax for Excel is a little special, look at the end of this post for external references.
$OleDbCmd.Connection = $OleDbConn
$OleDbCmd.CommandText = "SELECT * FROM [Sheet1$]"


Then set the Adapter object
$OleDbAdapter.SelectCommand = $OleDbCmd


And then fill the DataTable object with the results
$OleDbAdapter.Fill($DataTable)


If everything went fine, the command above will return the number of row present is the DataTable object. To display the “raw” contents, just enter
$DataTable


To show the first line (aka Row), use this $DataTable.Rows[0]
And how to display a given field in that row? Just use the field header. In my XLS, one header is for example “Name”
$DataTable.Rows[0].Name

More information can be found here:

Note: This was not tested using an XLSX files but with a standard XLS instead
Note 2: DataSet can be used instead of DataTable object but I prefer this one in favor of greater simplicity (this is to be used by sysadmins, not developersJ)
Note 3: I blogged about reading but you can update and insert too, see references above for details

And cut!

Advertisements

Author: Marc Lognoul

Relentless cloud professional. Restless rider. Happy husband. Proud father. Opinions are my own.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s