NatchOS offers an out-of-the-box import for plain text files.


FILE REQUIREMENTS

The files must match the following criteria:

  • Flat file in TXT file type
  • Single entity per file, optional extra file for child entities (ie. Product and product translations)
  • Header record is required, even if there is no data
  • Child files are required, with header record, even if there is no data
  • UTF8 encoding
  • Field separator must be used, no fixed length fields
  • Numeric values must be consistently formatted in the same culture
  • Date values must be formatted as YYYYMMDD, ie. 25th of May 1977 is 19770525 
  • Time values must be formatted as hh:mm:ss, ie. 13:05:00
  • Use a text qualifier
  • Required fields must be supplied or a default value must be configured


FILE LAYOUT GUIDELINES

These guidelines are highly recommended:

  • File compressed into ZIP
    • Child file should be zipped together with parent file - this ensures delivery of both files at the same time
    • Zip files may be password protected. The password length must be between 8 and 1000 characters. Supported encryptions are ZipCrypto and AES-256.
  • Field delimiter is TAB (ASCII 9)
  • Text qualifier is double quotes (ASCII 34)
  • Use invariant culture (LCID 127) for numeric values, for example 3.1415 for Pi
  • Each record should contain the same number of fields


FILE NAMING

We have a naming convention for files, that consists of 3 parts, that are joined by an underscore.

  • 9 digits for the ID of the entity
    • This number must increment (gaps allowed) with every file
    • Child file must have the same ID
  • Entity name (either predefined or custom)
  • Incremental of full delivery flag, using one of these 2 values: incr, full.


=> 190529099_customer_full.txt

=> 190529100_product_incr.txt

=> 000000022_product_full.txt and 000000022_productml_full.txt zipped into 000000022_product_full.zip




INCREMENTAL / FULL DELIVERY

The files can be processed as an incremental delivery or as a full delivery.


Incremental

These files are small and contain only changes since the previous devlivery of the entity.

Processing is fast and is possible during business hours.

Only the records in the file are inserted, updated or deleted.


Full delivery

These files contain all the data of the entity.

Processing may be slow and is only available during off-peak hours.

Live data is replaced by the records in the file. This will result in a delete of existing records that were not supplied in the file.

We have a protection against deleting too many records in case we process a file with not enough records. This protection check the percentage of records that will be deleted against a defined threshold, ie. 50% or 90%.


Incremental and full delivery may be mixed for an entity. Ie. A full delivery during the weekend, and incremental deliveries during the week.



DATA


Main file: Contains records per entity (ie. Product, Category), or link between entities (ie. The many-to-many category-product combination).

Every entity has it own set of predefined fields, which are documented per entity in their file definition. But there are some common fields that will be found in multiple files:

  • ~Code field - fields with Code as suffix:  the unique key of the entity as known in the master data.
  • LCID field - the Language Code Identifier. Defines the language and country of a record (ie. Product translation).
    • 2067 = nl-be = Dutch - Belgium
    • 2057 = fr-be = French - Belgium
    • 1043 = nl-nl = Dutch - Netherlands
    • Records must be supplied for every language and country combination your application will be available in. Even if the data is the same for a language in multiple countries.
    • (Google 'LCID' for complete list)
  • Misc field - data that can not be placed in the predefined fields can be placed inside XML in this field.
    • <root><extrafield>value</extrafield><anotherfield>1</anotherfield></root>
    • Fields/nodes must be placed within a single parent node.
  • Action field
    • Value I indicated INSERT command (processed as upsert)
    • Value U indicates UPDATE command (processed as upsert)
    • Value D indicates DELETE command (only values for key field and action field are required)
    • Delete only makes sense for incremental delivery; full delivery should always use Insert or Update.
    • Not for child files
  • PhoneNumberTypeID field - ID that indicates the type
    • 1 = phone
    • 2 = fax
    • 3 = mobile
  • SpatialLocation field - Latitude and longitude of a location.
    • Formatted as <lat>,<long>
    • Period as decimal separator
    • Example: 51.09644,3.83194 for Lochristi, Belgium


If master data does not have a unique key or the key consists of multiple fields, a single key must be created by joining fields together to create a unique key. To ensure a unique key is created out of multiple fields, a common technique is to use a separator between the fields, ie. pipe symbol (ASCII 124).

An example for a shipping address of a customer. The key for the customer is 'CC654'. The shipping address does not have a unique key, but is unique for the customer as '001'. The shipping address key would be 'CC654|001'.


Keys must be unique in a single file.


Optional child file: Contains child records of the parent. This is a one-to-many relation. Ie. 1 customer can have multiple phone numbers.



DATA TYPES:

  • Text / (n)varchar / (n)char: surround with text qualifier
  • Number / int / decimal: integer or decimal in invariant culture
  • Bool(ean) / bit: 0 or 1
  • Date: YYYYMMDD
  • XML: Fields/nodes must be placed within a single parent node.



ENTITIES:

Check your Admin Platform to see the configured entities with details about the file.