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 to 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 SQL query result).
Here, you specify source-to-target field mappings, i.e. the correspondence between the source and target fields / columns. The 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 contain different target attributes for different target formats. When you are exporting data to databases, many target columns attributes are available: column names, types, default values (if supported), primary key flags (if supported), column descriptions (e.g. comments in Oracle or PostgreSQL) etc. But when you export data to file formats like Excel or HTML, much fewer target attributes are available.
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 a practical example of calculated fields exporting.
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)
Please read a practical example of using that feature.
Attention! There are no overwrite prompts for these files.
Example of dBase to Firebird 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.
- 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 Exportizer Enterprise 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 tables.
Table mappings contain different target attributes for different target formats.
For each mapping entry, there are nested transformation options available: field mappings and some overrides of previously specified options like export mode etc. You can edit them or leave defaults.
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 and field 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 Firebird to SQLite table mappings:
Example of DBF to SQLite table and nested field mappings:
Example of Excel to SQLite table and nested column 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 in mappings will be discarded and default mappings will be created. To prevent losing your changes, save/load the mappings to/from file.
See also
