Link or import data

These instructions describe how to import data on a 32-bit or 64-bit PI AF server and how to link to data from a 32-bit PI AF server only. To link to data from a 64-bit server, see Link to data on a 64-bit PI AF server.

Important: We do not recommend using Excel files for linked tables. See the KB article: Do not link AF tables to Excel files.

1. In PI System Explorer, navigate to the PI AF table or create one as described in Create PI AF tables.

2. In the Library pane, expand the Tables node, and click the desired PI AF table.

   The table details display in the right pane.

3. Click Link or Import.

   The corresponding window opens.

4. Link only: If you are linking the table, enable the Impersonate Client option (not displayed for Import).

5. Click Build.

   The Data Link Properties window opens.

6. On the Provider tab, select the provider according to the version of Microsoft Office that you are using and click Next.

   • Office 97-2003: select Microsoft Jet 4.0 OLE DB Provider.

   • Office 2007 and higher: select Microsoft Office 12.0 Access Database Engine OLE DB Provider.

7. On the Connection tab, specify the following and click OK.

   • Data Source

     The location and file name of the database (such as C:\AFTestData.accdb). If you are linking, the path to the file must be relative to the PI AF server.

   • User Name

     Login credentials of a user that has been granted read access to the database.

     Note: To store the password with the connection information, select the Allow Saving Password check box. The password is stored as plain text (not encrypted).

8. On the Advanced tab, in the Access permissions list, select Share Deny None.

9. To verify that the spreadsheet is accessible, return to the Connection tab and click Test Connection.

   If the settings are valid, a Test connection succeeded message displays.

10. To dismiss the window and return to PI System Explorer, click OK.

11. To define the data to be returned from the spreadsheet, enter an SQL query in the Query field. To dismiss the window, click OK.

    • Microsoft Access example: SELECT * FROM Table1

12. To review the resulting data, examine the Table tab. If the query is specified correctly, the tab contains a table displaying the results.

13. To save your changes, right-click the table node and choose Check In.