Tutorial: Use Excel to Access PowerShift Data
If you use PowerShift, you probably already know that you can output reports to Excel or use the XML export programs to get PowerShift data into Excel. But you might not know that you can access PowerShift data directly from Excel. Many PowerShift customers use this ability -- called ODBC connectivity -- for expanded data analysis and reporting.
Following is a screen-by-screen tutorial that demonstrates how to access PowerShift data from Excel:
- Open a blank Excel workbook.
- In
recent versions: Select Data | From Other Sources | From Microsoft Query.
In old versions: Select Data | Get External Data | New Database Query.
- Select the data source defined as the PowerShift database and click OK.
Note: Your system administrator can point you to the correct data source or establish a connection if it is not listed on the Databases tab.
- You'll be promtped for a user name and password:
- Once your password is validated, a list of tables is displayed.
If you're unsure of a table (aka file) name or column (aka field) name, you can use the PowerShift Data File Inquiry (DFQ) program. The table and column names match the #FILES names.
In this example, the CUST_MST table (aka Customer Master file) is selected.
- You can click the > button to include all the Customer Master fields, or, click the + next to CUST_MST to list fields (shown below) and then highlight a field and click > to select it.
- The columns will be output in the sequence selected; you can use the up/down arrows adjacent to the column names to re-order them.
- After you've selected the fields you want to include, click Next. You can optionally add filters and sorts and save the inquiry.
- Editing the data in Microsoft Query is beyond the scope of this tutorial, so instead opt to return the data to Excel. The program prompts:
- Click OK to accept defaults. A snapshot of your PowerShift data appears.
- If you want to refresh worksheet data from PowerShift, right click on any imported cell and the select Refresh. The PowerShift database is queried and your worksheet is updated with current data.
The ability for Excel -- or other ODBC-enabled application -- to directly access PowerShift data requires the QICWARE SQL server and ODBC drivers. If you have questions about using these tools, contact the Qantel Helpdesk or call us at 630.300.6999.
