The 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 select File as the data source).
Select the data source, place the data from it to the temporary table (an Import Set), and map the data from the source to the target table with the transform map field maps.
Role required: admin.
You can find a diagram illustrating the import process below.
Key concepts
Concept | Description |
---|---|
Import source | A record that specifies what data an import set should import. |
Import set | A record that contains references to raw data uploaded to the temporary table. The record also returns information about the import process state. |
Import set table | An automatically generated table that is used as the temporary storage for the imported records before the transformation. The structure of the table is automatically generated depending on the imported data. |
Transform | The process of data conversion according to the transform map and transform scripts that define the source table (the import set) and the target table. |
Transform map | A set of field maps that defines correlations between the fields in an import set table and the fields in a target table. |
Transform script | A script that allows for customizing import operations using native JavaScript extended with SimpleOne Server-Side API. |
Field map | A record that specifies the relationship between a field in an import set table and a field in a target table. |
Coalesce | This option allows you to update existing table records within the transformation process. For more information, navigate to the relevant article part: Coalesce. |
Import sources
Create an import source to load raw data into the system for further processing and transformation. Set up an import source with different data source types and file formats.
- Navigate to Import → Import Sources.
- Click New and fill in the fields.
If the File type is selected, click the attachment icon and attach the file you need. You can attach JSON, XML, or Excel files.
- Click Save or Save and exit to apply the changes.
Import Source form fields
Field | Mandatory | Description |
---|---|---|
Name | Y | Specify the import source name. |
Import set table name | Y | Specify the import set table name. |
Type | N | Choose the type of import source. Available options:
|
File tetrieval method | N | Select the method to add the file to the import source. Available options:
This field appears if the File type is selected. |
Format | Y | Select the file format and attach it to the form. Available file format options:
This field appears if the File or Text type is selected. |
Import text | N | Specify the text you need to import. This field appears if the Text type is selected.
Text format Expand source
[ { "property": "value" }, { "property": "value" } ] |
LDAP definition | N | Click the magnifier icon to select the protocol you need. This field appears if the LDAP type is selected. See the LDAP Import Source article to learn more. |
After saving, the following UI-actions appear on the import source form. Use them for further import processing.
Action | Description |
---|---|
Test load (20 records) | Click to create a pilot import set with 20 records to analyze the importing data structure. Do not use the test set for further transformation. |
Load all records | Click to create an import set record by loading full data from the import source. 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 files containing data formatted in JSON as an import source. In this case, make sure that the files follow the criteria below:
- JSON files should be valid. Use the RFC 8259 document as the guideline.
- Data types available to use in JSON structure are listed below. Dot-walking is not supported.
{ "top500": 1, "name": "More", "sector": "Oil and gas" }
Result: one element. The table has top500, name, sector fields and contains one record.
[ { "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.
// 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, the system names of columns and tables that appear in the Import set table name field of the Import Sources (sys_import) table are transliterated and converted according to the following rules:
- 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 Latin а А a A б Б b B в В v V г Г g G д Д d D е Е e E ё Ё io Io ж Ж zh Zh з З z Z и И i I й Й j J к К k K л Л l L м М m M н Н n N о О o O п П p P р Р r R с С s S т Т t T у У u U ф Ф f F х Х h h ц Ц ts Ts ч Ч ch Ch ш Ш sh Sh щ Щ shch Shch ъ Ъ \ \ ы Ы y Y ь Ь \ \ э Э e e ю Ю yu Yu я Я ya Ya - All symbols that are not 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 _ . - 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.
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
.The imp prefix is added to the name, for example,
imp_client_list
.
Import sets
An import set is created automatically based on the raw data uploaded from the import source. An import set is a temporary storage for records imported from the data source.
Import Set form fields
Field | Description |
---|---|
Number | The field contains the import set unique number and is populated automatically. |
Import source | The field contains the related import source name and is populated automatically. |
Import set table | The field contains the related import set table name and is populated automatically. System names of table fields begin with the imp_ prefix. |
State | Import set loading state. The field is populated automatically and is always read-only. Possible values:
|
Short description | The field describes the structure of the table created and is populated automatically. Field value example: Table structure: (top500 | name | sector) |
Import Set records are in the parent-child relationship with the Import Set Rows table records.
Transform maps
A transform map is a set of field maps. In this set of field maps, correlations between the fields of the import set table and the fields of the target table are specified.
Each import operation requires at least one transform map that 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 so, complete the steps below:
- Click View transform map on the import source form you are configuring.
- In the message that appears, follow the link Create transform map.
- Fill in the fields in the form that appears.
- Click Save or Save and exit to apply the changes.
A transform map can also be created from scratch. To do so, complete the steps below:
- Navigate to Import → Transform Maps to open the transform maps list.
- Click New and fill in the fields.
- Click Save or Save and exit to apply the changes.
Transform Map form fields
Field | Mandatory | Description |
---|---|---|
Name | Y | Specify a displayed transform map name. |
Active | N | Select this checkbox to make the transform map available for use. |
Source table | Y | Select the table containing the import set data. |
Target table | Y | Select the table where you need to place imported data. |
Silent load | N | Select the checkbox to ignore business rules, notification rules and other server-side engines that can be triggered by the 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. You can find engine and business rule execution orders in the Execution order article. |
Ignore mandatory fields | N | Select the checkbox to ignore mandatory fields in the target table. |
Use script | N | Select the checkbox to display the Script field, where you can define the transform script. If you clear the checkbox after inserting a script, the script will not be applied within the transformation. |
Script | N | Insert the transform map script you need to use to transform field values from the source table to the target table. Use the runTransformRow() function with the following parameters:
This script can be used to define field relationships before the transformation. You can also configure relationships by creating related records via the Field Map related list of the specified transform map. If you are configuring field mapping within the script, it is recommended to create one more Field Map record to define the Coalesce settings for this transformation session. See the Developer API articles to learn more about the advanced import. |
Transform scripts
Transform script allows you to customize import operations using JS-scripts with SimpleOne Server-Side API.
Transform Script form fields
Field | Mandatory | Description |
---|---|---|
Transform Map | Y | Specify a transform map to which this script is related. |
Active | N | Select the checkbox to activate the script. |
When | Y | Specify when to execute the script. Available options:
|
Order | N | Specify the order of the transform script execution. 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.
|
Script | N | Insert the transform script using the Server-Side API. Use the runTransformScript() function with the following parameters:
Filling in the fields in the onBefore script Expand source
(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);
Import skip in onBefore script Expand source
(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 the 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 relationships between the Source table and the Target table, create at least one field map record with the Coalesce checkbox selected. This checkbox should be selected 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 configuration item (CI).
You can select the Coalesce checkbox for more than one Field Map record.
To create a field map, complete the steps below:
- Navigate to Import → Transform Maps.
- Open the transform map you need to configure.
- Click Create field map to open the Field Mapping widget. See the screenshot in the table below.
- Configure field mapping within the current transformation process.
- Click Set to save the changes or Cancel to discard the changes.
Here is another way to create field maps:
- Navigate to Import → Transform Maps.
- Open the transform map you need to configure.
- In the Field Maps related list, click New and fill in the fields.
- Click Save or Save and exit to apply the changes.
- Repeat the previous steps for each field map that needs to be set.
Field Map form fields
Field | Mandatory | Description |
---|---|---|
Transform map | Y | Displays the transform map that contains this field map. The field is populated automatically if the record is created via the related list or the Field Map widget. |
Source field | Y | Select the field of the source table that should be transformed. |
Target field | Y | Select 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 the values in the Target table are equal to the values in the Import Set table. If the values are equal, the record in the Target table is updated; otherwise, a new record is created. This option can be set up within the widget. Click Create Field Maps on the transform map form to open a widget that contains the Add coalesce or Drop coalesce button. |
Use source script | N | Select the checkbox to define the transform script in the Script field. |
Script | Y | 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. The field appears when the Use source script checkbox is selected. Do not clear the Use source script checkbox after inserting a script; otherwise, it will not be applied. Use the transformEntry() function with the following parameters:
|
Run a transform
- Navigate to Import → Import Sets.
- Open an import set you need to transform. Ensure that the state is Loaded.
- Click Transform.
As a result, the 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:
- The sequence begins with the onStart transform script. It will be executed at the start of the transformation before any source row is read.
- (optional) The script specified in the Field Map record takes a record object of the Import Set Row table as an argument and returns a value to put into the Target Field.
- (optional) The script specified in the Transform Map record transforms field values from the source row to the target row.
- The onBefore transform script is executed before the source row is transformed into the target row.
- Between the execution of the onBefore and onAfter scripts, the target record is inserted or updated.
- The onAfter transform script runs after the source row is transformed into the target row.
- The onComplete transform script is executed when all source rows are transformed.
The onBefore and onAfter scripts are repeated for each record, they run until each record is transformed.
- No labels