Page History

The following file formats are supported for manual import in BellaDati:

• CSV (plain text files)
• Microsoft Excel (XLS, XLSX) - Office 2003 - 2016 (previous versions not guaranteed)
• XML files
• ZIP files (containing one or more supported file formats above)
• JSON files

To import file:

• Go to the Data Set page
• Select Import data
• Choose Data file in Inport type
• Select appropriate Data file format

Importing from CSV

When you are importing from CSV, please continue directly to Import settings page.

Importing from Microsoft Excel

After uploading XLS/XLSX file you will be prompted to select the desired spreadsheet list.

Importing from XML

In the XML importing guide, you will be prompted to select the row tag, which represents repeatable data sentence. The following example illustrates it on XML file containing employees:

<employees>
	<employee>
		<name>Jan</name>
		<surname>Novak</surname>
	</employee>
	<employee>
		<name>Petr</name>
		<surname>Novak</surname>
	</employee>
        ...
</employees>

In this case, the row tag is <employee>.

1. Row tag: Select repeating tag in XML structure. Check extracted content in the preview on the left.
2. Optionally, you can add custom columns repeatedly: Select items and/or attributes when XML structure is not straightforward.

Importing from ZIP

Importing data compressed as ZIP archive represents an effective way how to reduce imported file size and also upload times significantly. It can contain the following file formats:

• Plain text (CSV)
• Microsoft Excel (XLS, XLSX)
• XML

Please follow corresponding chapters above to continue importing Microsoft Excel or XML file formats.

Importing From JSON

BellaDati also allows users to import data from JSON file. By using special transformation functions, it is possible to parse JSON arrays and objects.

In some cases, it might be necessary to change Array element to correctly parse the data into rows. This can be done in the import settings.

After choosing the Array element correctly, it is possible to use the function parseJSON() to load the JSON value into a variable and then access the values by using functions getString(), getInteger(), getDouble(), etc. to load the values. To access a nested object, users have to use function getJSONObject(). To access an array, function getJSONArray() is available.

Examples

Let's have a JSON file with following structure:

{
	"sales": [{
			"category": "Food",
			"summary": {
				"totalPrice": 136,
				"totalItems": 8,
				"cashierName": "John Doe",
				"items": [{
						"name": "Bread",
						"amount": 5,
						"price": 55
					},
					{
						"name": "Butter",
						"amount": 3,
						"price": 81
					}
				]
			}
		},
		{
			"category": "Drinks",
			"summary": {
				"totalPrice": 40,
				"totalItems": 5,
				"cashierName": "Jane Doe",
				"items": [{
						"name": "Water",
						"amount": 2,
						"price": 10
					},
					{
						"name": "Juice",
						"amount": 3,
						"price": 30
					}
				]
			}
		}
	]
}

To be able to get the values, users need to specify sales as Array element. 

To get the category, add a new column as an attribute and use following code:

JSONElement e = parseJSON(value(1))
return e.getString('category')

To get the total items, add a new column  as an indicator and use following code:

JSONElement e = parseJSON(value(1))
JSONElement f= e.getJSONObject('summary') as JSONObject; 
return f.getInteger('totalItems') 

To calculate the total price from all the item prices, use this formula:

JSONElement e = parseJSON(value(1))
JSONElement f= e.getJSONObject('summary') as JSONObject; 
total = 0;
f.getJSONArray('items').each()
	{
	JSONObject o = it as JSONObject; 
    g = o.getInteger('price') as Integer
      total = total + g
    }
	
return total

Let's have this JSON:

{"sensorId":"sensor01","values":["sensor01","2018/9/28 18:17","26.55","42"],"mqttTopic":"belladati","mqttMessageId":-1}

To access value from the JSONArray, use this formula:

JSONElement e = parseJSON(value(1))
JSONArray a = e.getJSONArray('values') as JSONArray
a.getString(2)

Let's have this JSON:

{
    "data":
    {
        "total":123,
        "data":
            [{
						"name": "Water",
						"amount": 2,
						"price": 10
					},
					{
						"name": "Juice",
						"amount": 3,
						"price": 30
					}
				]
        
    }
}

In this case, we need to set "data,data" as the Array element.

In order to get the value from the element name, we can use the following transformation script:

JSONElement e = parseJSON(value(1))
return e.getString('name')

Next Steps

• Import settings
• Adding custom columns in XML import