Reading PDMS Data into Microsoft Excel

A ScreenCam video demonstration file (excel.exe) showing the use of Excel to read in PDMS data comes with your installation.

1. Start Microsoft Excel

2. Select Data>Import External Data>Import Data…. The Select Data Source dialog box appears:

   

   Note:
   The list of data sources may differ from that shown above.

3. Any previous data source connections appear in this list and can simply be selected and opened using the Open button. This takes you directly to the Import Data screen shown at Step 10 below.

4. To create a connection to a new data source, click the New Source… button and select Other/Advanced on the Data Connection Wizard list displayed:

   

5. Click the Next> button and select PDMSProv OLE DB Provider 12.0 on the Data Link Properties list displayed:

   

6. Clicking the Next>> button will take you to the Connection tab of this Data Link Properties form:

   

7. Fill in the Data Source field with the connect string for the connection, and fill in the User Name and Password. You may have to uncheck the Blank Password check box before you can supply the password.

8. Clicking the OK button brings you to the Select Database and Table page of the Data Connection Wizard:

   

9. Select the table you wish to display and click the Next> button. This gives you an opportunity to name and save the link on the Save Data Connection File and Finish page:

   

10. Click the Finish button and Open the new link on the next screen. This leads to the final Import Data screen:

    

11. Finally press the OK button and the table will be displayed on the Excel page:

    

    Note:
    Data read into an Excel spreadsheet in this way can be edited if desired. However, there is no link back to PDMS, and changes made this way will not be reflected in the database.

Data can be modified when necessary using PDMS itself (e.g. a modify, create or delete operation) and should be followed by a Savework operation. When this has been done, executing a Refresh Data command in Excel will result in a corresponding change taking place in the spreadsheet (the Refresh Data command is on the Data menu, or you can click the icon.)

If you configure SQL Direct for use with SQL Server (see Using SQL Direct with Microsoft SQL Server) you can modify the data with an SQL Query. To do this, position the cursor outside the imported data, and select Data>Import External Data>New Database Query. Select New Data Source… and click OK. Enter any name of your choosing for the data source, and select SQL Server as the driver. Click the Connect… button and enter (local) as the server; click OK, then OK again. With your new data source highlighted, click OK; this brings up the Microsoft Query window. Close the Add Tables dialog that appears, and click the SQL button in the menu bar or select View>SQL… Enter an SQL Query to update your data and click OK. Click OK to the 'SQL Query can't be represented graphically…' warning, and again to the 'SQL statement has been executed successfully' message. Close the Microsoft Query window, clicking Yes to the 'You have not created a query containing data…' warning. Finally, execute a Refresh Data command in Excel to see your updated data.

You can save your Excel spreadsheet. After reloading, you may find that the Refresh Data command does not work. This is because your password will not be saved in the file by default. Edit the query (right click the data grid and select Edit Query…) and add the Password=<password>; option to the connection details.

You can email your Excel spreadsheet, with its current content, to another SQL Direct user. The recipient can refresh the data from his own SQL Direct configuration, providing he edits the query (right click the data grid and select Edit Query…) to enter his own connection details. He will also need the right table definitions in his table schema file.