How to export data from SQL database to Microsoft Office Excel and make it updated automatically?
Issue
A user would like to export data from SQL database to Microsoft Office Excel and wants that data to be updated/refreshed automatically.
Product Line
SQL Server Management Studio
Power Monitoring Expert (PME)
Environment
SQL Server Management Studio
Microsoft Office Excel
Cause
Data needs to be exported from the ION database into Microsoft Office Excel, and the data tables in Excel need to be updated automatically.
Resolution
1) Run Microsoft Office Excel and click on the "Data" tab from the menu bar.
2) From "Get Data" options, click on "From Database" and choose "From SQL Server Database". A new pop-up window, "Data Connection Wizard" will appear.
3) Type SQL server name, it should be something like "<your server name>\ION". You can find this name when opening SSMS and connecting to the database instance, and it will show in the "Server Name" box. Choose "Use Windows Authentication" to log on as current credentials. If these credentials do not have access to the database, choose the "Use alternate credentials" option to sign in.
Note for PME 7.2.x and PME8.0 Systems: Some Windows users may not have sufficient privileges to access ION database. In such a case, change the "Use Windows Authentication" to "Use the following Username and Password" and enter the following SQL server authentication.
Username: Report
Password: rep0rter! (NOTE: this password is valid for PME 7.2.x and PME8.0)
Click "Next".
4) Choose the database where your desired table is saved from the drop-down menu and then choose the table you want from the list that appears below the name of the database. Click "Load". The example below shows the table vDataLog from the ION_Data database.
5) Optional: You can change the name of the table at the top left. You can also choose how the data is desired to be displayed. Regular table, PivotTable report, or PivotChart and PivotTable report. Also, choose which worksheet you want the data to be displayed on.
6) Optional: To further modify the query, you can click on the "Query" tab and "Edit" to make the changes necessary.
Up to this point, a connection has been established between the Excel file and the database, and data should be displayed on the worksheet. However, check to make sure that it is updated automatically. You can do so following the steps below:
1) Click on "Data" from the menu bar. From "Connections" options, click on "Refresh All" drop-down menu and choose "Connection properties."
2) A new pop-up window will appear from the usage tab. Make sure to check "Enable background refresh" and "refresh every x minutes," where x is the desired choice of how frequently you want the data to be updated.
3) Click OK.
The data on the Excel worksheet will start to be updated/refreshed automatically now.