Exoprise CloudReady® Help
Search:     Advanced search

Importing Sensor Data into Excel

Article ID: 97
Last updated: 03 Jun, 2016
With the CloudReady Sensor Data Export API, you can import individual sensor or sensor crowd data into an Excel spreadsheet, so you can build custom graphs, share data with non-CloudReady users, and embed data/graphs in documents.  This article provides an example of how to do this.  You should review the Sensor Data Export API help article before proceeding with this article.

Connecting Excel to the CloudReady API

To import CloudReady data into Excel, you will connect to the API as if you were importing a text file.  
Start by selecting the "From Text" item in the Get External Data section under the Data tab:
 
 
This will open up the Import Text File Dialog.  You need to construct a valid query string as specified by the Sensor Data API spec.  For example, to query data for thee sensors, the following query string would be used to request the last 7 days data for sensors with IDs 9007, 9008, and 9009 (the apikey is an example; you will need to obtain the specific key the is associated with your account on CloudReady account profile page):
 
https://secure.exoprise.com/api/v1/sensors/export_data.csv?apikey=914abf235f2c09bb794ef0e3b467e5f7&period=7d&ids=9007,9008,9008
 
Once you have the query string defined, paste it into the File name field of this dialog:
 
Excel Import Text Dialog
 
Click the Import button to bring up the Text Import Wizard.

Running the Text Import Wizard

The Text Import Wizard will allow you to specify which data series to import.  On the first page of the wizard, indicate that the data is delimited and contains headers:
 
Excel Text Import Wizard 1
 
Next, specify that the data is delimited by commas:
 
Excel Text Import Wizard 2
 
Finally, select the series you want imported, by selecting columns and either choosing a format or selecting "skip:"
 
Excel Text Import Wizard 3
 
Click "Finish" to complete the configuration of the data connection.

Configuring the Data Import Range

Once the connection has been configured, Excel will prompt you to configure how the data is to be placed in the workbook.  First you will indicate where the data should be placed:
 
Excel Import Data Dialog
 
Before exiting this dialog, click the Properties button to open the External Data Range Properties dialog.  In this dialog you can name the data range, specify when and how the data refresh takes place, and whether to overwrite or insert data.  For example:
 
Excel External Data Range Properties
 
Once this configuration is complete, the query will be run, and the data inserted into the workbook.  Ex.
 
CloudReady Sensor Data

Refreshing and Modifying the Data Query

Once the connection information has been saved it can then be accessed in the Connections section under the Data tab:
 
Excel Data Connections Menu
 
The connection will re-query when the "Refresh All"  menu item is clicked.  To access the specific query, click on the Connections menu item.
 
Excel Workbook Connections Dialog
 
To modify the query click the Properties button to bring up the Connection Properties dialog, then click on the Definition tab:  
 
Excel Connection Properties Dialog
 
Select the query URL contained in the Connection file field.  Then click on the Edit Query button to open the Import Text File dialog:
 
Excel Import Text File Dialog
 
Paste the URL into the File name field and make any needed modifications.  Click the Open button to commit the changes.  Excel will again launch the Import Wizard as shown above where you can again modify what data series are selected and how the data is imported into the workbook.

Sample Workbook

To see how to use this API in conjunction with Visual Basic for Applications (VBA) in Excel, download the sample workbook available here.

Also read
document Sensor Data Export API

Prev   Next
Sensor Data Export API     Sensor - Skype Sensors