Import file format

This document describes the file format to import data into equanda applications.
This file format applies both to the ImportServlet and to the Import tool.

The equanda import file format is roughly based on CSV files.
However, many extensions have been provided to make the import file self-contained,
provide for powerful features like multiple fields and links,
to allow data for different tables to be sent in one connection
and that the table and field names can be specified.

General rules

• Empty lines are discarded

• Lines starting with a semicolon or hash are comments and are also discarded

• Lines starting with a colon are directives.

• All other lines are considered as data

File structure

• The file can contain zero or more data blocks.

• A data block starts with a table directive, followed by data lines

Table directives

• A table directive starts with ":table:" followed by the (root) table name (possibly surrounded by whitespace, and optionally including a selector), followed by another semicolon and the list of field names which are included in the data lines.

• The field names are separated by a comma.

• Apart from the real field names, "UOID" may be used to indicate the uoid, and "equandaType" can be used to indicate the record type.

• Whitespace around the fieldname is discarded.

• Field names are case sensitive (exactly the same as the definition in equanda).

• For linked fields, the field name has to either include the selector to select the linked record(s) or a list of fields (between square brackets) or both. When linking multiple fields which are specified using fields, the field names should be in double square brackets ("[" and "]", with no space in between).

• Only root table names can be used. You have to use equandaType to distinguish between variants of a root type.

Some example table directives :

:table:Company:equandaType,Reference,Name
:table:Company/Company: Reference, Name, PersonName, Address, Zip, City, Country, VAT, Customer, Supplier
:table:Company/TraceId: equandaType, Reference, TraceId, Name, MeatCompanyInfo/EECNumber
:table:Company/TraceId: equandaType, Reference, TraceId, Name, MeatCompanyInfo[Activity,EECNumber,OfficialRegistrationNumber]

NOTE for the selectors, you can only use those selectors which select only fields from the table itself.
If the selector includes referenced fields from other tables, it cannot be used to select the records in the import file.

Data line structure

• No data line may occur in the file before the first table directive.

• Field values are comma separated

• String field values are enclosed in double quotes. Any double quotes inside the field value have to be doubled.

• When a string value is not quoted, all whitespace in it is discarded.

• Inside a quoted string, quotes, newlines, carriage returns and tabs can be indicated with a backslash followed by a double quote, 'n', 'r', 't' respectively. Any backslashes which should be part of the actual value need to be doubled.

• Numeric fields are included with a dot as decimal point.

• Whitespace around the field values is discarded

• Null can be represented by "null" (without quotes). To use the string value "null", the null has to be quoted.

• Fields may be skipped (that is no value or just whitespace, but with sperating comma), in which case this is interpreted as if the field was not declared in the table directive. If no field values follow, then the commas may also be discarded.

• Multiple fields which have more than one value need to have their values enclosed in square brackets (same rules for quoting, whitespace etc). Eg '"val", "bla" , "alb" '

• Linked fields are indicated by the selector parameters (if more than one parameter, then the different parameters have to be in square brackets again).

Import / Update

The import servlet / tool can be used both for importing and for updating data.

When a record is specified, equanda uses the specified selector to check whether the record already exists.
If it does, then the fields are updated, otherwise a new record is inserted.

When no selector is specified, equanda tries to see whether the data line included a unique field.
If it does, this field is used to check for existence of the record.
When more than one unique field is included,
it is not defined which field will be used first to try to select a matching record.

Linked records

The data line can include linked records either using a selector, or by specifying the actual value.

When using a selector name, the value is the list of parameters for the selector.
If there is more than one parameter then this list needs to be enclosed in square brackets.
If the field is single linked, and the selector returns more than one record, then the import fails.

Alternatively, you may use a field list (optionally combined with a selector).
This will create or update the record and link it.

Examples

A couple of simple examples for some fictive tables (definition not included and may be different for each example).

:table: JustAString : Value
Simple test

This will include the string "Simpletest" without space, as spaces are discarded when the value is not quoted.

:table:Address: Reference, Street, Location
"addr1", "Mechelbaan", "Putte"
:table:Person: Name, Address/Reference
"Joachim", "addr1"

Simple example of a linked record.

:table:Person: Name, Address[Street, Location]
"Joachim", ["Mechelbaan", "Putte"]

Simple example of a multiple linked record.

:table:Person: Name, Address[[Street, Location]]
"Wim", [ ["Kerklaan", "Putte"], ["Heistraat", "Bree"] ]

Use of the automatic creation of linked records.

:table:Person: Name, Address[ Street, Location/Zip[Zip,City] ]
"Joachim", [ "Mechelbaan", [ "2580", "Putte" ] ]

Advanced linked record with nesting and combined selector and fieldlist for "Location".