Load Mapping

The Load Mapping component allows you to read so called mapping workbooks into the Content Store. The mappings will be stored in the mappings collection.

To read and use the mappings you can use the following functions in the Code component and the HTTP Request 2 component:

• lookup
• lookupByKey
• getMappingKeys

Each sheet in a mapping workbook needs to have a proper column name, which is specified in the first row. Prefix a column name with an asterisk * to mark it as a key. You can prefix multiple columns to specify combined lookup fields, at least one column needs to be prefixed. Besides that, a mapping workbook doesn't need to have any specific format and each sheet will be read automatically.

Example mapping Excel sheet:

*name	*lastName	street	number	city
Mark	Mike	Main Street	12	Los Angeles
Morgan	Freeman	First Avenue	24	Washington DC

To access a mapping use the combined mapping name, which exists of the workbookName without the extension and the sheetName separated by a -. Note that all names are lowercase. The same goes for the column names.

For example:

The mappings in a workbook with the name myMappings.xlsx that contains the following sheets: states, persons can be access by using the following names: mymappings-states and mymappings-persons

Optionally you can specify if existing mappings should be deleted prior to inserting and if each cell should be trimmed.

note

Empty rows are fully ignored

For more information on using the mapping functions, see the Code component.

Configuration

Path

Path to the file. The path can be overwritten by sending { path: "/path/to/file.xlsx" } to the input.

Example: ../test.xlsx

Ignore empty keys

When checked, empty cells in columns marked as a key are allowed. By default, empty rows in columns marked as a key will result in an error.

Delete existing mappings

When checked, existing mappings will be deleted based on the mapping name.

Trim cell

When checked, a trim will be applied to all cells. This removes the leading and trailing white space and line terminator characters from the value of the cell.

Content Store

Connection settings for storing the mappings in the Content Store.

Connection string

A MongoDB connection string.

Example: mongodb://<username>:<password>@localhost:27017/<databaseName>

Here <databaseName> is the database to use.

Use TLS

Whether or not to use TLS in case your mongoDB requires TLS.

Allow Invalid Certificates

Checking this will disable certificate validation. Warning: specifying this option in a production environment makes your application insecure and potentially vulnerable to expired certificates and to foreign processes posing as valid client instances.

Certificate Authority File

One or more certificate authorities to trust when making a TLS connection. In order to access the local filesystem, the XILL4_WORKDIRS environment variable must be set to the path of the directory to be accessed.

Inputs

• Name: Input

  • Description: Any message will make the component start reading the file from the path that is given in the configuration. However, the path can be overwritten by sending { path: "/path/to/file.xlsx" } to the input.

  • Example:

    {
      "path": "/path/to/file.xlsx"
    }

Outputs

• Name: Output

  • Description: The output will contain the names of the mappings that are successfully stored.

  • Example

    {
      "value": //incoming message
      "result": {
        "storedMappings": [
          {
            "path":"/path/to/file.xlsx",
            "storedMappings": [
              "mapping-users",
              "mapping-users"
            ]
          }
        ]
      }
    }