Query for a List of Systems from an Excel Spreadsheet

Follow

Revision: 2.0
Date: 04/19/2016

Problem:

You are maintaining a list of machine or devices to manage in an excel spreadsheet and wish to use that spreadsheet as a source of system information for your Lieberman product.

Resolution:

  1. To read from an Excel spreadsheet, you will need to know where in the spreadsheet your data is listed. Specifically, what sheet and what columns/rows.
  2. Download the 32-bit "Microsoft Access Database Engine 2010 Redistributable" from here: https://www.microsoft.com/en-us/down....aspx?id=13255.
  3. In the product, open your management set properties and select the Data Sources tab (tools) or click Add and select Targets for query to a data source (ERPM).
  4. Click the new button in the top right corner (tools) or click the configure button (ERPM).
  5. Select the option to Allow Manual Editing of the Connection String.
  6. Set the connection string to: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PATH_TO_SPREADSHEET;Extended Properties="Excel 12.0;HDR=YES";
    • For Example: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\book1.xlsx;Extended Properties="Excel 12.0;HDR=YES";
  7. Setup the query to your data. This will identify the sheet, columns and row numbers to pull from. The format is: select * from SHEET$COLUMN-START:COLUMN-END.
    • For example: select * from [Sheet1$b1:b20]
    • The above example will select from sheet 1, column b row 1 through column b row 20
    • Note that the provider will not include row 1 in the query as this is typically column name headers.
Applies To:
  • Enterprise Random Password Manager (ERPM)
  • User Manager Pro Suite (UMPS)
  • Service Account Manager (SAM)
  • Task Scheduler Pro (TSP)
Was this article helpful?
1 out of 1 found this helpful

Comments

Powered by Zendesk