Transfer Variations and Specifications

602SQL Internal Format

Data import and export from/to the internal format offers you the easiest way to create a backup copy of selected data in 602SQL. This data can be moved from one 602SQL instance to another.

The table must exist prior to importing data and have the same structure as the table (or query result) from which the data was exported. It is recommended to export the table definition along with the table contents, and import both at the same time.

The internal file format can transfer all types and structures from 602SQL.

Data export and import does not modify absolute record numbers in the table. The contents of delete records will not be exported. When you import data, it is recommended to discard all deleted records in a table.

Data files in the 602SQL internal format have the .TDT extension.

Transfer from 602SQL to 602SQL

A special data transfer case is the transfer from 602SQL to 602SQL. This operation will take column values (and the values calculated from the columns) from a certain set of records and insert these values to the specified table. The same columns are taken from each record from the set, and one new record is created in the target table.

A simple example is the creation of a table copy in the same application. The copy contains the columns and records of the source table specified in the transfer design.

602SQL Data Modification

Data modification is an operation that changes column values executed on a certain number of columns in a table. The same operation is performed with each record column.

Data modification is performed when both the source and target of the transfer is the same 602SQL table (or the source is a query from the same table).

Text Formats and Parameters

602SQL supports data transfer in two text formats:

CSV (Comma Separated Values) format: Each record is on a separate line and the values of columns are separated with a special character (usually a comma).
Column format: Each record is on a separate line and the values of columns are aligned in columns of a fixed length.

If a column value in the CSV format contains the separator character, it will be enclosed in delimiter characters (usually quotation marks). If this delimiter character is in a column value, it will be doubled.

Data in columns must be separated with blank spaces, not tabulators or other characters. If the column is separated with tabulators, it is a variation of the CSV format.

If the data exported to the text format should be imported back into 602SQL, it is recommended to put a header on the first line with the column names. The transfer designer is able to automatically generate a suitable transfer design according to this header (data in the header must be separated by at least one blank space for the column format). The header line must be skipped when importing data.

When you are exporting a table (or query result) that contains a text type column from 602SQL to a text file, line separators included in the text will be replaced with blank spaces in order to maintain the the file format (one line = one record). Lines in the exported text file (CSV) may be very long due to the text size. Line length has no effect on data imported into 602SQL. Only the beginning of the text delimited by the header position is exported for the column format.

All column types except for variable-length types (CLOB) can be exported into text formats.

If the exported column has a NULL value, it will be exported as an empty string (two separators following each other will be in the CSV text file).

Time and Date Format

The optional format of time and date must be described in more detail. These formats are described by a "mask" which is composed of characters that define the different parts of the date and time (D means day, M means month, Y means year and C means century for date, h means hours, m minutes, s seconds and f thousand of seconds for time) and separators (e.g. dot for date, colon for time). The user can choose the format from one of the most used formats, or defines a new format by entering the format "mask" in the box. The format is a part of the transfer design. If there is no format suitable, make use of the conversion functions Date2str or Time2str in the design. These functions allow you to adapt the Windows general formats.

These rules apply when reading a text file:

The maximum number of digits to be read at a time is equal to the maximum number of same consecutive characters in the mask, unless a separator is found first. Therefore, it is better to use longer masks for import (e.g. when the date is expressed by one or two numbers, it is better to use the DD notation in order to read the values correctly).
Blank spaces are skipped, other characters are skipped only if that same character corresponds with them in the mask.
These rules apply for writing to a text file:
The complete part of the entry will always be written (day, hour, etc.), and the number of consecutive characters in the mask is skipped only if needed. If the group of same characters in the mask is longer than the number of characters of the entry being written, it is appended with zeroes from the left. If you do not want to export the values with zeroes, you must use a shorter mask in the format settings.
Other separators from the mask are written to the output.

DBF Format

The DBF format provides data exchange between systems like dBase, FoxPro, Clipper, etc. Data is stored in one or two files. The second file contains memo field values. The format of the first file is fixed, but the second has many variations according to the target system and version.

602SQL supports memo field values for dBase IV and FoxPro 2.0. These formats are accepted by many systems.

The DBF format column names have a maximum length of 10 characters. Column names are truncated to 10 characters when exporting data into DBF. You must keep this in mind when importing the data back into a table with the same structure, since the designer will not find the relation between these two different column names and these columns will not be present in the default design.

Data Transfer Designer

Data Transfers

Data Transfer Usage Examples