CSV, FWV, FWV BINARY

Definition

CSV (also COMPRESSED), FWV (also FORMATTED) and FWV BINARY (also FORMATTED BINARY) are possible formats for a data stream and are explained in more detail below.

Use

The Loader assumes that data streams with plain text values have the format CSV. Hence, you do not have to specify the format CSV. If you do not want the CSV format, you must explicitly specify the FWV format or (in the case of binary coded data streams) the FWV BINARY format in a command for exporting and importing data.

You can specify the format you want in commands for exporting and importing using the syntax rules for the data format column_dataformat_spec and the data stream outstream_csv_spec, instream_csv_spec, outstream_fwv_spec, instream_fwv_spec.

See also:

Data Format, Data Stream

CSV (COMPRESSED)

CSV (Comma Separated Values) is a data stream format that can only be used for source data streams with plain text values. If you do not specify a format in a command for exporting or importing data and the data is not in plain text, the Loader generates an error message.

customer.data

Position no.

1      2     3       4       ....

"3000","Mrs","Jenny","Porter",...
"3100","Mr","Peter","Brown",...
"3200","Company","?","D
atasoft",...

The data fields do not have a uniform format but are separated by commas:

IMPORT TABLE command:

IMPORT TABLE hotel.customer
DATA INSTREAM 'customer_csv.data'
  cno       1
  title     2
  firstname 3
  name      4

  
zip       5
  
address   6
PAGE WITH 80% USAGE

Data Lines

In CSV format, a data line must at least be long enough to map the data. The length of the individual data fields and the total length of the data lines can vary. Each data line ends with a line break.

When data is exported, a data line in a source table with this format corresponds to exactly one data record in the target data stream.

When data is imported, a data line in a source data stream with this format corresponds to exactly one data record in the target table.

Data Fields

Data fields in the data streams must always have the external data type CHAR.

The individual data fields are separated by a freely selectable character (default value of the Loader for separator: comma). They can also be encapsulated in freely selectable characters (default value of the Loader for delimiter: double quotation marks).

If delimiters follow each other directly in a data line, then the value is the empty character string. If the relative position is larger than the number of values in the line, the value is also the empty character string.

Position Specification

The assignment of a data field of the data stream to a column in the table is determined by the position specification in a command for exporting or importing data. This specifies a relative position within the data record. It therefore specifies which value in a data record is to be imported into or exported from a specific column.

When you export data from the database to a data stream, assign the position 1 to the first column in your list. The position numbers of the following columns rise by 1 each time. This also means that each position number can only be assigned once.

When you import data from a source table, the data fields in the target table in the database must be sorted in ascending order. You can also assign the same input values to different columns or even omit positions. If consecutive delimiters are detected or if the relative position is greater than the number of values in the line, the value is the empty character string.

FWV (FORMATTED)

FWV (Fixed Width Values) is a data stream format that can only be used for source data streams with plain text values. As this format is not the default value of the Loader, it must be explicitly specified in an export or import command.

customer.data

Position no.

1 2 3 4 5 6 7 8 9 ....

- 3 0 0 0 M r s - - - - J e n n y - - - - - P o r t e r
- 3 1 0 0 M r - - - - - P e t e r - - - - - B r o w n -
- 3 2 0 0 C o m p a n y ? - - - - - - - - - D a t a s o

The data fields have a uniform format.

IMPORT TABLE command:

IMPORT TABLE hotel.customer
DATA INSTREAM FILE 'customer.data' FWV
  cno       01-05
  title     06-12
  firstname 13-22
  name      23-32
  zip       33-37
  address   38-62
PAGE WITH 80% USAGE

Data Lines

A data line in a data stream corresponds to a data record that you want to export or import.

All data lines have the same fixed length. Each line in the data stream contains the individual data fields at the same position with a fixed length. Each data line ends with a line break.

Data Fields

The data fields in the data stream are assigned to the table columns using the position specification in a command for exporting or importing data.

Position Specification

The data fields in the data stream are described using their byte start and end position. The first possible byte start position is 1. Specifying an end position is optional. If an end position is not specified for a data field, it has a length of 1.

The data fields in the data stream can be assigned to table columns in any order. The order does not affect the processing speed.

FWV BINARY (FORMATTED BINARY)

FWV BINARY (Fixed Width Values BINARY) is a data stream format that enables binary values to be exported and imported. As this format is not the default value of the Loader, it must be explicitly specified in an export or import command.

Data Lines

The FWV BINARY format is largely the same as the FWV format. The only difference is that the length of a data record is only determined by the total length of the individual data fields.

Unlike the FWV format, a data record does not end with an additional line break.

Data Fields

The data fields can contain special characters and all have the same fixed length. This also applies to the individual data records.

The data fields in the data stream are assigned to the table columns in accordance with the position specification in the command for exporting or importing data.

Position Specifications

Data fields are described by their byte start and end positions. The first possible byte start position of a data record is 1. Specifying an end position is optional. If you do not specify an end position, the corresponding data field has a length of 1.

The data fields of the data stream can be assigned in any order to the table columns. The order does not affect the processing speed.

The positions do not need to follow each other directly when you export or import data. When it exports data, the Loader fills any gaps with blank characters.

See also:

PAGES, RECORDS