Data Import

The Import engine allows you to collect data from various data sources and then map this data into the relevant tables.

The SimpleOne import engine supports:

• different data source types
• different file formats (if you select File as the data source)

You can select a data source, place the data from it to a temporary table (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.

tip

Role required: admin, import_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 state of this import attempt.
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 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 as a set of field maps.
Transform script	A script that allows customizing import operations using native JavaScript extended with SimpleOne Server-Side API.
Field map	A record that specifies the relationship between a field in the Import Set table and a field in the target table.
Coalesce	This option allows you to update existing table records within the transformation process. See Coalesce to learn more.

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 selected, click the attachment icon and attach the file you need. You can attach JSON, XML, or Excel files.
4. 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	Select the type of import source. Available options: File Text LDAP
File retrieval method	N	Select the method to add the file to the import source. Available options: Attachment 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: JSON XML Excel (only when the File type is selected) This field appears if the File or Text type is selected.
Import text	N	Enter the text you need to import in the selected Format. This field appears if the Text type is selected.
LDAP setting	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 a new import set record by loading all the data from the import source. This import set can be used for further transformation.

After you load the data into the import source, save the record and call one of the data loading actions, a new record appears in the Import Sets related list.

JSON import

You can use files containing data formatted in JSON as an import source. In this case, ensure that the files correspond to the criteria below:

• JSON files should be valid. Use the RFC 8259 document as the guideline.
• The data type is one of the following: JSON object, Array or objects, or Object with array of objects. Dot-walking is not supported.

JSON Object

  {
    "top500": 1,
    "name": "More",
    "sector": "Oil and gas"
  }

Result: one element. The table has top500, name, and 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, and 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, and 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:

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:

Conversion table

	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

2. 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 _ .
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.

note

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

The system created an import set automatically based on the raw data uploaded into the import source. Import sets are intended to be a temporary storage for records imported from the data sources.

Import Set form fields

Field	Mandatory	Description
Number	N	Import set unique number. The field is populated automatically.
Import source	Y	The field is populated automatically and contains the related import source name.
Import set table	Y	The field is populated automatically and contains the related import set table name. System names of table fields begin with the imp prefix.
State	N	The import set loading state. This 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. Canceled – data loading has been canceled.
Short description	N	The field describes the structure of the table created and is populated with the data automatically. Field value example: Table structure: (top500 | name | sector)

The 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. Use it to specify the correlations between the fields of the import set table and fields of the target table.

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:

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

tip

A transform map can also be created from scratch. To do so, 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 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, the 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 order 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 is not applied within the transformation.
Script	N	Insert the necessary transform map script to transform the field values of the source table in the target table. Read the Developer API articles to learn more about the advanced import.

Script recommendations

Use the runTransformRow() function with the following parameters:

• source – the Import Set Row record that will be transformed. Call a specific field from the record like a regular SimpleRecord object. For example, source.imp_name.
• map – the Transform Map record used for the transformation process.
• log – the object that contains info, warn, and 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 like a regular SimpleRecord object. For example, target.name = source.imp_name. Mind the field type in the target table when using the object of the target parameter.
• isUpdate – a Boolean parameter that checks whether the target record is updated.

Use this script 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 the SimpleOne Server-Side API.

To do so, do the following:

1. Open the necessary record of the Transform Map table.
2. In the related list section, select the Transform Script tab.
3. Click New and fill in the fields.
4. Click Save or Save and exit to apply the changes.

Transform Script form fields

Field	Mandatory	Description
Transform Map	Y	Specify a transform map to use in the script.
Active	N	Select the checkbox to activate the script.
When	Y	Specify when to execute the script. Available options: 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 Set Row record transformation into the target table. onAfter – the script runs after each Import Set Row record is transformed into the target table. onComplete – the script runs when all Import Set Row records are transformed.
Order	N	Specify the order of the transform script execution. Fill in this field with an integer number.
Script	N	Add the transform script using the Server-Side API.

caution

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 recommendations

Use the runTransformScript() function with the following parameters:

• source – the Import Set Row record that will be transformed. Call a specific field from the record like a regular SimpleRecord object. For example, source.imp_name.
• map – the Transform Map record used for the transformation process.
• log – the object that contains info, warn, and 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 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 in the fields in the onBefore script:

Filling in the fields in the 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 – a Boolean parameter that allows you to skip the Import Set Row record.
• status_message – the message that describes the process of 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 the fields of the import set table and the fields of the target table. To do so, determine the values from the source table that will be added to the target table.

info

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 Coalesce attribute enabled. 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.

To achieve stricter mapping, 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 need 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 the changes or Cancel to discard the changes.

tip

Here is another way to create field maps:

1. Navigate to Import → Transform Maps.
2. Open the transform map you need to configure.
3. In the Field Maps related list, click New and fill in the fields.
4. Click Save or Save and exit to apply the changes.
5. Repeat the steps 1–4 for each field map that needs to be set.

Field Map form fields

Field	Mandatory	Description
Transform Map	Y	Specify the transform map using 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 verifies that values in the Target Table are equal to values in the Import Set table. If the 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 the Add coalesce and Drop coalesce buttons appears.
Use Source Script	N	Select the checkbox to define a 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. Read the Developer API articles to learn more about advanced import.

Script recommendations

Use the transformEntry() function with the following parameters:

• source – the Import Set Row record that will be transformed. Call a specific field from the record 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 like a regular SimpleRecord object. For example, target.name = source.imp_name.

Run a transform

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

As a result, the message Import is completed appears. The imported data is 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. (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.
3. (optional) The script specified in the Transform Map record 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 the onBefore and onAfter scripts, the target record is inserted or updated.
6. The onAfter transform script runs after the source row is transformed into the target row.
7. The onComplete transform script is executed when all source rows are transformed.

note

The Field Map, Transform Map, and the onBefore and onAfter scripts are repeated are repeated for each record until each record is transformed.