Skip to content

Import Export

Import and export should be done using ViewGenerator options (see CLX-5609). If you need to implement some custom data handling for some reason, the model classes of the lib component Convert can be used.

Using Lib Convert

The "Convert" component provides ways to import or export based on a configuration. The configuration has the following format:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
fields: <fieldConfig>
className: <entityName>
batchSize: <batchSize>
importer: <importer>
importerOptions: <importerOptions>
behavior: <behavior>
idData: <idData>
template: <template>
filter: <filter>
livecycleCallbacks:
  pre: <callback>
  postBatch: <callback>
  postFailure: <callback>
  postSuccess: <callback>

The following options are present for import and export:

  • <fieldConfig> is the configuration on how to produce field values. See below.
  • <className> is the fully qualified entity class name to import/export.
  • <batchSize> conversion is done in batches of 1000 by default. Use this to set a custom batch size.
  • <callback> contains a "adapter" and "method" key to trigger a JSON adapter method before/after import/export/batch.

The following options are only present for import:

  • <importer> is the fully qualified class name of a class implementing \Cx\Lib\Convert\Model\Entity\ImportInterface. See built-in importers.
  • <importerOptions> is a key value array of options to pass to the importer. See built-in importers.
  • <behavior> is one of "truncate", "overwrite" and "skip". "truncate" will empty the table before importing. "overwrite" will overwrite attributes of any existing entries. "skip" will skip existing entries. For "overwrite" and "skip" you need to specify .
  • <idData> is used to identify existing entites. See entity filters below.

The following options are only present for export:

  • <template> is the template to use for export. See below.
  • <filter> can be used to filter the entities to export. See entity filters below.

Field mappings

Field mappings are always from the perspective of the target. So we say "We need
these fields and their values are created as follows.". The first index is therefore
the list of fields we want to produce:

1
2
3
4
5
6
<myTargetField>:
  fields: <sourceFields>
  defaultValue: <defaultValues>
  format: "<format>"
  typeConversions: "<typeConversions>"
  callback: <callback>
  • <myTargetField> is the name of the field to create a value for.
  • <sourceFields> is a list of source fields.
  • <defaultValues> is a set of default values, indexed by source field name.
  • <format> allows chaining source fields together with other strings using placeholders in lower than and greater than signs.
  • <emptyFormat> is optional. If set it is used instead of <format> if all fields have empty values.
  • <typeConversions> can be used to do type conversion. This might require additional fields.
  • <callback> is a JSON adapter reference to a callback.

Field mapping type conversions

<typeConversions> is a list of type conversions to do in the order they appear in the mapping. The format to specify them is as follows:

1
2
3
  typeConversions:
    <type>:
      <typeOptions>
  • <type> is one of the types listed below
  • <typeOptions> are key-value pairs of options for the type. See below which are available or required.

The following conversions currently exist:

  • int: Returns the integer value of the field.
  • float: Returns the floating point value of the field.
  • bool: Returns the boolean value of the field. Set the type option inverted to true to invert the value.
  • entity: Fetches a related entity, see below.
  • DateTime: Creates a DateTime object or a DateTime format string, see below.
  • RegEx: Performs a regular expression replacement, see below.
entity

Fetches an entity to relate (import) or a related entity (export). The following type options are required:

1
2
  class: "<entityClass>"
  idData: "<entityFilter>"
  • <entityClass> is used together with "entity" type conversion.
  • <entityFilter> is the filter to fetch an entity by. See entity filters below.
DateTime

Passes the field value to \DateTime::construct() and converts the timezone from/to the internal timezone. The default timezone for imports and exports is "UTC". Use the following type option to override the default timezone:

1
  timezone: <timezone>
  • <timezone> is the optional timezone to use for this field.

Additionally, a format can be supplied in which case the conversion yields a string in the given date format (see PHP's date format):

1
  format: <phpDateFormat>
RegEx

Does a regular expression replacement based on a <pattern> and a <replacement>:

1
2
  pattern: <pattern>
  replacement: <replacement>

Examples

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
simpleField:
  fields:
  - mySourceField
dateTimeField
  fields:
  - date
  - time
  format: "<date> <time>"
  typeConversions:
    "DateTime":
relatedEntity:
  fields:
  - foreignId
  typeConversions:
    "entity":
      class: "Cx\\Modules\\MyComponent\\Model\\Entity\\MyEntity"
relatedEntity2:
  fields:
  - foreignId
  typeConversions:
    "entity":
      class: "Cx\\Modules\\MyComponent\\Model\\Entity\\MyEntity"
      idData:
        "myEntityRelationField.someRelation[5]":
          fields:
          - foreignId
inexistantField:
  fields:
  - dummy
  defaultValue:
    dummy: "someValue"
fieldWithCallback:
  fields:
  - mySourceField
  callback:
    adapter: "MyJsonAdapter"
    method: "myJsonMethod"

Entity filters

Entities can be fetched or identified using a class name, a filter and a value.

  • The class name identifies the entity class to fetch and the point from where the filter starts.
  • The filter identifies the field to match in relation to the entity class.
  • The value needs to match the field identified by the filter.

A very simple example is for fetching the user with ID 3:

Class name: \Cx\Core\User\Model\Entity\User
Filter: id
Value: 3

This fetches an entity of the user class where the field id matches the value 3.

A more elaborate example would be to fetch the user who's profile attribute 7 has the value 3:

Class name: \Cx\Core\User\Model\Entity\User
Filter: userProfile.userAttributeValues[attributeId=7,history=1].value
Value: 3

The most common use of this is to identify existing entities based on import data using the idData key.
If we build on the example above and want the user that who's attribute 7, which we're going to inject
as a parameter, matches the client key from the import data:

1
2
3
4
idData:
  "userProfile.userAttributeValues[attributeId=<clientAttributeId>,history=1].value":
    fields:
      - client

Please note that the example above might be unpractical as we need to specify the attribute value's history ID.

The other use-cases are:
* Finding entities that need to be related to entities on import. For example assigning a user to a group.
* Filtering the entities that shall be exported.

Built-in importers

The following importers are built in:

Class name File type Constructor arguments Options Description
\Cx\Lib\Convert\Model\Entity\Csv CSV separator and enclosure can be set. They default to ; and " columnNamesInFirstRow can be set to false if this is not the case. Imports a comma separated file.
\Cx\Lib\Convert\Model\Entity\Xml XML - elementName need to be set to the name of the element representing an entry. Very simple XML importer. Returns the name and value of direct child elements of the element specified as elementName.

Export template

Export templates have the following structure:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
<!-- BEGIN head -->This will be output before any entities<!-- END head -->
<!-- BEGIN entity -->
    This is output for each exported entity
    <!-- BEGIN field_xy -->
        This is output for the field `xy`.
        {FIELD_NAME} outputs the current field's name.
        {FIELD_VALUE} outputs the current field's value.
    <!-- END field_xy -->
    <!-- BEGIN field -->
        This is output for all fields that do not have a specific block.
        {FIELD_NAME} outputs the current field's name.
        {FIELD_VALUE} outputs the current field's value.
    <!-- END field -->
<!-- END entity -->
<!-- BEGIN foot -->This will be output after any entities<!-- END foot -->

How to trigger

You can trigger an import by calling the following method:

1
2
3
4
5
6
$exporter = new \Cx\Lib\Convert\Controller\Import();
$exporter->exportToFile(
    $this->cx,
    $exportConfig,
    $file
);

You can trigger an export by calling the following method:

1
2
3
4
5
6
$exporter = new \Cx\Lib\Convert\Controller\Export();
$exporter->exportToFile(
    $this->cx,
    $exportConfig,
    $file
);

You can optionally pass true as a fourth argument to these methods to turn on
profiling output.

Usage with legacy or non-DB entities

Warning

This is subject to change!

  • We should only call the callback when data is mapped
  • For this we'd need a separate callback to get entities by
  • Callback should not have to handle

You can use this to call a method for each set of entity data instead of
inserting it into the database using Doctrine automatically.

To achieve this you simply pass a string like the following in the
field:

Legacy::<componentName>::<controllerName>::<methodName>

This will then receive the following arguments:
* string $className: The string you passed above.
* array $fieldConfig: The field part of your import configuration.
* array $element: Unmapped/raw data for this entity.
* Behavior $behavior: An enum that you do not have access to ... <-- this needs to change!
* array $idData: Data to identify existing entities by
* bool $profile: If this is true you "should" output profiling data, if any

Let the user create an import/export configuration

Information

This is not yet implemented, but planned

Legacy usage

\Cx\Lib\Convert\Model\Entity contains models for data in different formats. They
allow reading from and writing to file, buffer or string and should be used
instead of using any native PHP functions (for example fgetcsv()) directly.

Currently, only reading from a CSV file is supported. This can be done as follows:

1
2
3
4
5
6
7
8
$objFile = new \Cx\Lib\FileSystem\File('my-file.csv');
$csvImporter = new \Cx\Lib\Convert\Model\Entity\Csv();
$data = $csvImporter->parseFile(
    $objFile,
    function(array $lineData, int $lineNumber): void {
        // ...
    }
);

The callback gets called for any non-empty row of the data file. Rows only
containing separator and enclosures are treated as empty.

By default the first (non-empty) row is used as headers, by which the data will
be indexed. If you don't want that you can pass false as the third parameter.

For more info about the available parameters, see code documentation.

Import (deprecated)

The following is the description of the deprecated Import controller for reference.

Als erstes muss die Import Klasse eingebunden werden und ein Objekt davon erstellt werden:

1
$importlib = new \Cx\Lib\Convert\Controller\Import();

Der Importvorgang erfolgt nun in 3 Schritten:

  1. Auswahl der Datei, Angabe von Optionen (Typ der zu importierenden Datei [csv, excel]). Die Datei wird beim Abschicken des Formulars hochgeladen. (Fileselect)
  2. Die Datei wird in den tmp Ordner verschoben und ein erstes mal geparst. Dabei werden die Feldnamen herausgelesen. Erledigt wird dies von einer weiteren Klasse. Danach wird das Formular zum zuweisen der Felder angezeigt. Die Optionen von Punkt 1 werden per hidden Felder weitergegeben. (Fieldselect)
  3. Nun wird die Datei ein zweites mal geparst und die Daten werden herausgelesen. Aufgrund der Zuweisungsangaben (Punkt 2) des Users wird ein array mit den Daten zurück gegeben. Die Datei wird gelöscht, die Arbeit der Importklasse ist erledigt.

Entsprechend dieser drei Schritte ist nun folgendes zu tun: (Ein gutes Beispiel befindet sich in der Datei modules/memberdir/admin.class.php, _import() Methode)

If Verzweigungen

Im code, wo die Import Klasse verwendet werden soll, müssen einige if-Verzweigungen gemacht werden. Hier ein Beispiel:

1
2
3
4
5
6
7
8
9
if (isset($_POST['import_cancel'])) {
    // Abbrechen. Siehe Abbrechen 
} elseif ($_POST['fieldsSelected']) { 
      // Speichern der Daten. Siehe Final weiter unten. 
} elseif ($_FILES['importfile']['size'] == 0) { 
     // Dateiauswahldialog. Siehe Fileselect 
} else { 
     // Felderzuweisungsdialog. Siehe Fieldselect 
}

Fileselect

Beim Importlib Objekt muss die entsprechende Methode aufgerufen werden:

1
$importlib->initFileSelectTemplate($this->_objTpl);

Übergeben werden muss das Template Objekt. Die Variable wird per Referenz übergeben.

Die Methode zeigt nun das Standard Fileselect-Formular an. Falls noch weitere Optionen benötigt werden, können die wie folgt hinzugefügt werden:

1
2
3
4
5
6
7
8
$this->_objTpl->setVariable(array(
    'IMPORT_ACTION'      => '?cmd=memberdir&amp;act=import',
    'IMPORT_ADD_NAME'    => 'Kategorie',
    'IMPORT_ADD_VALUE'   => $this->_getCategoryMenu(),
    'IMPORT_ROWCLASS'    => 'row2',
    'TXT_HELP'           => 'W&auml;hlen Sie hier eine Datei aus, deren Inhalt importiert werden soll:'
)); 
$this->_objTpl->parse("additional");

Der TXT_HELP Platzhalter ist für die Anzeige eines Hilfe-Texts.

Fieldselect

Auch hier muss eine Methode des Importlib Objekts aufgerufen werden, mit dem Templateobjekt als Parameter:

1
$importlib->initFieldSelectTemplate($this->_objTpl, $given_fields);

Der zweite Parameter ist ein Array mit den 'gegebenen' Werten, also den Werten die in der Rechten Spalte angezeigt werden bei der Feldauswahl. Das Array muss folgende Struktur haben:

1
2
3
4
5
Array
(
    [Key] => Value,
    [2] => "Beispiel"
)

Falls noch Optionen vom Fileselect (additional options) durchgereicht werden müssen, können auch noch zusätzliche hidden-input-Tags geparst werden:

1
2
3
4
$this->_objTpl->setVariable(array(
    'IMPORT_HIDDEN_NAME'    => 'category',
    'IMPORT_HIDDEN_VALUE'   => 5,
));

Final

Am Ende muss noch die Methode getFinalData aufgerufen werden:

1
$data = $importlib->getFinalData($fields);

Übergeben wird ein Array mit den Feldnamen.

1
2
3
4
5
Array
(
    [Key] => Value,
    [2] => "Beispiel"
)

Man erhält nun ein Array mit den Daten. Das Array hat folgende Struktur:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
Array
(
    [0] => Array
        (
            [key1] => Wert1
            [feldname1] => Wert1
            [key2] => Wert2
            [feldname] => Wert2
        )
    [1] => Array
        (
            [1] => Astalavista
            [Firmenname] => Astalavista
            [2] => Schmid
            [Kontaktperson] => Schmid
        )
)

Nun müssen die Daten nur noch gespeichert werden ;)

Abbrechen

Damit der Benutzer den Vorgang auch abbrechen kann, muss noch die Cancel Methode implementiert werden. Anschliessend empfiehlt es sich, eine Weiterleitung zu machen.

1
2
3
$importlib->cancel(); 
header("Location: index.php?cmd=memberdir&act=import");
exit;