Background documentationData Format Locate this document in the navigation structure

 

With most Loader commands for importing and exporting data, you can specify the external data type and the data format that the relevant field values in the data stream have or should have. The specified data format applies to the columns that have the corresponding internal database data type. You can also define which separators and delimiters are to be used. You can specify the data format for the data stream in import and export commands . This data format overrides the corresponding default value or the value that was set using the SET command.

Structure

Syntax Syntax

  1. <column_dataformat_spec> ::=
  CSV <csv_format_spec> ...
| FWV [BINARY] <fwv_format_spec> ...
| FWV BINARY <fwv_format_spec> ... <noheader_spec>
End of the code.

Syntax Syntax

  1. <table_dataformat_spec> ::=
  PAGES
| RECORDS <records_format_spec> ...
End of the code.

Syntax Syntax

  1. <csv_format_spec> ::=
  <separator_spec>
| <delimiter_spec>
| <null_spec>
| <field_format_spec>

<fwv_format_spec> ::=
  <null_spec>
| <int_spec>
| <field_format_spec>

<records_format_spec> ::=
  <field_format_spec> ...

<field_format_spec> ::=
  <bool_spec>
| <code_spec>
| <date_spec>
| <int_spec>
| <number_spec>
| <time_spec>
| <timestamp_spec>

<noheader_spec> ::=
  NOHEADER <unsigned_integer>

<separator_spec> ::=
  SEPARATOR '<character>'

<delimiter_spec> ::=
  DELIMITER '<character>'

<bool_spec> ::=
  BOOLEAN '<true_value>/<false_value>'

<int_spec> ::=
  INTEGER HILO
| INTEGER LOHI

<number_spec> ::=
  DECIMAL '/[<digit_grouping_symbol>/<decimal_symbol>/'
End of the code.

You can use these syntax rules in a command for exporting and importing data to specify a range of data formats. Some of these formats will be explained in more detail. If the formats are not specified, Loader uses the respective default values.

CSV, FWV, FWV BINARY, PAGES, RECORDS

These are formats of the data stream:

CSV

CSV (Comma Separated Values) is a format for CHAR data in which a data line must at least be long enough to represent the data. The assignment of a data field of the data stream to a column in the table is determined by the position specification.

FWV

FWV (Fixed Width Values) is a format for plain text values in which a data line in the data stream corresponds to a data record. The assignment between data fields in the data stream and table columns is defined by the position specification.

FWV BINARY

FWV BINARY (Fixed Width Values BINARY) is a format for binary values in which the data fields all have the same fixed length. The assignment between data fields in the data stream and table columns is defined by the position specification. Unlike the FWV format, a data record does not end with an additional line break.

PAGES

Format that specifies that the application data is stored in pages

RECORDS

Database format

See also: CSV, FWV, FWV BINARY, PAGES, RECORDS, Data Stream

<noheader_spec>

Syntax rule noheader_spec is only evaluated for IMPORT TABLE commands if the data format is FWV BINARY. You can use noheader_spec to specify that the data stream has no special header with the length (unsigned_integer) of an individual data record. At the same time, you specify the length of an individual data record in the data stream. If the data stream was generated with EXPORT TABLE, it contains a special header with the length of an individual data record in the data stream. If this header is missing, Loader can use noheader_spec to specify the length of an individual data record. If this rule is missing, Loader calculates the length of an individual data record using the information in the column positions of the import command.

<separator_spec>

You use the syntax rule separator_spec to specify the character used to separate data fields from each other when CSV format is used (separator representation). The default value is comma (,). You have to specify exactly one character of the 7–bit ASCII character set (only 1 byte long).

<delimiter_spec>

You use syntax rule delimiter_spec to specify the character used to delimit data in selections when CSV format is used (delimiter display). The default value is double quotations marks (“). You can specify exactly one character of the 7–bit ASCII character set (only 1 byte long) or no character (no delimiter).

<int_spec>

INTEGER HILO: The current data stream stores integers so that the byte with the lowest valency is stored first, that is, the furthest to the right in the binary number (the big endian).

INTEGER LOHI: The current data stream stores integers so that the byte with the highest valency is stored first, that is, the furthest to the right in the binary number (little endian, byte swap).

<bool_spec>

You can use syntax rule <bool_spec> (<bool_spec> ::= '<true_value>/<false_value>') to define the character string used in data streams to represent BOOLEAN values that are either exported from the database or imported into the database. true_value defines the character string for values that are true (default value: TRUE), false_value defines the character string for values that are false (default value: FALSE). The character strings can be up to 10 characters in length.

This setting is valid for this Loader command. If you do not specify the character strings, either the value set with the SET BOOLEAN command or the default value of the Loader is used.

<number_spec>

You can use the syntax rule <number_spec> (<number_spec> ::= DECIMAL '/[<digit_grouping_symbol>]/<decimal_symbol>/') to specify which characters are used in decimal numbers to group thousands (digit_grouping_symbol: no character or character for structuring thousands, character cannot be a number, default value: no character) and as decimal separator (decimal_symbol: character for separating decimal places, character cannot be a number, default value: period).

This setting is valid for this Loader command. If you do not specify any characters, either the value set with the SET DECIMAL command or the default value of the Loader is used.

Example

Example Example

EXPORT TABLE hotel.room

  CATALOG OUTSTREAM 'room_ddl.catalog'

  DATA OUTSTREAM 'room_csv.data'

    SEPARATOR ','

    DELIMITER '"'

    NULL '?'

    DECIMAL '/./,/'

    BOOLEAN 'TRUE,FALSE'

    TIMESTAMP ISO

    DATE ISO

    TIME ISO

  PACKAGE OUTSTREAM 'room_csv.package'

End of the example.

The generated data file has the following content (excerpt):

"10","double","45","200.00"

"10","single","20","135.00"

"20","double","13","100.00"

Loader Tutorial, Exporting, Importing

More Information