Import Sensor Data Into Excel

With the Sensor Data Export API, you can import individual sensor data into an Excel spreadsheet, so you can build custom graphs, share data with non-Exoprise 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 Exoprise for CloudReady Synthetic Data

To import data into Excel, you will connect to the API as if you were importing a text file.

1. Open Microsoft Excel
2. Create a new Workbook
3. 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 the 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 associated with your account on Exoprise account profile page):

https://secure.exoprise.com/api/v1/sensors/export_data.csv?apikey=914abf235f2c09bb794ef0e3b467e5f7&period=7d&ids=9007,9008,9008

4. Once you have the query string defined, paste it into the File name field of the Import Text File Dialog.
5. 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.

1. On the first page of the wizard, indicate that the data is delimited and contains headers
2. Next, specify that the data is delimited by commas
3. Finally, select the series you want imported, by selecting columns and either choosing a format or selecting “skip:”
4. 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.

1. First you will indicate where the data should be placed
2. On the Import Data dialog, choose Properties… to open the External Data Range dialog
3. In this dialog, name the data range, specify when and how the data refresh takes place and whether to overwrite or insert data
4. Once the configuration is complete, the query will be run and the data inserted into the workbook

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:

The connection will re-query when the “Refresh All”  menu item is clicked.  To access the specific query, click on the Connections menu item:

1. To modify the query, click the Properties button to bring up the Connection Properties dialog, then click on the Definition tab.
2. Select the query URL contained in the Connection file field.  Then click on the Edit Query button to open the Import Text File dialog
3. Paste the URL into the File name field and make any needed modifications
4. 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.