Starting from Meazurem client version 1.5, it’s possible to export online sensor data to a CSV file. Exporting sensor data to an external file extends your capabilities to use the data.
In this blog post, we use Google Sheets, free online spreadsheet software. It works inside a web browser and thus on all platforms. Google Sheets requires that you have an Google account and that you are signed in.
If you don’t want to use Google Sheets, you can apply most of these instructions on other software too. Alternatives to Google Sheets include Microsoft Excel, Apple Numbers and OpenOffice Calc, for example.
What is a CSV file?
A CSV (comma separated values) file is a text file that contains data in a table form, separated by commas and line breaks. It’s a simple format that can be used for storing and transferring data. The file has a standard format and all spreadsheet programs can open it.
Here’s an example of exported Meazurem sensor data:
Timestamp,Temperature,Humidity,Pressure 2020-05-26T00:00:00.000Z,8.32,75.5,102073 2020-05-26T00:01:00.000Z,8.31,75.5,102074
How to export sensor data with Meazurem
Exporting data is done per sensor.
To export sensor data with the Android app,
- tap the small arrow on the sensor to open the extended options
- tap the export icon to open the export view
To export sensor data with the iOS app,
- long press the sensor to open the actions
- select Export to open the export view
After you are in the Export view, pick the start and end times. The selected dates are inclusive. The exported data will contain samples from the beginning of the start date to the end of the end date. To make the export, tap Export.
Exporting huge amount of data from the database is time consuming. That’s why exporting your data will be processed in background. Once the job is one, a download link is sent to your email. It should not take over a minute to receive the link.
How to import a CSV file to spreadsheet software?
This section is quite specifically tailored for Google Sheets. If you use another tool, please refer to the documentation of that tool.
To open a CSV file with Google Sheets,
- Open a new browser tab and type sheets.new to the address bar. A new spreadsheet document should open.
- Navigate File -> Import -> Upload and either drag your file to the box or select the file from your computer. You should be presented with options similar to the following image:
- The default options are OK. Select Import data. You should be presented with a view similar to the following image:
At this point, you have imported the exported CSV file into a Google Sheets. Next, you will learn how to plot a chart.
How to visualize CSV file data as a curve?
Producing a simple chart is very easy. To make a chart,
- Select all cells. It’s easy with ctrl + a (or command + a on macOS).
- Navigate Insert -> Chart. A chart is created for you.
Charting the data right away is fast but it might not lead to appropriate result. Let’s edit the data to make the chart look better and also present the times correctly.
Modify the CSV data for the chart
1. Different sensor data range
For example, if one quantity has much different range of values, the chart is hard to visualize. In our sensor data example, the pressure has values much larger than temperature or humidity. This causes the scale of the chart to be improper. Because the chart scale is so large, you basically loose all the temperature and humidity variation information.
There are at least two ways to solve the situation.
- Scale the values to a different range. For example, pressure data presented in Pascals (for example, 100 000 Pa) can be scaled to kilopascals (100 kPa). Now the range of pressure values around 100 are visually acceptable in the same chart with temperature and humidity.
- Edit the chart data range and drop the pressure column completely. In this case you should have a graph something like:
2. Gaps between samples or uneven distribution of samples per time
The data might have gaps or the data samples are not evenly distributed. It’s impossible to point it from the image but our sample data has a gap.
To solve this problem,
- Add a new column after the Timestamp (between columns A and B). Let’s call it Time.
- Add the following formula to the B2 cell.
=DATEVALUE(MID(A2,1,10)) + TIMEVALUE(MID(A2,12,8))
- While keeping the B2 cell focused, navigate to Format -> Number -> Date time.
- While having the B2 cell focused, move the mouse cursor to the bottom right corner of the cell, over the square box. Double click to copy the cell values down.
Now you should have something like:
3. Timezone adjustment
Data exported from Meazurem has the timestamp in UTC (Universal Time, Coordinated) timezone. It’s the same as GMT (Greenwich Mean Time).
To make the time to match your local timezone, one more fix is needed. In this example, we use the timezone of Finland. It’s UTC+3 or GMT+3 during summer time.
- Edit the B2 cell, and add + (3/24) to the end of the line. Here the 3 presents the UTC offset, and 24 the hours per day. If your timezone offset is negative, use a negative number as the offset. The full formula is now
=DATEVALUE(MID(A2,1,10)) + TIMEVALUE(MID(A2,12,8)) + (3/24)
- Remove rest of the B column values down from the B2 cell.
- Copy the cell values down as before.
The data should look something like
Make a chart from the CSV data
Now we have modified the data so that we can plot a visually correct chart.
This time, don’t select all the cells. Select only the columns that you want in the chart. In our example, we will select columns B, C, D and E.
Run the chart tool again to get the final result. Now you have all the quantities presented in the same chart, illustrating the missing data samples too.
Our data sample had one hour gap in the data. This gap is illustrated in the image above with a green circle that is added manually afterwards. The CSV file was missing values for that time range but with our data preparation, we were able to make the chart to notice it. If you compare this image to the first one, you can see that the first simple chart missed the gap totally. This is because the chart didn’t understand that the column A contained time values.
Do even more with a CSV data
That’s it. Now you are able to make cool charts of your Meazurem online sensor data.
Which ever software you use, learn to use the tool and you can make awesome things with it!