Volume 3, March 17, 2003
In this Tech Tip:
• How to paste a Cloudscape database table into a Microsoft Excel spreadsheet
• Holidays
How to paste a Cloudscape database table into a Microsoft Excel
spreadsheet
Product: Niagara
Revision: Release 2.301.330
Tip: There may be a time when you want to paste a Cloudscape database table into
a Microsoft Excel spreadsheet. You may want to do this so you can create a
chart in Excel or save the data for some other use. This could be another way
to view the data that is logged.
In Internet Explorer
1. If you want to get a current log, go to the log index for the Cloudscape database
(http://[IP]/log/index).
If you want to get an archived log, go archived log index for the Cloudscape database
(http://[IP]/archive/index).
If you want to run a SQL query on an archived log, go to the application database of the
Niagara station (http://[IP]/appdb/index). See Using SQL Queries for more information.
2. Select the log you want to view in Excel in the text/html format.
3. Copy the Address location (URL) of the log.
Get a Log Table into the Excel Spreadsheet
4. Start a new Excel spreadsheet document.
5. Select the A1 cell of the worksheet.
6. Select Data > Get External Data > New Web Query.
7. In the Enter the address for webpage… field, paste the copied address location from
Internet Explorer
8. Click OK.
9. On the Returning External Data dialog box, click OK
North America Europe Asia Pacific
3951 Westerre Pkwy, Suite 350 100 Lodge Lane 6 Temasek Blvd
Richmond, VA 23233-1313 Little Chalfont, Buckinghamshire HP8 4AH #16-03 Suntec Tower 4, 038986
USA UK Singapore
1.804.747.4771 Phone 44 (0) 1494.766377 Phone +65.6.887.5154 Phone
1.804.747.5204 Fax 44 (0) 1494.766311 Fax +65.6.887.5342 Fax
www.tridium.com
Tridium, revolutionary://software.solutions, Vykon and Niagara Framework are trademarks of Tridium, Inc. LonWorks is a registered trademark of the Echelon
Corporation; Modbus is a registered trademark of Gould Inc. © Copyright Tridium 2003
Have the Excel Spreadsheet Data Refresh
To refresh the data manually click the Refresh Data Button ; or to have data refreshing
done automatically, click the Data Range Button , select the Refresh Every box and
enter the update interval that you would like.
Have Excel Automatically Sign into a Niagara Station
At the beginning of the URL you pasted into the address location of the New Web Query, put
in your username and password before the address.
For example: http://[username]:[password]@[IP]/log/fetch?swid=/demoR2/log&content-
type=text/html
Have Excel Spreadsheet Display More than 2500 Records in a Query
At the end of the URL that you pasted into the address location of the New Web Query, type:
&limit=#### (where # represents an actual number). You can use the word none rather than
a number, but this will not put a limit on the number of records that are returned. Be very
careful when delineating the number of records to be returned, as timeouts can occur from
large logs or archives. For example:
http://[IP]/log/fetch?swid=/demoR2/log&content-type=text/html&limit=5000
Using SQL Queries
To query an archived log in Internet Explorer, go to the URL http://[IP]/appdb/queryForm.
From that URL, you can query archived logs available in http://[IP]/archive/index for the data
that you need. After you query a log, you can copy the resulting URL for use in an Excel
spreadsheet.
For example, the following query will sort the information in descending order:
SELECT * FROM {ARCHIVE NAME} ORDER BY 1 DESC
This selects all the records from the archive and displays them in descending order by column
one.
Holidays
North America Europe Asia Pacific
None in March None in March None in March
April 18th April 18th & 21st April 18th
North America Europe Asia Pacific
3951 Westerre Pkwy, Suite 350 100 Lodge Lane 6 Temasek Blvd
Richmond, VA 23233-1313 Little Chalfont, Buckinghamshire HP8 4AH #16-03 Suntec Tower 4, 038986
USA UK Singapore
1.804.747.4771 Phone 44 (0) 1494.766377 Phone +65.6.887.5154 Phone
1.804.747.5204 Fax 44 (0) 1494.766311 Fax +65.6.887.5342 Fax
www.tridium.com
Tridium, revolutionary://software.solutions, Vykon and Niagara Framework are trademarks of Tridium, Inc. LonWorks is a registered trademark of the Echelon
Corporation; Modbus is a registered trademark of Gould Inc. © Copyright Tridium 2003
