After you chose your export format and general data options, you can provide source-to-target table and/or field mappings. Table and field mappings interface is similar: you can save them to file and load them from file, rearrange items, use different utilities for batch changing target attributes etc.
Field Mappings
Field mappings setup is available in the second export step:
- when exporting one dataset (a table or a SQL query result);
- when exporting multiple datasets, as nested mappings inside each table-to-table pair (see below).
Here, you specify source-to-target field mappings, i.e. the correspondence between source and target fields / columns. Left column contains a list of source fields or dynamic expressions, and the second column contains a corresponding list of the target fields. Next, additional target attributes may follow depending on the target table (file) type, e.g. column type, size etc. The right-most column can be filled with file names expressions. For each source entry, you must fill either target field name, or target file name expression, or both.
Also, here you specify the key fields for UPDATE, DELETE, MERGE commands when exporting data to databases or SQL script.
Field mappings can be saved to or loaded from a field mappings file. Use the field mappings files whenever you need the same field mappings to be used more than once.
If you skip this step, the list of the source columns (fields) to export and the target field names are determined automatically using selected format related options and data range options. Automatic determination of the target field names may be not desirable, for example, when using Append export mode and the target object (table or file) already exists.
Calculated fields
As noted above, you can export not only data from physical fields/columns of the source dataset, but also results of dynamically calculated formulas, or, in other words, calculated fields.
To add a calculated field to the mappings, click Add button. In the added field mapping row, fill the left column with the dynamically calculated expression, and fill the rest of columns accordingly.
The expression must be declared as a parameter of vle_expr macro, i.e. inside the parentheses following vle_expr keyword; this will tell the application to interpret the construction as expression but not the field name. See the image below.
Totals expressions
For some target formats like Excel (XLSX), PDF, or HTML, you can define a Totals expression for every needed target field.
For example, you are exporting a list of customer orders, and want to add a total sum of all orders in the bottom of 'ORDER_SUM' column. To do this, activate the corresponding cell in the list of mappings, and fill it with the formula like this: vle_expr(dataset_sum(1, 0, 'ORDER_SUM')).
The result of such an expression will be placed in the corresponding column of the target, in a row, following the last exported row.
Target file name expressions
When the target file name expression (in one of the right-most columns of the mapping grid) is specified, the corresponding source field data will be exported into individual files. This is especially useful for BLOB/CLOB fields, but can be applied for othe fields too. Be sure to specify expression, which returns a unique file name, for example:
vle_expr(extract_file_path(target_file_name(1)) + 'notes' + to_string(dataset_field_val(1, 'PaymentId')) + '.txt')
(here, we use value of the unique PaymentId field to ensure we get a unique file path for each record)
Attention! There are no overwrite prompts for these files.
Example of SQL Server to Oracle field mappings:
Notes
- Field mappings can have different target attributes for different output formats; for example, for Database output format, it is possible to specify the field type, field size etc. All available attributes are described here.
- After manually changing the field mappings, if you go back to the previous step and change the source or significant export options, your changes in mappings will be discarded and default mappings will be created. To prevent losing your changes, save/load the mappings to/from file.
- In some cases, when your export operation does not involve changes in the target structure, the most of the target attributes will be read-only. This can be, for example, when you are exporting data to existing database table in Append or Update export mode.
- When exporting multiple datasets, you can edit nested field mappings for every pair of table mappings (see below).
Table Mappings
Table mappings setup is available in the second export step, when exporting multiple datasets (tables and/or SQL query results).
Here, you specify source-to-target table mappings, i.e. the correspondence between the source datasets and target files or tables.
If you click a '...' button in Transformation column, you will be able to specify nested column mappings and some overrides of previously specified options like export mode etc. for each table mapping pair.
Table mappings can be saved to or loaded from a table mappings file. Use the table mappings files whenever you need the same table mappings to be used more than once.
If you skip this step, the table mappings are built automatically by the application using source names and, to the extent possible, naming rules of target database or file system.
Example of SQLite to Excel table mappings:
Notes
- Table mappings can have different target attributes for different output formats; for example, for HTML output format, a document title attribute is available. Depending on current application context, some target attributes are prepared automatically and cannot be changed manually. All available attributes are described here.
- After manually changing the table mappings, if you go back to the previous step and change the source or significant export options, your changes will be discarded and default mappings will be created. To prevent losing your changes, save/load the mappings to/from file.
See also
