Importing table data

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

Choose the file you want to import, either by uploading it from your computer or downloading it from the cloud.

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.

info

For data deletion, the file can be a .csv, an Excel or a Text file.

The details will be automatically filled in:

• the compression type (ZIP, GZIP or none): will be detected
• the format (CSV semi-colon separated, CSV comma separated, CSV tab separated, Excel or text file): CSV semi-colon separated by default
• the encoding (UTF-8, UTF-16 Little Endian, ISO-8859-1, CP1252): UTF-8 by default
• the 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.

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

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:

• Value Mapping: You will have to do a mapping between the value of your file and the expected Actito value for the attribute in question. This mapping could be compared to a simplified "search and replace" function in Excel. Once the attributes are mapped, click on "Save".
• Data Formatting
  • Date: possibility to change the date and time format
  • Phone: choose a default country code for phone numbers that do not have one, or select a column from your file that provides the country code values
  • ISO Language Code: if your file uses the ISO 639-1 format for language codes (e.g. "en"), no transformation is needed. Otherwise use this rule to specify the format used in your file so it can be converted automatically.
  • ISO Country Code: if your file uses the ISO 3166-1 alpha-2 format for country codes (e.g. "UK"), no transformation is needed. Otherwise, use this rule to specify the format used in your file so it can be converted automatically.
• Text Cleaning
  • Escape special characters: convert special characters (e.g. & < >) into their escape sequences
  • Unescape special characters: convert special characters (e.g. & < >) back to their escape sequences
  • Remove accents (diacritics): remove accents and other diacritical marks from characters (e.g. "résumé" > "resume")
  • Remove characters
  • Replace text
• Text Formatting
  • Convert to lowercase: convert all letters to lowercase (e.g. "HELLO WORLD" > "hello world")
  • Convert to proper case: capitalize the first letter of each word (e.g. "john doe" > "John Doe")
  • Convert to uppercase: convert all letters to uppercase (e.g. "welcome" > "WELCOME")
  • Capitalize first letter: capitalize only the first letter of the entire text (e.g. "hello world" > "Hello world")
• Text Cropping
  • Remove leading and trailing whitespaces
  • Keep first N characters: keep only the first n characters from the start
  • Keep last N characters: keep only the last n characters from the end
  • Delete first N characters: remove the first n characters from the start
  • Delete last N characters: remove the last n characters from the end

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.

You can also visualize the transformation rules and advanced loading options that are used.

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 configuration)
• 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 configuration, choose "Create a import" to access the configuration 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.

From this notification, you can access the report and view the execution details of your import.

You can also view the import results and analyse the possible error files, from the Finished execution section in the "Data imports" section.