Data Transfer Designer
The data transfer designer will open, if you:
- Execute the New command from the pop-up menu of the Transfers category and select a Non-XML format option on the first page of the wizard.
- Execute the action Export data or Import data. The selected table will be preset as the source or target table. You must fill in the only transfer settings.
- Select a data transfer design stored in the database and execute the Edit action. The designer will open with the description of the selected transfer.
How to Design a Transfer
The transfer designer is composed of two parts: a wizard that allows you to interactively choose and specify the data transfer parameters and the designer window that displays the transfer information summary and the grid describing the transferred columns.
Wizard Page 1
You must select the basic transfer type. A transfer between the database and a XML file or a transfer between the database and a data file. Only data transfers will be described here, XML transfers are described here. On this page you will get knowledge about creating XML transfer (and other objects) based on XSD scheme or XSL-FO form.
Wizard Page 2
You must specify the data source for the transfer here. You must choose data from the 602SQL database, ODBC data source or data in an external file. The control options in the bottom part will change according to this selection:
- The options for 602SQL data are table or query (checkbox Data source is a VIEW), select the database name (server name), application (scheme) name and the selected object name.
- The options for ODBC data sources are table or query (checkbox Data source is a VIEW), select the server name, scheme name and the selected object name. These names may have different meanings for different ODBC data sources. If no ODBC data source is open, this option will be disabled.
- Choose the format and coding of the data file and select the file (the button with three dots opens the standard window for the selection of the input file).
The last step on this page is (when transferring from a table or a view) the specification of a constraint condition for the transferred records (in the form of ID>100, where ID is the selected table column). If no condition is set, all records will be transferred.
Wizard Page 3
You must specify the data transfer target here. Again, you must choose whether you are importing to the 602SQL database (to an existing or new table), to an ODBC data source or an external file. The control options in the bottom will change according to this selection:
- Specify the server name, schema name and table name (select an existing table from the combobox or enter a new one) for transferring to 602SQL.
- Specify the server name, schema name and table name for transferring to an ODBC data source.
- Specify the format, coding and select the output file (if you choose an existing file, the data transfer will overwrite it after confirmation).
The last step on this page (when the target is a table) is the specification of the time when indexes should be rebuilt. The Incremental update during import choice means that the index is created for each imported record. The choice Rebuild index after import means that the import will be done first and then the index will be created. The second choice is faster for a large data set, but works only if duplicity will not occur when creating the index.
Wizard Page 4
You can specify some parameters for text transfers here:
-
Create header line in the target text file: Check this if you want to insert a header with the column names in the target file.
-
Number of header lines in source file: How many lines of the input file should be ignored (usually the first line contains column names).
-
CSV field separator: Specifies the character used as a separator for fields in the CSV format (usually a comma or a semicolon).
-
CSV quote value: Specifies the character for delimiting fields containing the separator (usually quotation marks).
-
Date format: Text notation of the date format. If you are not satisfied with the choices, you can create your own mask. D means day, M means month, Y means year and C means century, doubling letters means leading zeros, other characters are separators (e.g. mask for 2004-08-25 is CCYY-MM-DD).
-
Time format: Text notation of the time format. If you are not satisfied with the choices, you can create your own mask. H means hours, M minutes, S seconds and F thousand of seconds, others are separators (only lowercase letters).
-
Timestamp format: Text notation of the timestamp format. If you are not satisfied with the choices, you can create your own mask. The characters for individual parts are described above for date and time.
-
Boolean encoding: Specifies how TRUE and FALSE boolean values are entered in the text. If you are not satisfied with the choices, you can create your own by entering your own names for FALSE and TRUE (in this order) and separate them with a comma (maximum length is 5 characters). The NULL value will always be an empty string.
-
Decimal separator: Specifies the character for separating the decimal part of REAL and NUMERIC type numbers (usually a dot or a comma).
-
Write real numbers in semi-logarithmic format: Specifies the notation format of real numbers (e.g. 1.23e2).
-
Accept lines terminated by CR only: Use for Linux formated text files, where lines are terminated with the CR character. Lines are terminated with the CR and LF in Windows.
When you finish the wizard, the actual transfer designer will open. The top part of the window contains summary information about the transfer from the wizard pages with the ability to go back and change the settings (buttons on the right). The contents of the transfer description in the grid depends on the transfer type (see Data transfers - transfer description). The grid contains the default transfer design that can be modified. If you modify the design, change the source or target structure, you may still generate the default design (use the Design / (Re)Create default design command).
You can check syntax by executing the Design / Validate command and start the transfer using the Transfer data command. Transfer progress can be monitored on the status line.
