Exporting Data to Text |
Data may be exported from the database to form a structured text file. This section describes the different text file formats that the Data Transfer tool can produce. Consider the following settings affecting the format of the text file before exporting data.
Row Delimiter: Determines the type of character that signifies a break between the rows of a table. Possible characters: {CR/LF} (Carriage return / line feed. In Windows applications, a new line in the text is normally stored as a pair of CR LF characters. In Unix applications, a new line is normally stored as a LF character. Some applications use only a CR character to store a new line), {CR}, {semicolon} (;), {comma} (,),{tab}, {vertical bar} (|), (semicolon}{LF}, or {comma}{LF}.
Column Delimiter: Determines the type of character that signifies a break between columns in each row. Possible characters: semicolon, comma, or vertical bar.
¡PText Qualifier: Determines how each tuple of any data type except BINARY, LONGVARBINARY, or numeric data types (integer, smallint, serial, decimal, double, float) is enclosed. Possible values: none, single quote, or double quote.
Binary Qualifier: Determines how each tuple of BINARY or LONGVARBINARY type data is enclosed. Possible values: none, single quote, or double quote.
Binary Padding: A character appended to each tuple containing binary data.
Fixed Field: Instead of using a row delimiter, the text file may be formatted with fixed fields. This means a number of spaces, or fields, defines each column.
Include column name: The first line in a text file may be used to define the column names. The format is "column1"."table name"."owner name";"column2"."table name"."owner name"; etc. In this case the column delimiter is set to semicolon (;).
Include table schema: The first line in a text file may be used to define the column schema (or the second line if the first line was used to define column names). The format is data type(scale,precision);data type(scale,precision) etc. In this case the column delimiter is set to semicolon (;).
Use NULL to display null data: Columns that contain no data output "NULL".
File link name for FILE type data: The file name for system or user file objects is displayed.
Use escape character "/": This character is used when qualifiers or delimiter characters appear in the data. If the data contains a reserved character, the reserved character will be enclosed by an escape character (/).
Use temp files to store LONGVARBINARY or LONGVARCHAR type data column content: BLOB data is stored as a separate, linked file (as a file object), and the name of the file containing the BLOB is displayed.
Data can be exported by selecting individual columns from a table, or through a valid SQL SELECT statement. For more information on SQL syntax, refer to the SQL Command and Function Reference.
Related topic: