Columns configuration

You also need an "external" syntax for each column to define which external data goes into that column and any handling that might apply. This is also an indexed array. Obviously indices used for each column must relate to the indices used in the general configuration. In its simplest form this is just a reference to the external data's name:

'code' => [
    'exclude' => 0,
    'label' => 'LLL:EXT:externalimport_tut/locallang_db.xml:tx_externalimporttut_departments.code',
    'config' => [
        'type' => 'input',
        'size' => 10,
        'max' => 4,
        'eval' => 'required,trim',
    ],
    'external' => [
        0 => [
            'field' => 'code'
        ]
    ]
],
Copied!

The properties for the columns configuration are described below.

Properties

Property Data type Step/Scope
arrayPath string Handle data (array)
arrayPathSeparator string Handle data (array)
arrayPathFlatten bool Handle data (array)
attribute string Handle data (XML)
attributeNS string Handle data (XML)
children Children records configuration Store data
disabledOperations string Store data
field string Handle data
fieldNS string Handle data (XML)
multipleRows boolean Store data
multipleSorting string Store data
substructureFields array Handle data
transformations Transformations configuration Transform data
value Simple type (string, integer, float, boolean) Handle data
xmlValue boolean Handle data (XML)
xpath string Handle data (XML)

value

Type
Simple type (string, integer, float, boolean)
Description

Sets a fixed value, independent of the data being imported. For example, this might be used to set a flag for all imported records. Or you might want to use different types for different import sources.

This can be used for both array-type and XML-type data.

Scope
Handle data

field

Type
string
Description

Name or index of the field (or node, in the case of XML data) that contains the data in the external source.

For array-type data, this information is mandatory. For XML-type data, it can be left out. In such a case, the value of the current node itself will be used, or an attribute of said node, if the attribute property is also defined.

Scope
Handle data

arrayPath

Type
string
Description

Replaces the field property for pointing to a field in a "deeper" position inside a multidimensional array. The value is a string comprised of the keys for pointing into the array, separated by some character.

For more details on usage and available options, see the dedicated page.

Works only for array-type data.

If both "field" and "arrayPath" are defined, the latter takes precedence.

Scope
Handle data (array)

arrayPathFlatten

Type
bool
Description

When the special * segment is used in an arrayPath, the resulting structure is always an array. If the arrayPath target is actually a single value, this may not be desirable. When arrayPathFlatten is set to true, the result is preserved as a simple type.

Scope
Handle data (array)

arrayPathSeparator

Type
string
Description
Separator to use in the arrayPath property. Defaults to / if this property is not defined.
Scope
Handle data (array)

attribute

Type
string
Description

If the data is of type XML, use this property to retrieve the value from an attribute of the node rather than the value of the node itself.

This applies to the node selected with the field property or to the current node if field is not defined.

Scope
Handle data (XML)

xpath

Type
string
Description

This property can be used to execute a XPath query relative to the node selected with the field property or (since version 2.3.0) directly on the current node if field is not defined.

The value will be taken from the first node returned by the query. If the attribute property is also defined, it will be applied to the node returned by the XPath query.

Please see the namespaces property for declaring namespaces to use in a XPath query.

Scope
Handle data (XML)

fieldNS

Type
string
Description

Namespace for the given field. Use the full URI for the namespace, not a prefix.

Example

Given the following data to import:

<?xml version="1.0" encoding="UTF-8"?>
<Invoice xmlns:cbc="urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2" xmlns:cac="urn:oasis:names:specification:ubl:schema:xsd:CommonAggregateComponents-2">
    <InvoiceLine>
        <cbc:ID>A1</cbc:ID>
        <cbc:LineExtensionAmount currencyID="USD">100.00</cbc:LineExtensionAmount>
        <cac:OrderReference>
            <cbc:ID>000001</cbc:ID>
        </cac:OrderReference>
    </InvoiceLine>
    ...
</Invoice>
Copied!

getting the value in the <cbc:LineExtensionAmount> tag would require the following configuration:

'external' => [
    0 => [
        'fieldNS' => 'urn:oasis:names:specification:ubl:schema:xsd:CommonBasicComponents-2',
        'field' => 'LineExtensionAmount'
    ]
]
Copied!
Scope
Handle data (XML)

attributeNS

Type
string
Description
Namespace for the given attribute. Use the full URI for the namespace, not a prefix. See fieldNS for example usage.
Scope
Handle data (XML)

substructureFields

Type
array
Description

Makes it possible to read several values that are located inside nested data structures. Consider the following data source:

[
  {
    "order": "000001",
    "date": "2014-08-07",
    "customer": "Conan the Barbarian",
    "products": [
      {
        "product": "000001",
        "qty": 3
      },
      {
        "product": "000005",
        "qty": 1
      },
      {
        "product": "000101",
        "qty": 10
      },
      {
        "product": "000102",
        "qty": 2
      }
    ]
  },
  {
    "order": "000002",
    "date": "2014-08-08",
    "customer": "Sonja the Red",
    "products": [
      {
        "product": "000001",
        "qty": 1
      },
      {
        "product": "000005",
        "qty": 2
      },
      {
        "product": "000202",
        "qty": 1
      }
    ]
  }
]
Copied!

The "products" field is actually a nested structure, from which we want to fetch the values from both product and qty. This can be achieved with the following configuration:

'products' => [
 'exclude' => 0,
 'label' => 'Products',
 'config' => [
    ...
 ],
 'external' => [
    0 => [
       'field' => 'products',
       'substructureFields' => [
          'products' => [
             'field' => 'product'
          ],
          'quantity' => [
             'field' => 'qty'
          ]
       ],
       ...
    ]
 ]
]
Copied!

The keys to the configuration array correspond to the names of the columns where the values will be stored. The configuration for each element can use all the existing properties for retrieving data:

The substructure fields are searched for inside the structure selected with the "main" data pointer. In the example above, the whole "products" structure is first fetched, then the product and qty are searched for inside that structure.

The above example will read the values in the product nested field and put it into "products" column. Same for qty and "quantity". The fact that there are several entries will multiply imported records, actually denormalising the data on the fly. The result would be something like:

order date customer products quantity
000001 2014-08-07 Conan the Barbarian 000001 3
000001 2014-08-07 Conan the Barbarian 000005 1
000001 2014-08-07 Conan the Barbarian 000101 10
000001 2014-08-07 Conan the Barbarian 000102 2
000002 2014-08-08 Sonja the Red 000001 1
000002 2014-08-08 Sonja the Red 000005 2
000002 2014-08-08 Sonja the Red 000202 1

Obviously if you have a single element in the nested structure, no denormalisation happens. Due to this denormalisation you probably want to use this property in conjunction with the multipleRows or children properties.

Scope
Handle data

multipleRows

Type
boolean
Description

Set to true if you have denormalized data. This will tell the import process that there may be more than one row per record to import and that all values for the given column must be gathered and collapsed into a comma-separated list of values. See the Mapping data chapter for explanations about the impact of this flag.

If these values need to be sorted, use the multipleSorting property.

Scope
Store data

multipleSorting

Type
string
Description

If the multipleRows need to be sorted, use this property to name the field which should be used for sorting. This can be any of the mapped fields, additional fields or substructure fields.

Scope
Store data

children

Type
array (see Children records configuration)
Description
This property makes it possible to create nested structures and import them in one go. This may typically be "sys_file_reference" records for a field containing images. This should be used anytime you are using a MM table into which you need to write specific properties (like "sys_file_reference"). For simple MM tables (like "sys_category_record_mm"), you don't need to create this children sub-structure for the MM table. It is enough to gather a comma-separated list of "sys_category" primary keys.
Scope
Store data

transformations

Type
array (see Transformations configuration)
Description

Array of transformation properties. The transformations will be executed as ordered by their array keys.

Example:

$GLOBALS['TCA']['fe_users']['columns']['starttime']['external'] = [
 0 => [
    'field' => 'start_date',
    'transformations' => [
       20 => [
          'trim' => true
       ],
       10 => [
          'userFunction' => [
             'class' => \Cobweb\ExternalImport\Transformation\DateTimeTransformation::class,
             'method' => 'parseDate'
          ]
       ]
    ]
 ]
];
Copied!

The "userFunction" will be executed first (10) and the "trim" next (20).

Scope
Transform data

xmlValue

Type
boolean
Description
When taking the value of a node inside a XML structure, the default behaviour is to retrieve this value as a string. If the node contained a XML sub-structure, its tags will be stripped. When setting this value to true, the XML structure of the child nodes is preserved.
Scope
Handle data (XML)

disabledOperations

Type
array
Description

Comma-separated list of database operations from which the column should be excluded. Possible values are "insert" and "update".

See also the general property disabledOperations.

Scope
Store data