Query for a List of Systems from an Excel Spreadsheet
To use an Excel spreadsheet as a source of system information for Privileged Identity:
- 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.
- Download the 32-bit Microsoft Access Database Engine 2010 Redistributable from the Microsoft Download Center.
- 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.
- Click the new button in the top right corner (tools) or click the configure button.
- Select the option to Allow Manual Editing of the Connection String.
- 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";
- 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.