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