Course 9 / Lecture 3:

Collect sensor data into Microsoft Excel
(measure the carrots)

In this lecture you will learn how to collect information received from a sensor (in our case the ultrasonic distance sensor) and how to save this information into a spreadsheet. The spreadsheet can be opened by Microsoft Excel, WPS Office or any other spread sheet management software. You will also learn to build a carrot measuring device from Lego.

C9 L3 Figure 1
Figure 1 - You can store distance measurements with Ozeki 10

Requirements

Task #1:

Create 'TXT File' connection in Control Panel!

The first step of this lecture is to create the required connection that stores data coming from the ultrasonic sensor into a CSV file. You can do it with a text connection. Create it in Ozeki 10 by opening the Control Panel and clicking on Create new Connection. Select 'Application' from the connection methods and choose the 'TXT File' application icon (Figure 2).


Figure 2 - Select the 'TXT File' connection in Ozeki 10

After you click the 'TXT File' application, you will be able to see the connection details. Here you can type a name for the connection, but now you need to focus on the File Format. In this lecture the file format needs to be set to 'CSV' (Figure 3), so the data that you collect will be saved in that format. Finally you can change the inbox directory or you can accept the default directory.


Figure 3 - Select CSV file format and click 'OK'

Task #2:

Write code to collect distance information from ultrasonic sensor and store it in a CSV file by using the text connection!


Step 1: Start moving your robot towards an obstacle

After you set up a text connection, you need to write a SNAP script to collect data. So first drag a 'when green flag clicked' entry point as usual. Then for reading different distance values by using the ultrasonic sensor, you should start the motor (Figure 4). This can be done with a 'start motor [motors] at [value]%' block, where you use both A and B motors. Finally add 'repeat' where you can specify how many times the loop should be executed. In this lecture you will read data 10 times.


Figure 4 - Ready to collect ultrasonic data 10 times

Step 2: Collect data to 'TXT File' connection

You should collect data with a 'repeat' block to the connection, so please add the 'To [connection], message: [distance]' block (Figure 5), where you have to select the text connection that you have created and dragged the 'Distance in cm: [connection]' block to the message section and here you can select the ultrasonic sensor. Finally add a 'wait [number] secs' block and type 1. With that you can achieve that the program will collect data from the sensor once in every seconds until it reaches 10 measurements. Finally add a 'Stop motor [motors]' block to stop the robot.


Figure 5 - Sending data to the 'TXT File' connection

Task #3:

Open CSV spreadsheet to check the results


When the program is fully executed the results will be saved in a CSV file. You can find this file in the directory you specified when creating the connection (Task #1). The file is called 'inbox.csv' to store all messages. If you open it in a simple notepad, you can find all data in a text file separated by commas. But if you open it in Microsoft Excel or in WPS Office (Figure 6), the data will be split into columns separated by the comma character between each connection address and measurement.


Figure 6 - Open the spreadsheet to see results

Program code


Figure 7 - While moving the robot, this program saves 10 distance values

Periodically get distance data with an ultrasonic sensor
The code can collect and save data while the robot goes forward
You can collect sensor data measured by your robot:
Download Ozeki Snap code for collecting distance data

More information