Query for a List of Systems from an Excel Spreadsheet

To use an Excel spreadsheet as a source of system information for Privileged Identity:

  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 the Microsoft Download Center.
  3. In Privileged Identity, open your management set properties and select the Data Sources tab (tools) or click Add and select Targets for query to a data source.
  4. Click the new button in the top right corner (tools) or click the configure button.
  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. Configure the query to your data. This will identify the sheet, columns and row numbers from which to pull. 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 typically contains column name headers.