Data Table Loader tool (load of Excel Files)

×

Warning message

You can't delete this newsletter because it has not been sent to all its subscribers.

Data Table Loader (DTL) is a wizard-based tool designed and developed aimed at ingesting data provided in an Excel file by offering a flexible environment to establish a direct connection between the file data and the Snap4City platform. Using DTL, it is possible to import (static) data, by uploading an Excel file and execute a set of operations to make the uploaded data compatible with the Snap4City platform and the KM4City multi-ontology. The data is uploaded as excel file in the tool, also asking to the providers to add some descriptors for each dataset (some of them connected to the KM4City classes), then is automatically aggregated to the other processed data in the platform in a semantic way.

NOTE: Please note that the publication of data via this tool is moderated and some time can passed before to have the loaded data available.

DTL Overview

In the following sections, the general features and the process that are needed to be completed to ingest data, using DTL , is briefly described.

File Upload

The first step of data ingestion in DTM is uploading the Excel file. DTM offers suitable flexibility by supporting multi-sheet excel files. Some guidelines are provided to the user to assist him on how to upload an Excel file properly. If the user does not upload an Excel file, following the provided guidelines, s/he needs to edit the file and re-upload the file again.  Also, a table which presents the information of uploaded files by the user is available and includes:

  • the number of files that has been already uploaded by the user
  • the maximum number of files to be uploaded by the user
  • uploaded file (for each file in chronological order):
    • File download (if the user role is "RootAdmin")
    • Organization associated with the file (if the user role is "RootAdmin")
    • filename
    • upload date and time
    • status: { Model: Created or Not Created, Device(s): Created or Not Created, Instance(s): Created or Not Created}
  • Possible actions for each file:
    • View the file data and metadata, by clicking on “VIEW DETAILS” button, including:
      • device names
      • instances
      • values
      • value names
      • value types
      • dateObserved
      • coordinates
      • nature and sub-nature (semantic descriptors)
      • context broker
    • Delete the file and the data created on Snap4City: (by clicking on “DELETE” button). This functionality is available only if the associated instances have not yet been created.

Inserting General Information

In this step, the user is asked to insert:

  • dateObserved: there are two cases:

 

  • File case: there is a dateObserved for the whole file, this means that each row of the file is not associated to a specific date, as happens in the following file: Example_dateObservedFile_NoCoord_Address.zip. In this case, the user must select a dateObserved from a date-time picker.

 

A set of guidelines is provided to the user directly on the tool for setting dateObserved in both File and Row cases.

  • Defining the ‘General Information’ for each file, includes:
    • Sheet name: A general name for the sheets of the file (e.g., year, age, city)
    • Context broker: which refers to the context broker of IoT devices to be created (selecting one from the predefined available list)
    • Nature and Sub-Nature: which refers to the nature and sub-nature of the IoT devices to be created. The IoT Devices are the type of data created on the Snap4City platform form the excel file uploaded. Nature and Sub-nature can be selected by the user from the available list (that is related with the KM4City multi-ontology, this choice will allow the semantic aggregation in the Snap4City Knowledge Base).

 

Inserting Coordinates

In this step, the user is asked to insert coordinates regarding the file. There are two cases:

  • Row case: there is a GPS location for each row, as in the following file: Example_dateObservedRow_CoordRow.zip. The user then must select columns that include latitudes and longitudes.

 

  • File case: there is a GPS location for the whole file, this means that there is not present a GPS location at row level: Example_dateObservedRow_CoordFile.zip. The user then must insert a latitude and longitude for the file (the same date will be associated to each row).

 

  • Address case: There is an address for each row in this case: Example_dateObservedFile_NoCoord_Address.zip. Therefore, the user must select a column that contains the addresses for each row. Also, to obtain the coordination of each device/instance, the user must specify a search circle by:
    • the latitude and longitude of center

the radius of the search circle A possible coordination is then returned by the tool which can be viewed in the preview table in the next step. It is noted that in the Address case, if there is no address provided for a row, the associated device/instance will not be produced (later by an IoT App). A set of guidelines is provided, to the user for setting coordination in both File, Address, and Row cases.

Selecting Value Types and Value Units

In this step, for each column header, the user is asked to insert a value type and an associated value name. If there is no proper value type or value name for a column, the user needs to contact snap4city platform (mail to: snap4city@disit.org).

Composing Value Name

In this step, the user is asked to compose a value name for the uploaded file. A value name will be used to identify an IoT device. Therefore, as suggested in the provided guidelines, a proper value name is composed of a set of column headers that could be used to identify the IoT device. By default, the file name and the sheet name, chosen in the previous step, is included in the composed value name. It is noted that, if there is a dateObserved for each row, this step is skipped. A set of guidelines is provided to the user for composing value name directly on the tool.

Preview

In this step, an overview of inserted data and other configured settings (e.g., device name(s), sheet name(s), value types, value names, data types, nature and sub-nature, context broker) is presented. The user can observe them and change the settings by coming back to the previous step(s). Otherwise, s/he can save the data of the uploaded file by clicking on Save button.

It is noted that, in the preview table, if an address is provided for each row, the calculated coordinate for each row can be edited manually by the user.

Result

When the data of the uploaded file is saved in our database, the result is presented to the user, together with a table including the ID of inserted data, the file name, and the username which uploaded the file.

Data Table Manager: updated dataset

Here after a table reporting the excel files uploaded on Snap4City. For each dataset are shown the following details:

  • Dataset (file) status: All = Model: Created + Device(s): Created + Instance(s): Created
  • #total Instances = Total number of Instances present on Snap4City = #IoTDevices*#Instances for device
  • #fields for each device = Number of fields for each device (that corresponds to the columns numbers of the excel file excuding latitude and longitude) = #total fields (not considering lat, long and measured time)
  • #total fields = #total Instances* (#fields for each device)

Organization

User

File name

Status

#IoTModels

#IoTDevices

#Instances for device

#total Instances

#fields for each device

#total fields

WestGreece

******

Rooms for rent 2017-2019.xlsx

All

1

20

3

60

6

360

WestGreece

******

Arrivals_Departures of Air Transport_Montly_2010-2019.xlsx

All

1

1

120

120

4

480

WestGreece

******

Arrivals_Departures of Air Transport_Annual_2010-2019.xlsx

All

1

1

10

10

4

40

WestGreece

******

Arrivals_nights_and_occupancy_in_hotel_2010-2018.xlsx

All

1

4

9

36

4

144

WestGreece

******

DOMESTIC MOVEMENTS 2013-2018.xlsx

All

1

6

6

36

6

216

WestGreece

******

WESTERN GREECE Hotel potential 2010-2019.xlsx

All

1

24

10

240

6

1440

WestGreece

******

REGION OF WESTERN GREECE Visitors to Museums Archaeological sites 2010-2018.xlsx

All

1

4

9

36

4

144

WestGreece

******

TRAFFICKERS ABROAD 2013-2018.xlsx

All

1

1

6

6

6

36

WestGreece

******

Cruise ship movement in the port of Patras.xlsx

All

1

1

7

7

5

35

WestGreece

******

Cruise ship movement in the port of Katakolo.xlsx

All

1

1

7

7

5

35

WestGreece

******

Basic Sizes of Incoming Tourism of the Region of Western Greece.xlsx

All

1

6

4

20

7

140

 

 

 

 

 

 

 

 

 

 

Mostar-BosniaHerzegovina

******

Annex II_Indicators_Mostar_HERITdata_quarterly.xlsx

All

1

1

9

9

8

72

Mostar-BosniaHerzegovina

******

Annex II_Indicators_Mostar_HERITdata_annual.xlsx

All

1

1

3

3

26

78

Mostar-BosniaHerzegovina

******

Annex I_Attractions.xlsx

All

1

30

1

30

7

210

Mostar-BosniaHerzegovina

******

Annex III_Tourist number in 2019.xlsx

All

1

65

1

65

5

325

Mostar-BosniaHerzegovina

******

Annex III_Number of tourists_monthly_2018_2019.xlsx

All

1

65

24

1560

5

7800

Mostar-BosniaHerzegovina

******

Annex IV_Monthly statistics.xlsx

All

1

1

24

24

5

120

 

Sintesi:

TOT westgreek

11

69

191

578

57

3070

TOT Mostar

6

163

62

1691

56

8605

TOTALE

17

232

253

2269

113

11675

 

Data Table Manager: not managed datasets

Organization

File name

Notes

WestGreece

GreeceHotels.xlsx

The dataset seems containing Point of interest (Hotels), but the location is missing (latitude and longitude)

WestGreece

Peripheral Unit of Ilia.xlsx

The dataset seems containing Point of interest (Hotels), but the location is missing (latitude and longitude)

 

Data Table Loader  IOT App configuration

To setup a Data Table Loader (or POI) IoT application for a user, associated with an organization (assuming that the IoT application code is available (for example, in the clipboard or in JSON format), the following steps are needed to be followed:

  • Login: into the user’s account on the Snap4City platform. In fact, only authorized users, associated with an organization, are capable of running an IoT application on Snap4City the platform;

  • Create: a new application, by inserting a name, when navigating to the IoT applications;

  • selecting the associated context broker by updating the service property of ‘fiware orion out api v2’

  • updating the context broker