Page History

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 (in an Import Set), and map the data from the source to the target table with the transform map and field maps.

You can find a graphical procedure view of diagram illustrating the import process in the diagram below.

Anchor
Import Sources Import Sources

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.

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.

   Tooltip

   onlyIcon	true
   appendIcon	info-filled
   iconColor	blue

   You can attach JSON, XML, or Excel files.

    

     

4. Click Save or Save and exit to apply the changes.

Import Source form fields

[
  {
    "property": "value"
  },
  {
    "property": "value"
  }
]

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

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:

1. JSON files should be valid. Use the RFC 8259 document as the guideline.
2. 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, 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:
   

   Expand

   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.

The current system name of the column is transformed to lowercase.

Info
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.

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

Import set unique number. The field is populated automatically.

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

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

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.
• Canceled – data loading has been canceled.

1. title 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.

   Info
   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.
   
   

Anchor
Import Sets Import Sets

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

Table structure: (top500 | name | sector)

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

Anchor
Transform Maps Transform Maps

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:

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.

Transform Map form fields

Transform scripts

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

Transform Script form fields

(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);

(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);

Anchor
Field Maps Field Maps

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.

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 to open the Field Mapping widget. 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.

Field Map form fields

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. Imported data will be transferred into the target table.

Transform sequence

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

Image Added

1. The sequence begins with the onStarttransform 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.
   

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 records.

A transform map is a set of field maps. In this set of field maps, correlations between fields of the import set table and 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:

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.

Transform Map form fields

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

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.

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.

Field Map form fields

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.

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.