In addition to the post about basic import, Novulo would like to add advanced information about import definitions used in imports.
Import definition > Best practices
-
Keep the “Delete records” checkbox unchecked:
-
If you use the import definition with the type “ODBC connection string”, the best practice is to always set a sort clause on the SQL query. You should put it on a column that can be sorted on easily, like the primary key (ID) or another index column. If you don’t sort explicitly, and the batch size is smaller than the total row count of the import, you might not import all records. If your sort column is not an index or primary key, the query can become too slow.
Novulo Import revision 205 introduced new functionality.
-
Read all existing records to compare with import data: this will load all existing records in the table in the server’s memory to compare if changes need to be processed. When importing large amounts of data, this can make imports process faster. Our recommendation is to test your import with the checkbox on and off to evaluate what is fastest for your server and dataset
-
Commit only changed records: With this checkbox off (single behaviour before r205), the import engine would commit all records that are imported, also when there are no changes. This updates the “Modifed at” and “Modified by” columns for all records all the time, and also triggers data processes. When you don’t need this, your import will be faster when the checkbox “Commit only changed records” is ON, as it will skip database commits when there are no changes.
Example with XLSX
If you need to update data from your application:
-
Find the needed table (grid) > check/add columns to your grid, including one based on a unique value > click on the “Export” button on the grid > the Export file is created.
-
The file can be modified (for example, add a new column “NewName” and rename the initial “Name” column to “OldName”.
-
Add an import definition with the needed data type and type XLSX > Fields > a new source field:
-
Set the source field > optionally in the “Notation” field you can indicate how your import should be interpreted (for example, dates format “dd-MM-yyyy” or decimal numbers ###.###,##). Default for dates is yyyy-MM-dd and default for decimal numbers is the culture of the account that is doing the import. Scheduling service will always be English if available:
-
Save > you are redirected to the Field form > set target field.
-
In the target field > if you set the checkbox “Use advanced expression” (1) to true > the field “Advanced target expression” will be visible (2) > inside it you can use Novulo expression and use one or more source fields. Notice: field_1 refers to the source field with the number = 1, etc.
-
Notice the checkbox “Allow null value” > it allows to set null to the field as value, must be used carefully if you don’t want to lose data. On the other hand, it can be used to clean the table columns (example here).
Example with XML
-
Go back to our example and set the type to XML > the field “Record XPath” will be visible:
-
Now we can do adjustments in the source field > for example, use one instead of two to take the name/old name:
-
You can put the smartness/conditions/etc. in the Source field box. The field is typeable. Often it makes your import faster.
