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"""

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

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

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

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”

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!


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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s