{}

Our Brands

Impact-Company-Logo-English Black-01-177x54

Welcome to the Schneider Electric Website

Welcome to our website.
Search FAQs
Custom SQL Queries of Vista used in Excel

Issue
Customer would like to import data directly into Excel, but have it appear like it does in Vista.

Product Line
ION Enterprise, SPM, PME

Environment
Importing data from the ION_Data database, but the data is displayed as it is stored in the tables of ION_Data.

Cause

Customer would like to import data directly into Excel, but have it appear like it does in Vista.

Resolution

The following is a complete walkthrough of how to set up a new Data Source, import it into Excel then edit it to display like Vista.

CREATE A NEW DATA SOURCE

1. Open Data Source (ODBC) from Administrative Tools of the Control Panel.
Add data source

2. Click Add… under the User DSN tab
Add data source

3. Scroll down to select SQL Server and click Finish
Select SQL

4. Enter a Name for the Data Source, a brief Description (optional) and either an IP address or server name in the Server field.
Create Data Source

5. Click Next at this screen.
NOTE: your IT personnel must set up the SQL server you are trying to connect to with your Network login ID and Password. You will need Read access.
Or Create Data sourceyou will need an SQL user and password with Read Access

6. Check the Change the default database to field and select ION_Data from the pulldown list.
Change default

7. Click Next and then Finish on the following two setup screens.

8. Click Test Data Source…
Test data source

9. Confirm that you get a TEST COMPLETED SUCCESFULLY! message. This verifies that your new Data Source was configured correctly.
Test Data Source

GET QUERY FROM VISTA

1. Open Vista.

2. Either create a new Data Log Viewer (DLV) and link it to a node(s) or find an existing one from a diagram (e.g. Voltage Log).

3. Right-click the DLV and click Edit Query to use the Query Wizard. The finished product of this DLV will be very similar to how the data is displayed in Excel.

4. Right-click the DLV and click Edit SQL to open the SQL commands in Notepad. This is ION SQL so type getnativesql in the very top line to get the true SQL statements. Save and close.
Notepad test script

5. Open the DLV. Highlight the entry (SELECT dbo….) and click Edit -> Copy.
Vista viewer

6. Go back to Edit SQL and remove the getnativesql command to return the DLV to its default state.

CONFIGURE EXCEL TO IMPORT DATA

1. Open a new Sheet in Excel

2. Click Data -> Import External Data -> New Database Query…

3. Select the new Data Source and click OK
Choose data source

4. Add any of the tables and columns (it doesn't matter which ones since we will write over the SQL queries) and click Next until the Query Wizard - Finish screen appears Finish wizard

5. Select View data or edit query in Microsoft Query and click Finish.

6. Click View -> SQL….
View SQL

7. Delete the existing SQL commands and paste in the SQL query from Vista.

8. Click File -> Save to save your new custom query. Future queries can easily be found under the Queries tab when importing new data.
Choose data source

9. Click File -> Return Data to Microsoft Office Excel.

10.  A message appears stating that database is being queried. This should only last a few seconds, but could be longer depending on query size and database communication.
Excel screen

NOTE: If using the DateAndFracAsUnix, timestamps will be in UNIX time. Replace this function with "imestampUTC" (without Quotes) to have the timestamps return as UTC time. Timestamps for meters are stored in the database using UTC time. Additional SQL commands will need to be added to the query to create column labels and timestamp conversion otherwise they will need to be entered manually.

Schneider Electric Philippines

Explore more
Range:
Articles that might be helpful Users group

Discuss this topic with experts

Visit our Community for first-hand insights from experts and peers on this topic and more.
Explore more
Range: