Import engine allows administrators to collect data from various data sources, and then map this data into relevant tables.

The SimpleOne import engine supports:

  • different data source types.
  • different file formats (if you choose "File" as the data source).

You can choose the data source, place the data from it to the temporary table (also can be called "an import set"), and after that, with the transform maps and the field maps, you can map the data from the source to the target table.

Role required: admin.

You can find a graphical procedure view of the import process in the diagram below. A more detailed description is provided further in this article.

    

    

Key concepts

ConceptDescription
Import sourceA record that specifies what data an import set should import. 
Import set

A record that contains references to raw data uploaded to the staging location. The record also returns information about this import attempt state.

Import set table

An automatically generated table that is used as temporary storage for imported records before transformation. The structure of the table is automatically generated depending on the imported data.

Transform

The process of converting data according to the transform map and transform scripts that define the source table (the import set) and the target table.

Transform Map

In this set of field maps, a record that specifies correlations between fields in an import set table and fields in a target table.

Transform ScriptA script that allows customizing import operations using native JavaScript extended with SimpleOne Server-Side API.
Field MapA record that specifies the relationship between a field in an import set table and a field in a target table.
Coalesce

An option related to the transformation process. This option allows updating existing table records within the transformation process.

For more information, navigate to the relevant article part: Coalesce.

Import Sources


Creating an import source is necessary to load raw data into the system for further processing and transformation. You can set up an import source with different data source types and file formats.

  1. Navigate to Import → Import Sources.
  2. Click New and fill in the fields.
  3. If the File type is chosen, click on the attachment icon   and attach the file you need.

  4. Click Save or Save and Exit to apply changes.

Import Source form fields

FieldMandatoryDescription
NameYSpecify the import source name.
Import Set Table NameY

Specify the import set table name.

The maximum length of the table name is 63 symbols.

Type

N

Choose the type of import source. Available choice options:

File Retrieval MethodN

Choose the method to add the file to the import source. Available choice options:

  • Attachment.

This field will appear if the File type is chosen.

FormatY

Select the file format and attach it to the form. Available file format options:

  • JSON
  • XML
  • Excel (only when the File type is chosen)

This field will appear if the File or Text type is chosen.

Import TextN

Specify the text you need to import. This field appears if the Text type is chosen. 

Text format
[
  {
    "property": "value"
  },
  {
    "property": "value"
  }
]
LDAP DefinitionN

Click on the magnifier icon to choose the protocol you need. This field appears if the LDAP type is chosen. 

For more information about LDAP import source, refer to the Importing using LDAP article.

After saving, the following UI actions appear on the import source form. They are responsible for further import processing.

UI ActionDescription
Test load (20 records)

After clicking, a pilot import set is created with a 20-records capacity, intended for importing data structure analysis.

Do not use the test set for further transformation.

Load all records

This UI action creates a new import set record by loading full data from the import source there.

This import set can be used for further transformation.

After loading data into the import source, saving the record and calling one of the data loading actions, a new record appears in the Import Sets related list. 

Using JSON format


You can use JSON files containing data formatted in JSON as an import source. In this case, make sure that the files follow the criteria below:

  1. JSON files should be valid. Use the RFC 8259 document as a guideline.
  2. Data types available to use in JSON structure are listed below. Dot-walking is not supported.
JSON Object
  {
    "top500": 1,
    "name": "More",
    "sector": "Oil and gas"
  }

Result: one element. The table has top500, name, sector fields and contains one record.

Array of objects
[ 
 {
    "top500": 1,
    "name": "More",
    "sector": "Oil and gas"
  },
  {
    "top500": 2,
    "name": "Nevermore",
    "sector": "Oil and gas"
  }
]

Result: an import set with the related table is created. The table has top500, name, sector fields and contains two records.

Object with array of objects
// Path for Each Row == exportField
{
   "exportField":[
      {
         "top500":1,
         "name":"More",
         "sector":"Oil and gas"
      },
      {
         "top500":2,
         "name":"Nevermore",
         "sector":"Oil and gas"
      },
	  {
		"top500": 4,
		"name": "Givememore",
		"sector": "Oil and gas",
		"more_info": "https://instance.example.com/company/34"
	  }
   ]
}

Result: an import set with the related table is created. The table has top500, name, sector fields and contains three records.

Table and column name transformation


When converting data for import, some changes occur to the system names of columns and tables that appear in the Import Set Table Name field of the Import Sources (sys_import) table. Namely, the names are transliterated and converted according to the following rules:

  1. The system changes all Cyrillic letters to the Latin ones according to their ordinal number in the list of the Latin symbols. See below how the transliteration is done:
    Cyrillic symbols [
            'а', 'б', 'в', 'г', 'д', 'е', 'ё', 'ж', 'з', 'и', 'й', 'к', 'л', 'м', 'н', 'о', 'п',
            'р', 'с', 'т', 'у', 'ф', 'х', 'ц', 'ч', 'ш', 'щ', 'ъ', 'ы', 'ь', 'э', 'ю', 'я',
            'А', 'Б', 'В', 'Г', 'Д', 'Е', 'Ё', 'Ж', 'З', 'И', 'Й', 'К', 'Л', 'М', 'Н', 'О', 'П',
            'Р', 'С', 'Т', 'У', 'Ф', 'Х', 'Ц', 'Ч', 'Ш', 'Щ', 'Ъ', 'Ы', 'Ь', 'Э', 'Ю', 'Я'
        ];
    Latin symbols [
            'a', 'b', 'v', 'g', 'd', 'e', 'io', 'zh', 'z', 'i', 'j', 'k', 'l', 'm', 'n', 'o', 'p',
            'r', 's', 't', 'u', 'f', 'h', 'ts', 'ch', 'sh', 'shch', '\'', 'y', '\'', 'e', 'yu', 'ya',
            'A', 'B', 'V', 'G', 'D', 'E', 'Io', 'Zh', 'Z', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P',
            'R', 'S', 'T', 'U', 'F', 'h', 'Ts', 'Ch', 'Sh', 'Shch', '\'', 'Y', '\'', 'e', 'Yu', 'Ya'
    ];

  2. All symbols that are relevant to the regular expression /[^A-Za-z0-9_]+/u are substituted with an underscore _  symbol. That is all symbols except capital and lowercase letters, digits, and the underscore are substituted with _ .
  3. A system column name is split into parts by an underscore _. Then, the parts are connected with an underscore _ . For example, the name _DOC_____1 is transformed to doc_1 as a result .
  4. The current system name of the column is transformed to lowercase.


    If a column name is empty, then, when converting, the system sets the following name imp_invalid_column + column number. The column number is determined by its order in the table. The numbering starts from 0. For example, imp_invalid_column0.

  5. The imp prefix is added to the name, for example, imp_client_list.


Import Sets


Generally, an import set should be created automatically based on the raw data uploaded into the import source. Import sets are intended to be a staging area for records imported from the data sources. 

Import Set form fields

FieldMandatoryDescription
NumberY

Import set unique number. The field is populated automatically.

Import SourceY

The field contains related import source name. The field is populated automatically.

Import Set TableY

The field contains the related import set table name. The field is populated automatically. 

System names of table fields begin with imp_ prefix

StateY

Import set loading state. The field is populated automatically and is always read-only.

Possible values are:

  • Loading – data loading into the import set is in progress.
  • Loaded – data loading into the import set has been finished.
  • Processed – the import set transform has been processed.
  • Cancelled – data loading has been cancelled.
Short DescriptionN

The field describes the structure of the table created and is populated with the data automatically.

Field value example:

Table structure: (top500 | name | sector)

Import Set records are in the parent-child relationship with the Import Set Rows table.

Transform Maps


Transform map is a set of field maps. In this set of field maps, correlations between fields of an import set table and fields of a target table are specified.

Each import operation requires at least one transform map which specifies the relationship between the import set table and the target table.

After creating the first import set, create a related transform map. To do this, complete the steps below:

  1. Click View Transform Map on the import source form you are configuring.
  2. In the appeared toast message, follow the link Create Transform Map.
  3. Fill in the fields in the appeared form.
  4. Click Save or Save and Exit to apply changes.

A transform map can also be created "from scratch". To do this, complete the steps below:

  1. Navigate to Import → Transform Maps to open the transform maps list.
  2. Click New and fill in the fields.
  3. Click Save or Save and Exit to apply changes.

Transform Map form fields

FieldMandatoryDescription
NameYDisplayed transform map name.
ActiveNSelect this checkbox to make the transform map available to use.
Source TableYSelect the table containing the import set data.
Target TableYSelect the table where you need to place imported data.

Silent Load

N

Select the checkbox to ignore business rulesnotification rules and other server-side engines which can be triggered by insert or update actions. In particular, workflows related to this record will not start, the field changes history will not be recorded and will not be displayed in the Activity Feed, respectively.

When updating records automatically with scripts, you may also need to use the Silent Load feature. In this case, use the silentMode method.

You can find engine and business rule execution orders in the Execution order article.

Ignore Mandatory Fields

NSelect the checkbox to ignore mandatory fields in the target table.
Use ScriptN

Select the checkbox to display the Script field, where you can define the transform script.

If you unselect the checkbox after inserting a script, the script will not be applied within the transformation.

ScriptN

Insert a transform map script you want to use to transform field values from the source table to the target table.

Use the runTransformRow() function with the following parameters available:

  • source – the Import Set Row record that will be transformed. Call a specific field from the record just like a regular SimpleRecord object. For example, source.imp_name
  • map – the Transform Map record that is currently being used for the transformation process. 
  • log – the object that contains info, warn, error methods. The methods are intended to log the transformation process. 
  • target – the record in the target table that will be inserted or updated within the transformation of the source record. Call a specific field from the record just like a regular SimpleRecord object. For example, target.name = source.imp_name.
  • isUpdate boolean that checks whether the target record is updated.

This script can be used to define field relationships before transformation. You can also configure relationships by creating related records via the Field Map tab of the specified transform map.

If you are configuring field mapping with the script, then it is recommended to create one more allocated field map record to define the Coalesce settings for this transformation session.

Transform Scripts


Transform script allows you to customize import operations using JS-scripts with SimpleOne Server-Side API

Transform Script form fields

FieldMandatoryDescription
Transform MapYSpecify a transform map to which this script is related.
ActiveNSelect the checkbox to make the script active or inactive.
WhenY

Specify the script type to occur:

  • onStart – the script runs at the start of the transformation before any Import Set Row record is read.
  • onBefore – the script runs at the start of the Import Ser Row record transformation into the target table.
  • onAfter – the script runs after each Import Ser Row record has been transformed into the target table.
  • onComplete – the script runs when all Import Set Row records are transformed.
OrderN

The field specifies the order in which the transform script will be executed. Fill in this field with an integer number. 

The Order field is temporarily not working correctly – our team is working on its logic improvement to make it more efficient. We will inform you about the changes in one of our next releases.


ScriptN

Enter the script using SimpleOne Server-Side API

Use the runTransformScript() function with the following parameters available:

  • source – the Import Set Row record that will be transformed. Call a specific field from the record just like a regular SimpleRecord object. For example, source.imp_name.
  • map – the Transform Map record that is currently being used for the transformation process. 
  • log – the object that contains info, warn, error methods. The methods are intended to log the transformation process. 
  • target – the record in the target table that will be inserted or updated within the transformation of the source record. Call a specific field from the record just like a regular SimpleRecord object. For example, target.name = source.imp_name.
    When specifying the object of the target parameter, take into account the type of the field in the target table. An example of filling fields in onBefore script:
Filling fields in onBefore script
(function runTransformScript(source, map, log, target /*undefined onStart*/) {
  target.email = source.imp_mail; // string
  target.username = source.imp_samaccountname; // string
  
  const parsedPhoneDigits = source.imp_mobile.match(/\d/g);
  target.phone = parsedPhoneDigits ? parsedPhoneDigits.join('') : null;  // phone

  const company = new SimpleRecord('org_company');
  company.addQuery('name', 'like', source.imp_company);
  company.selectAttributes('sys_id');
  company.setLimit(1);
  company.query();
  target.company = company.next() ? company.sys_id : null;  // reference
})(source, map, log, target);

  • ignore – boolean that allows skipping the Import Set Row record.
  • status_message – the message that describes the process of record transformation.
Import skip in onBefore script
(function runTransformScript(source, map, log, target /*undefined onStart*/) {
  const duplicate = new SimpleRecord('task');
  duplicate.addEncodedQuery(`number=${source.imp_number}^sys_id!=${target.sys_id}`);
  duplicate.selectAttributes('sys_id');
  duplicate.setLimit(1);
  duplicate.query();
  if (duplicate.next()) {
    ignore = true; // skip Import Set Row
    status_message = 'The "Number" [number] field should be an unique.'; // write message to Import Set Row -> Description
  }
})(source, map, log, target);

Field Maps


Field maps are used to define the correlation between fields of the import set table and fields of the target table. For this, you need to determine the values from the source table that will be added to the target table.

During the import process, you may need to update the existing records in the target table. To set up the key relationships between the Source Table and the Target Table, create at least one field map record, with the enabled Coalesce attribute. This attribute should be enabled for a field map that contains relationships of columns with unique values. It can be an email for a user, or a serial number for a CI.

In order to achieve stricter mapping, you can enable the Coalesce option for more than one Field Map record.

To create a field map, complete the steps below:

  1. Navigate to Import → Transform Maps.
  2. Open the transform map you want to configure.
  3. Click Create field map.
    • The Field Mapping widget appears. See the screenshot in the table below.
  4. Configure field mapping within the current transformation process.
  5. Click Set to save changes or Cancel to discard changes.

Here is another way to create field maps:

  • Navigate to Import → Transform Maps.
  • Open the transform map you want to configure.
  • In the Field Maps related list, click New and fill in the fields.
  • Click Save or Save and Exit to apply changes.
  • Repeat the previous steps for each field map that needs to be set.

Field Map form fields 

FieldMandatoryDescription
Transform MapYDisplays the transform map using this field map. The field is populated automatically.
Source FieldYSelect the field of the source table that should be transformed.
Target FieldYSelect the field of the target table that should store the values from the source table.

Coalesce

N

If this option is active within the transformation process, the engine checks whether values in the Target Table are equal to values in the Import Set table. 

If values are equal, the record in the Target Table will be updated; otherwise, a new record will be created.

This option can be set up within the widget. After clicking Create Field Maps on the transform map form, a widget containing either Add coalesce or Drop coalesce buttons appears.

Use ScriptN

Select the checkbox to define the transform script in the Script field.

ScriptY

Insert a script to determine a function that takes the record object of the Import Set table as an argument and returns the value to put into the Target Field.

Enter the script using SimpleOne Server-Side API

Use the transformEntry() function with the following parameters available:

  • source – the Import Set Row record that will be transformed. Call a specific field from the record just like a regular SimpleRecord object. For example, source.imp_name.
  • target   the record in the target table that will be inserted or updated within the transformation of the source record. Call a specific field from the record just like a regular SimpleRecord object. For example, target.name = source.imp_name.

The field appears when the Use Script checkbox is selected.

Do not unselect the Use Script checkbox after inserting a script; otherwise, it will not be applied.

Running a transform


  1. Navigate to Import → Import Sets.
  2. Open an import set you want to transform. Make sure that the state is Loaded.
  3. Click Transform.

As a result, the toast message Import is completed appears. Imported data will be transferred into the target table.

Transform sequence


The scheme below shows the sequence of events triggered by the transformation: 

  1. The sequence begins with the onStart transform script. It will be executed at the start of the transformation before any source row is read.
  2. The field map script takes the record object of the Import Set Row table as an argument and returns the value to put into the Target Field.
  3. The transform map script transforms field values from the source row to the target row.
  4. The onBefore transform script is executed before the source row is transformed into the target row. 
  5. Between the execution of onBefore and onAfter scripts, the target record is inserted or updated.
  6. The onAfter transform script runs when the source row has been transformed into the target row.
  7. The onComplete transform script is executed when all source rows are transformed.

The onBefore and onAfter scripts are repeatable, they will run until each record is transformed. 

  • No labels