The Novulo platform has an advanced Import engine that imports data from external sources. The Import engine works along with the Export engine and allows flexible data management. For example, you can import products, contacts, addresses, etc., and link them.
You need to install M3448 in your application to use the basic import.
Import engine basics
The setup looks like this: an import record (1) has an import definition assigned (2) and an import file/source (3) > an import definition has fields (4).
Every time you click on the “Import” button, you import records from your import file/source. The rules for importing are defined in the import definition: do you want to import all records or filter them, do you want to import all columns for every record or some, etc.
Every import record has an import mode. It defines how you want to import records:
At an import definition level, you define what you want to import, and what data (record type, fields, etc.).
This instruction will teach you how to create a new importdefintion with import fields and then use these to import new data into a application.
How to create a simple import definition?
If you want to start importing data, first you have to choose a dataset. A dataset corresponds to a specific SQL table in the application. So for example, products, countries, persons, etc. are all datasets that you can import. An import definition can only import exactly one dataset. For the import of mulitple datasets at once you can use sequential import definitions, which is more advanced.
Once you chose the dataset you can define the fields/values that you are going to import to your application. For example, for a product that could be its code, name or its GUID.
Every field that you define corresponds to a column in your import excel sheet.
Adding a new import definition
Go to All apps > Application maintenance > Import > Import definitions > click on “Add” button > fill in the form:
Let us check the most important fields.
- Dataset defines the record type (data table) being imported. You can add one dataset to the import definition. For example, we want to import products – in the Dataset field, we choose products.
- Key value defines a property (data table column) to check if a record is unique. It also could be a combination of key values. For example, you could set a name and a code as key values, so the import engine won’t allow you to import two product records with the same name and code. We recommend including a GUID as a key value if it is available. GUIDs tend to be unique accross multiple applications and are therefore a good unique identifier.
- Duplicate behavior. If there are duplicate records, the Import engine can:
- Overwrite it (import leading);
- Do not overwrite;
- Add duplicate;
- Stop import;
- Overwrite (system leading).
- Do not add new records. This prevents the import from adding new rows. It will only update existing data. If you are sure that the import should not add new data but only change/update existing data, you can turn this on.
- Import type (CSV, XML, XLS, XLSX, SQL, ODS, ODBC (for the last one check How to add a ODBC connection string in the import definition?)).
- Use parameters (default = no). It is for advanced import: you can use parameters for filtering the dataset and its fields.
- Sheet name. If you import with an excel file, the sheet that you want to import has a name. You must define which sheet should be used because an excel file can have multiple ones.
- Headers start at row. In an excel file the column headers mostly start in row 1. The application needs to know which row it should look at to identify the columns. Let us say in cell A1 you place some personal notes that should not be imported, and the column headers are in row 2, you should set this to 2 so that the application does not try to import the personal notes from cell A1.
Adding import definition fields
As soon as you fill in the import definition form, you need to set the import fields (properties, values) you want to import and add them to the fields grid. The easiest way is to add all the fields and remove the ones you do not need:
Also, you can add fields one by one via the “Add field” form.
Let us have a closer look at the import definition fields.
Every import definition field has a target field. A target field is a property (value, data table column) of a data model you choose as a dataset (for example, target field = GUID of a data type record = product). It matches with the source field (= field_1 in your target expression). A source field relates to a column in your import file/import source. For example, the import excel file contains a list of products with columns like name, code, status, etc. Then, there should be an import field with a matching source field for each of these columns.
To create a new import field, first you choose a target field for an import definition field. In the example below we choose among all available product properties in the application. Depending on your application composition there could be less or more in your application.
Notice! If the target field is a link to another data model (table), the Field form will suggest you set a field of the linked record for machting. For example, a product has a unit > it is a link between the product data model and the unit data model > so the import engine will match the unit record by its source field. For example, in your import file in the Unit column are stored GUIDs, so you set GUID as a source field in your import definition field for a unit.
As soon as the field (1) with the target field (2) is added > add a source field (3): type a Source field name (4) that corresponds to the name of the column in your import sheet (case sensitive!) and set a type (5).
For some target fields (for example, enumerations) the import engine sets type and target expression automatically.
In our import definition example (1) we are going to add a new field (2) to match the property “Is distributed” (3) with the type yes/no (4). As soon we set the type > the target expression filtered this enumeration via a source field (5). In the file we exported “Is-distributed” column contains a description of the enumeration (“Yes” or “No”), so we set a source field with the type = text (6).
Tip! Make sure that the source fields for the import definition fields are set. Do not leave them empty, otherwise, the import engine won’t match fields from the import file to properties (columns) of the chosen dataset (table).
Double check source field names and types: for example, if in your import file, GUID is saved as a string (text), but in your import definition you saved GUID as a number or with a different name (for example, CUiD), the import will return an error.
Notice! For an advanced import, you can add more than one source field and use them in advanced target expressions. The expression will then use field_1, field_2, etc. to calculate a new value that can be stored in the application.
Then check the key values grid. You can add one or more. Key values ensure that you do not import duplicate values.
How to use that definition for an import?
Go to All apps > Application maintenance > Import > Imports > add a new one > fill in a form:
Let us check the most important fields.
- Import mode.
There are 3 options to import data, they are defined in the Import mode:
- Import with a definition > First you create an import definition and then assign it to your import.
- Import with an embedded definition > If you select this import mode, you need to attach an import file in XLSX or XML.
- Import a definition > Instead of importing data, you can import an embedded import definition in XLS, XLSX, XML, ODS.
What are embedded import definitions?
Embedded import definitions are import definitions that are embedded in a file. When using an export definition, one can generate a matching import definition. Then one can export the data, and the application will embed the import definition. Then, while importing the application knows how to interpret the data in the file automatically and one does not have to create an import definition manually first.
Also see this community post for more info: https://community.novulo.com/t/how-can-i-c
reate-an-excel-sheet-template-that-is-optimzed-for-an-import-into-novulo/51
How to create import definitions automatically based on export definitions?
You can create an import definition automatically from the export definition if the embedded import definitions checkbox is set to “Custom” (click on the button “Generate import definitions”).
For this functionality, you need to install M3792 into your application.
Go back to our import record. What are other fields?
-
Skip missing fields / Skip error rows
If set to true > the Import engine “meets” the missing fields or a row with an error, it will try with the next row without stopping the import. -
Imported
Set to true as soon as the import is finished. -
Run in background
By letting the import run in the background the import is executed by the scheduling service, so you do not see a spinner while a huge import is running and can continue your work. The disadvantage is that you have to refresh the page manually to check whether the import is done.
When the import form is filled in > add an import file > click on the” Import” button > check the import logs and the overview of the records that you were importing.
Tip! Sort the import logs based on their ID in descending order to always see the newest log at the top of the list.