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.
See how it works:
Load Mapping Explainer
To read and use mappings you can use the following functions in the Code component and the HTTP Request component:
lookuplookupByKeylookupByFieldgetMappingKeys
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.
Empty rows are fully ignored
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.
lookupKey is the full key used to identify a specific mapping entry. It is built by combining the mappingName with the values of the mapping key columns, separated by underscores (_).
The lookup key has the following format:
<mappingName>_<keyValue1>_<keyValue2>_...
The order of the values in the lookup key corresponds to the order of the key columns in the mapping.
For example, consider the following mapping:
| *name | *lastName | city |
|---|---|---|
| Morgan | Freeman | Washington DC |
If the mapping name is mappingexample-sheet1, the lookup key for this row is:
mappingexample-sheet1_Morgan_Freeman
The lookup key is stored in the rawId field of the mapping document in the mappings collection. 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));
Handling lookup errors
When processing documents in a main function, you can include the incoming object and lookup values in your own error handling.
For example:
async function main(value) {
const lookupValues = [
value.result.name,
value.result.lastName
];
try {
const person = await lookup(
"mappingexample-sheet1",
lookupValues
);
send(person);
} catch (e) {
log("error", "Mapping lookup failed", {
document: value.result,
mappingName: "mappingexample-sheet1",
lookupValues
});
throw e;
}
}
main(value)
.then(() => done())
.catch((e) => done(e));
This makes it possible to see both the document being processed and the values that were passed to the mapping lookup.
If the error is handled outside main, make sure the incoming object is still available in that scope:
main(value)
.then(() => done())
.catch((e) => {
log("error", "Object with mapping error", value.result);
done(e);
});
Configuration
Inputs | Outputs
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
}