Data Format (SAP Library

Data Format

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 in import and export commands for the data stream. This data format then overrides the corresponding default value or the value that was set using the SET command.

Syntax

<column_dataformat_spec> ::= CSV <csv_format_spec> ...
| FWV [BINARY] <fwv_format_spec> ...
| FWV BINARY <fwv_format_spec> ... <noheader_spec>

<table_dataformat_spec> ::= PAGES
| RECORDS <records_format_spec>

<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> ...

<noheader_spec>          ::= NOHEADER <unsigned_integer>
<separator_spec>         ::= SEPARATOR '<separator>'
<delimiter_spec>         ::= DELIMITER '<delimiter>'
<null_spec>              ::= NULL value representation
<bool_spec>              ::= BOOLEAN '<true_value>/<false_value>'
<code_spec>              ::= code specification
<date_spec>              ::= date specifications
<int_spec>               ::= INTEGER HILO | INTEGER LOHI
<number_spec>            ::= DECIMAL '/[<digit_grouping_symbol>]/<decimal_symbol>/'
<time_spec>              ::= time specifications
<timestamp_spec>         ::= time stamp specifications

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 determined 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 determined 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
NOHEADER <unsigned_integer>	unsigned_integer: length of an individual data record in the data stream See the SQL Reference Manual, Unsigned Integer (unsigned_integer)
SEPARATOR '<separator>' <separator> ::= <character>	separator: character that separates data fields are separated from each other if the CSV format is used (separator representation), default value: comma (,) You have to specify exactly one character that originates from the 7 bit ASCII character set (only 1 byte long). See the SQL Reference Manual, Character (character)
DELIMITER '<delimiter>' < delimiter > ::= <character>	delimiter: character used to delimit data in selections when the CSV format is used (delimiter dispay), default value: double quotations marks (“) You can specify exactly one character that originates from the 7 bit ASCII character set (only 1 byte long) or no character (no delimiter).
BOOLEAN '<true_value>/<false_value>'	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 may have a maximum length of 10 characters. See the SQL Reference Manual, Character String
INTEGER HILO	The current data stream stores integers so that the byte with the lowest valency is stored first, that is, is furthest 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 right in the binary number (little endian, byte swap).
DECIMAL '/[<digit_grouping_symbol>] /<decimal_symbol>/'	digit_grouping_symbol: defines a character or no character for structuring thousands Default value: no character decimal_symbol: defines the character for separating the decimal places Default value: period The character cannot be a number. See the SQL Reference Manual, Character (character)

Examples

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'

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

"10","double","45","200.00"
"10","single","20","135.00"
"20","double","13","100.00"

Loader Tutorial, Structure link Exporting, Importing

Explanation

You can use these syntax rules within a command for exporting and importing data to specify a range of data formats: Some of these formats shall be explained in more detail at this point. If the formats are not specified, the Loader uses the respective default values, which are specified further up in the table.

CSV, FWV, FWV BINARY, PAGES, RECORDS

These are formats of the data stream.

CSV, FWV, and FWV BINARY

PAGES and RECORDS

<noheader_spec>

The 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 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 the EXPORT TABLE, it contains a special header with the length of an individual data record in the data stream. If this header is missing, the Loader can use noheader_spec to specify the length of an individual data record. If this rule is missing, the Loader calculates the length of an individual data record using the information on the column positions in the load command.

<bool_spec>

You use the syntax rule bool_spec to specify the character strings that represent the BOOLEAN values in data streams to be exported from a database instance or to be imported into a database instance. This setting is valid for this Load command. If you do not specify the character strings, either the value set with the SET BOOLEAN command or the Loader’s default value is used.

<int_spec>

You use the syntax rule int_spec to define the binary representation of integers in data streams. You can only do this for data streams with the FWV BINARY format. This specification is ignored during the export into data streams or the import from data streams in another format.

If the representation specified for a data stream does not match the current computer, the values are adjusted before being exported to the data stream or imported into the database.

<number_spec>

You use the syntax rule number_spec to specify which characters are to be used in decimal numbers to group thousands and separate integers from decimal places. This setting is valid for this Load command. If you do not specify any characters, either the value set with the SET DECIMAL command or the Loader’s default value is used.

See also: