Field Mappings File - Database Tour Documentation

Field mappings files contain source-to-target field mappings for export and import operations. Use them whenever you need the same field mappings to be used more than once. They can be also used when exporting data from command line or action files.

It is supported two formats of field mappings files: XML (recommended) and plain text.

XML Format

This format is flexible and extendable and therefore is recommended to use whenever possible.

Such field mappings files can be created either in the Export dialog on the Field Mappings step or manually using a text editor. When creating the files manually please note that they must conform to XML standards and contain byte order marks at the beginning if needed.

The file contains an optional common part and a series of field mappings. The common part may include some technical information like application name and version, and key fields for special cases of exporting data to databases and SQL script.

Each mapping must contain 2 parts: source and target.

The source part must contain a FieldName attribute with the physical name of the source field or a dynamic expression.

The target part can have a variable number of attributes (either FieldName or FileName or both must be filled):

FieldName

Specifies the physical name of the target field (column).

Target formats: All

FieldType

Specifies the type of the target field (column). For SQL databases, it should be a SQL type supported by the target database. For non-SQL databases (e.g. ASCII tables), it should be a type supported by the target database.

Target formats: Database, SQL

FieldSize

Specifies the maximum number of characters for text fields (columns) or total number of digits for fields (columns) holding floating-point numbers.

Target formats: Database, SQL

FieldScale

Specifies the scale (number of digits to the right of the decimal point) for fields (columns) holding floating-point numbers.

Target formats: Database, SQL

FieldNotNull

A value of 1 means that the field will NOT accept NULL values.

Target formats: Database, SQL

FieldDefaultValue

Specifies the default value for the field.

Target formats: Database, SQL

PrimaryKey

Specifies whether the field is a part of the primary key or not.

Target formats: Database, SQL

FieldSQLSpecification

Specifies full SQL specification of the field (without field name). See example below.

Target formats: Database, SQL

FieldDescription

Specifies the field description (currently, Oracle, SQL Server, PostgreSQL, and MySQL databases only).

Target formats: Database, SQL

FileName

Specifies path of file to store contents of the source field. If not empty, the contents of the source field will be saved into a separate file specified by this attribute. The target folder must exist. The file path should be a dynamic expression returning a unique file name, otherwise the file will be overwritten for each exported record. For database images, the file extension will define the target image format, so it is possible to convert the image format. See example below.

Target formats: All

Attention! There are no overwrite prompts for these files.

TotalsExpression

Specifies expression the result of which should be shown in the target file after exporting all source records. It can be a dynamic or constant expression.

Target formats: All, except Database, SQL, DBF

XML Format Example

Example of the Access-to-Oracle field mappings file in XML format:

<?xml version="1.0" encoding="UTF-8"?>

<FieldMappings>
  <Software>Database Tour Pro</Software>
  <KeyFields>PAYMENT_ID</KeyFields>
  <Items>
    <FieldMapping>
      <Source>
        <FieldName>PaymentId</FieldName>
      </Source>
      <Target>
        <FieldName>PAYMENT_ID</FieldName>
        <FieldType>INTEGER</FieldType>
        <PrimaryKey>1</PrimaryKey>
      </Target>
    </FieldMapping>
    <FieldMapping>
      <Source>
        <FieldName>Department</FieldName>
      </Source>
      <Target>
        <FieldName>DEPARTMENT</FieldName>
        <FieldType>VARCHAR2</FieldType>
        <FieldSize>20</FieldSize>
        <FieldDescription>Department Name</FieldDescription>
      </Target>
    </FieldMapping>
    <FieldMapping>
      <Source>
        <FieldName>ActualityDate</FieldName>
      </Source>
      <Target>
        <FieldName>ACTUALITY_DATE</FieldName>
        <FieldType>DATE</FieldType>
        <FieldDescription>Date of Payment</FieldDescription>
      </Target>
    </FieldMapping>
    <FieldMapping>
      <Source>
        <FieldName>PaymentSum</FieldName>
      </Source>
      <Target>
        <FieldName>PAYMENT_SUM</FieldName>
        <FieldType>NUMBER</FieldType>
        <FieldSize>12</FieldSize>
        <FieldScale>2</FieldScale>
        <FieldDescription>Payment Amount</FieldDescription>
      </Target>
    </FieldMapping>
    <FieldMapping>
      <Source>
        
        <FieldName>vle_expr(0.1 * dataset_field_val(1, 'PaymentSum'))</FieldName>
      </Source>
      <Target>
        <FieldName>COMMISSION_SUM</FieldName>
        <FieldType>NUMBER</FieldType>
        <FieldSize>12</FieldSize>
        <FieldScale>2</FieldScale>
        <FieldDefaultValue>0</FieldDefaultValue>
        <FieldSQLSpecification>NUMBER(12, 2) DEFAULT 0<
        <FieldDescription>Commission Amount</FieldDescription>
      </Target>
    </FieldMapping>
    <FieldMapping>
      <Source>
        <FieldName>Notes</FieldName>
      </Source>
      <Target>
        <FieldName>NOTES</FieldName>
        <FieldType>VARCHAR2</FieldType>
        <FieldSize>400</FieldSize>
        <FieldSQLSpecification>VARCHAR2(400)</FieldSQLSpecification>
        <FieldDescription>Notes regarding the payment</FieldDescription>
        
        <FileName>vle_expr(extract_file_path(target_file_name(1)) + 'notes' + to_string(dataset_field_val(1, 'PaymentId')) + '.txt')</FileName>
      </Target>
    </FieldMapping>
  </Items>
</FieldMappings>

Text Format

Such files consist of series of SourceFieldName=TargetFieldName pairs, one mapping per line. It is recommended that field names not contain equal signs. Instead of source field names, dynamic expressions can be used (see example below). Lines with a semicolon at the beginning are ignored as comments. Blank lines are allowed.

Such files can have any extension and can be created in any text editor. When creating the files please include byte order marks at the beginning if needed.

Text Format Example

Example of the field mappings file in text format:

;Source-to-Target mappings for Payments exporting
Department=DEPARTMENT
ActualityDate=ACTUALITY_DATE
PaymentCount=PAYMENT_COUNT
PaymentSum=PAYMENT_SUM
;dynamic expressions are possible:
vle_expr(0.1 * dataset_field_val(1, 'PaymentSum'))=COMMISSION_SUM

Notes
Target FieldName attributes, if contain characters, uncommon for field names in the target database (i.e. spaces etc.), should be enclosed in double quotes or square brackets according to the rules of the target database.