CSV – Sapelli

CSV stands for “comma-separated values” and will display data within a table. CSV files can be opened with a spreadsheet program, such as Microsoft Excel or Google Sheets. Data within the CSV format will look similar to the example below. This example is one data record taken from the Transport Demo project:

StartTime	StartTime-LocalYYYYMMDD_HHMMSS	StartTime-UnixMS	DeviceID	TransportMode	TransportMode-Value	TransportMode-Image	Picture	Picture-Files	Position	Position.Latitude	Position.Longitude	Position.Altitude	Position.Accuracy	Position.TimeUTC	Position.Provider
2017-03-31T10:33:29.441Z	2017-03-31 10:33:29	1490956409441	1752196661	9	Train	train.svg	17118;	‘Picture_1752196661_2017-03-31T10.33.29_17118.jpg’:	true	51.5	-0.10999833333333334	5.0	20.0	2017-03-31T10:33:41.000Z	1
2017-03-31T10:56:07.572Z	2017-03-31 10:56:07	1490957767572	1752196661	1	Motorcycle	motorcycle.svg	22381;	‘Picture_1752196661_2017-03-31T10.56.07_22381.jpg’:	true	51.5	-0.10999833333333334	5.0	20.0	2017-03-31T10:56:36.000Z	1
2017-03-31T10:56:40.783Z	2017-03-31 10:56:40	1490957800783	1752196661	8	Subway	subway.svg	4757;	‘Picture_1752196661_2017-03-31T10.56.40_4757.jpg’:	true	51.532999999999994	-0.113	5.0	20.0	2017-03-31T10:56:50.000Z	1

Header

The first row of the file is the header and explains the columns of the table. At the end of the header, there is some information about the project:

schemaName: This displays the name and version of the Sapelli project as well as the Form ID(s) in the project. In this example, the project was called “Transport_Demo_(v2.3)” and the form was called “Survey“.
modelID: This displays the project’s Model ID. You can view the Model ID of a Sapelli project by clicking on the “Details” tab at the top of the home page screen. The Model ID is a combination of the ID of a project and its Finger print. In other words, any change to the project ID or any change in the PROJECT.xml file will automatically generate a new Model ID. This is important when comparing data to check that the exact same project was used across devices. For example, when sending data between phones the data will not merge if the model ID does not match between the phones.
modelSchemaNumber: This is an internal Sapelli number to identify the Form, that this record came from. This is used for Sapelli to import data from other devices.
exportedAt: This displays the time and date recorded when the data was exported to the phone, using the ISO 8601 standard of measuring time.

Rows

Following the header, each row is a record collected with Sapelli Collector. The columns might change depending on the project. For example for the Transport Demo project, we have the following columns:

Start Time

There are four “StartTime” columns. They essentially all provide the same information, but in different formats:

The first StartTime row records the time the user began collecting this record using the ISO 8601 standard of measuring time.
The second StartTime row records the time the user began collecting this record in local time using the following format: year/day/month and hour/minutes/seconds.
The third row records the time the user began collecting this record in UTC (coordinated local time). This is useful when collecting data from around the world, where local time may not be the best reflection of when a record was captured.
Finally, the fourth row records the time the user began collecting this record in unix milliseconds.

It is also possible to have an “EndTime” recorded. However, as the default value for the attribute ‘storeEndTime’ is ‘false’ you need to specifically declare it when writing the PROJECT.XML file (see the Form section of XML documentation for more information on the ‘storeEndTime’ attribute).

Device ID

DeviceID: This is the unique ID that is given to every device that has downloaded Sapelli. Therefore, even if data has been merged together from various devices, you can differentiate which data has come from a different device. To see your phone’s “DeviceID” follow these steps:

On the home page of the app click the ‘three dot’ menu icon in the top right hand corner. Next click the “About” option. Information in a box will appear on your screen and at the bottom you will see the unique Device ID of that phone.

Choice ID

The following four columns are named after the Choice ID field. In this example, in the Transport Demo PROJECT.xml, the first Choice had the id=”TrasportMode” (refer back to the decision tree for clarity). The choice or ‘leaf of the tree’ the user reaches is displayed in these columns. All four columns refer to the same choice, but display the information in different formats:

TransportMode: The number of the leaf (or choice) that the user clicked on within this choice set, in this case for the first row, they chose number “9”. The numbers start counting from zero, so number “9” would actually be the tenth choice within the choice field. Therefore, referring back to the PROJECT.xml file we can find the tenth ‘leaf’ of the decision tree to see what choice the user ended up at.
TransportMode-Value: As the number in the previous row is not very descriptive of the choice the user clicked on, this column provides the value of that choice. Therefore, in this case, we know the user clicked on the “Train” choice in the Transport Demo project.
TransportMode-Image: This column describes the image that represents that choice (the picture the user see’s on their screen when presented with the choice).
TransportMode-Caption: If there was text used to represent a choice then this column will display that text. In this case, the caption is empty as there was no text displayed in the project.

Picture

The next two columns are named after the Photo ID field, in the Transport Demo PROJECT.xml the Photo id=”Picture”. Both columns refer to the same photo, but display the information in a different format:

Picture: This row displays the unique ID of the photo(s) captured. Where more than one photo is captured, all the photo ID’s will be listed in this row separated by a semicolon.
Picture-Files: This row displays the full name of the photo(s) captured using the following format – “Photo field ID_DeviceID_date captured (using ISO 8601 standard)_Photo’s ID number.jpg”. This would also be the name of the photo file if it were to be downloaded. Where more than one photo is captured, all photo name’s will be listed in this row, separated by a semicolon.

Location

The next eight columns are named after the Location ID field, in the Transport Demo PROJECT.xml the Location id=”Position”. These columns refer to the location of the user’s device when the data was collected. Some of the columns won’t capture results unless they have been declared in the PROJECT.xml file. For example, you would need to specifically declare the speed, bearing and altitude attributes when writing the PROJECT.xml to see results in the data, as their default value is ‘false’. (See the Location attributes in the Field types section of the XML documentation).

Position.Latitude: Displays the latitude of the user’s device.
Position.Longitude: Displays the longitude of the user’s device.
Position.Altitude: Displays the altitude of the user’s device.
Position.Bearing: Displays the bearing of the user’s device.
Position.Speed: Displays the speed (in metres/second) at which the user’s device was moving whilst recording the location (for example, this may be useful if the user is travelling in a car whilst collecting data).
Position.Accuracy: Displays the accuracy (in metres) of the location recording.
Position.TimeUTC: Displays the time (using GPS time) at which the location was recorded.
Position.Provider: Refers to how the location data was provided. If the provider is unknown then the value displayed would = 0, where GPS is the provider the value displayed would = 1, and if the provider is the network the value displayed would = 2.