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.


2. Click Add… under the User DSN tab


3. Scroll down to select SQL Server and click Finish


4. Enter a Name for the Data Source, a brief Description (optional) and either an IP address or server name in the Server field.
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.

6. Check the Change the default database to field and select ION_Data from the pulldown list.
7. Click Next and then Finish on the following two setup screens.

8. Click Test Data Source…


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

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.
5. Open the DLV. Highlight the entry (SELECT dbo….) and click Edit -> Copy.


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
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

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

6. Click 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.


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.
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.