Load Mapping

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

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

• lookup
• lookupByKey
• lookupByField
• getMappingKeys

Each sheet in a mapping workbook needs to have a 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. 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 consists 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

warning

This component is designed and tested to work with .xlsx files produced with Microsoft Excel. Files created or modified with other software (such as LibreOffice Calc) will often work, but may also contain subtle differences in data or file structure that are not accounted for. Therefore, the reliability of the component and accelerators cannot be fully guaranteed when using files that have been created or modified with software other than Microsoft Excel.

Functions

lookup(mappingName:string, lookupValues:array): Record<string, string>

Gets mapping values previously loaded into the Content Store using the Load Mapping component.

mappingName is the name of the mapping in which to lookup the values. The mapping name refers to the mapping name under which the mapping is stored in the Content Store. It is the combined mapping name, which consists of the workbookName without the extension and the sheetName divided by a -. Note that all names in the mapping name are lowercased.

lookupValues is an array of values that are used to lookup the mapping. The order of the values in the array should correspond to the order of the keys in the mapping. For example, if the mapping has two keys, name and lastName, the lookupValues array should contain two values, the first value being the value of the name key, and the second value being the value of the lastName key.

Example mapping Excel sheet:

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

Example of using lookup:

// Using lookup in code component
async function main() {
  const person = await lookup("mappingexample-sheet1", ["Morgan","Freeman"]);
  send(person);
}

main()
.then(() => {
  done();
})
.catch((e) => done(e));

getMappingKeys(mappingName:string): Promise<string[]>

Gets the keys of a mapping.

lookupByKey(lookupKey:string): Record<string, string>

Gets mapping values by their key. As opposed to the lookup function, this function requires the full lookup key.

lookupByField(mappingName:string, query:Record<string, string>): Record<string, string>

Gets a mapping using only one key in case there are multiple.

Example of using getMappingKeys and lookupByKey:

async function main() {
  const keys = getMappingKeys("mappingexample-sheet1");
  for await(const key of keys) {
    const person = lookupByKey(key);
  }
}

main()
.then(() => {
  done();
})
.catch((e) => done(e));

Configuration

Path

Path to the file. The path can be overwritten by sending { path: "/path/to/file.xlsx" } to the input. The local file system or a cloud storage can be used.

Example:

• File system: ../test.xlsx
• Cloud storage: <cloud-storage>//test.xlsx

Delete existing mappings

When checked, existing mappings will be deleted based on the mapping name. You can override this setting by sending { deleteExistingMappings: true } to the input.

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. You can override this setting by sending { trimCell: true } to the input.

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.

Inputs | Outputs

Load Mapping

Input

Output

Error

00 0 (ilsyxu266)

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"
  "deleteExistingMappings": true,
  "trimCell": true
}