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. 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.
When the target file name expression (the right-most column) is specified, the corresponding source field data will be exported into individual files. This is especially useful for BLOB/CLOB fields. 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.
When using dynamic expression instead of source field name, 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.
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.
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.
- 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
