Importing Data via the Interface

After creating your custom table, you can import data in order to populate it.

To do so via the user interface, go to the 'Imports' application (Data > Imports) and click on 'Create an import'.

Note that you can also access it via a shortcut in the Profiles module via the "Plus" button (Profiles > Profiles of X table).

Good to know

Importing data is also possible with Custom Tables APIs.

Choose the type of custom table in which you want to import data.

Next, select if you want to start your import from blank or use a saved configuration with predefined settings.

tip

Click on the small info icon to view the details (file type, mapping, loading parameters,...) of the saved config.

You'll then be redirected to the import wizard.

tip

It is also possible to start an import from the 'Tables data' app by selecting a table then 'Import data' from the 'More' button.

Creating an import

General data

First, give a name to the import. It will help you find the import execution in the 'In progress' and 'Finished' tabs. This name must therefore be unique.

Then select the entity of the custom table in which you will import data. This will also be the entity of the import.

Finally, select the table in which you want to import data. Only tables stores on the previously selected entity are displayed in the dropdown.

Loading parameters

You will consequently need to specify the import parameters, which means defining the behavior of existing records.

• Update/Creation: All lines are taken into account. If an existing row is found, it is updated. If no corresponding row is found, a new row is created.
• Creation only: Only lines leading to the creation of a new line are taken into account. You will therefore only add new records to the database.
• Update only: Only lines that lead to a row update are taken into account. This mode can be used for tables for which a data update is relevant.
• Delete only: Only lines that lead to a row deletion are taken into account

File upload

You will have to upload an import file containing the data that you want to push in your table.

The file can be a .csv or an Excel file of maximum 100 MB. It can be uploaded from your computer or from a cloud location configured in your license, namely the transferbox or a FTP. Various separators and formats are available, but must be specified.

• Compression: ZIP, GZIP or None

tip

Compressing the file is not mandatory but can hold more data. Please try to zip your file if your CSV is bigger than the limit of 100MB

• Format: Select if your are import an Excel or a CSV file with semi-colon, comma or tab separator
• Encoding: The supported encodings are UTF-8, UTF 16 Little Endian, ISO-8859-1 and CP1252
• File with header: This button (which is toggled by default) specifies if the fist line of the file is the header of the columns. A file with headers makes the data mapping easier. If it doesn't contain nay, the next step will follow the order of the columns.

File format - specifications

Your file must follow specific constraints in order to be accepted.

• The size of the file must be maximum 100 MB (if the file is zipped, only the size of the ZIP counts).
• The order of the columns is not important.
• Mandatory columns must be found in the file. Optional ones can be omitted.
• Each column must be unique.

tip

Click on the 'Download an example' to download an illustration file containing the structure of your table and the expected value type for each column.

You will obtain a practical example of the format expected for this file.

Good to know

Columns with the technical attributes "creationMoment", "updateMoment" and "id" are ignored during the import.

This means that if you need to carry out a migration of data from one table to another, you can export table data and import them afterward without having to modify the file.

Attributes mapping

This step allows you to establish the correspondence between the columns of your file (on the left) and the attributes of the table (on the right).

tip

If your file contains headers, Actito's artificial intelligence will automatically map the column to the corresponding field.

The AIO logo indicates automatically mapped fields. These remain editable manually.

• If the header of a column does not correspond to an attribute in the table, you will have to perform the mapping manually by selecting the corresponding field from the drop-down menu.
  A sample of values ​​is displayed below the column header.
• If your file does not contain any header, you will need to perform the mapping based on the order of the columns and the sample of values ​​serving as an example.
• If a column in your file should not be imported, keep the "Ignore column" option.
  The top right box shows all mandatory (and unique) fields that have not yet been mapped.
  All mandatory attributes must be mapped to allow new rows to be created, while keys identify existing rows in "Update/Creation" and "Update Only" modes.

Data preparation

During this step you have several options:

• Transform: allows you to apply transformation rules to the values in the file in order to clean or reformat them so that they match the expected values in Actito.
• Explore advanced options: allows you to define the loading method for each field.
• Simulate: allows you to simulate the file import and identify potential errors.

Transform

This screen allows you to:

• View the columns of the file and the mapping with Actito attributes carried out in the previous step.
• View a few values from the file for each field.
• Define transformation rules to be applied to your fields if necessary.
• Preview the result of these transformations applied to the values already displayed from the file.
• Ignore invalid values:
• If the box is unchecked, then in the case of an invalid value for this field the entire line in the file will be completely rejected, even if the other fields contain valid values.
• If the box is checked, an invalid value will simply be ignored and left empty in Actito, while the rest of the line will be imported. Note that in the case of a mandatory field, the rest of the line will still be rejected.

The transformation rules available depend on the field format. For all fields:

• Possibility to define a mapping between the values in the file and those to be inserted into Actito. For fields of type String (text):
• Text cleaning
  • Escape special characters
  • Unescape special characters
  • Remove accents (for example converts “é” to “e”)
  • Remove characters
  • Replace text
• Text formatting
  • Convert to lowercase
  • Convert to capitalized initials
  • Convert to uppercase for all characters
  • Capitalize the first letter only
• Text trimming
  • Remove spaces at the beginning and end of the string
  • Keep the first N characters
  • Keep the last N characters
  • Remove the first N characters
  • Remove the last N characters

For fields of type Date or Timestamp:

• Date format formatting (and optionally time for a timestamp)

Explore advanced options

Here you can define two main behaviors:

• Handling empty values: the checkbox here defines how potential empty values in the file are handled.
  • If checked, an empty value in the file will be imported and may overwrite a non-empty value previously present in Actito.
  • If unchecked, an empty value in the file will be ignored to avoid overwriting the value already existing in Actito.
• Processing values already in the database: defines how the import behaves if values already exist in the database.
  • Replacing values with those in the file will overwrite the existing values in the database.
  • Keeping existing values will only allow adding values in place of currently empty values, but not updating an existing value. You can choose the desired behavior field by field or apply the same rule to all fields via the first line.

Simulate

This option allows you to run a simulation of the file loading (without actually importing the data into the concerned table) and to identify potential issues in advance.

All lines of the file are displayed, with their status. If there is no warning on the field then no error has been identified, and it is also possible to filter via the “Show warnings only” button to display only problematic fields.

You have access to all the features of the “Transform” option described earlier, including the use of transformation rules and the choice to ignore invalid values if needed.

You can relaunch a simulation at any time that will take into account any adapted transformation rules, and/or download the corrected file.

If a simulation is relaunched, the screen will update with the result of this new simulation, with the possibility to define a new round of transformations. This makes it possible to apply several transformation rules in succession to the same field if necessary to ensure it is in the correct format in Actito.

The advanced options are also available, with the same possibilities as explained previously.

Summary

This final step gives you a summary of the previous steps. You can therefore check where the data will be loaded, in which import mode, the file settings, as well as the field mapping with the Actito attributes.

If transformation rules and advanced loading options are used, the interface indicates this with a green check mark.

caution

Transformation rules are not saved in this configuration.

Saving an import configuration

If you want to reuse your import settings for future manual imports, you can also save an import configuration before you launch.

An import configuration keeps in memory all the import details:

• the import name (an incremental suffix will be added to it when you create new imports based on the config)
• the destination table and entity
• the file type and compression
• the attribute mapping
• the loading parameters

To create one, click on "Save as configuration".

You then need to give a name to the configuration, so you can find it when you select a table type at step 0.

info

To delete a saved import config, choose "Create a import" to access the config selection screen, and enter "Edit mode" in the top right corner.

Checking your import results

After launching your import, it will appear in the 'In progress' tab until it is completed. This can take between a few seconds to a few minutes, depending on the volume of imported data.

To check the import results and analyse the possible error files, please see the Finished execution section in the Data imports section.