Background documentationCSV, FWV, FWV BINARY Locate this document in the navigation structure

 

CSV (also COMPRESSED), FWV (also FORMATTED) and FWV BINARY (also FORMATTED BINARY) are possible formats for a data stream.

Loader assumes that data streams with plain text values have format CSV. You therefore do not have to specify format CSV. If you do not want CSV format, you need to explicitly specify FWV format or (in the case of binary coded data streams) FWV BINARY format in a command for exporting and importing data. You can specify the required format in commands for exporting and importing using the syntax rules for data format column_dataformat_spec and data stream outstream_csv_spec, instream_csv_spec, outstream_fwv_spec, instream_fwv_spec.

Structure

CSV

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, Loader generates an error message.

Example Example

Data stream customer_csv.data with data at position 1, 2, 3, 4, ...

"3000","Mrs","Jenny","Porter",...

"3100","Mr","Peter","Brown",...

"3200","Company","?","Datasoft",...

Import 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

End of the example.
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 external data type CHAR. The individual data fields are separated by any character (default value of Loader for separator: comma). They can also be encapsulated in any characters (default value of Loader for delimiter: double quotation marks). If there are successive delimiters in a data line, the value is an 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 defined by the position specification in a command for exporting or importing data. This is a relative position within the data record that 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 position 1 to the first column in your list. The position number of each following column is incremented by 1. This 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 of the database must be sorted in ascending order. You can also assign the same input values to different columns or 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 an empty character string.

FWV

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

Example Example

Data stream customer_fwv.data

-3000Mrs----Jenny-----Porter

-3100Mr-----Peter-----Brown-

-3200Company?---------Dataso

Import Command

IMPORT TABLE hotel.customer

DATA INSTREAM 'customer_fwv.data' FWV

  cno       01-05

  title     06-12

  firstname  13-22

  name      23-32

  zip       33-37

  address    38-62

PAGE WITH 80% USAGE

End of the example.
Data Line

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 with a fixed length at the same position. 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 the starting and end position of the byte. The first possible starting position is 1. You can optionally specify an end position. 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

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

Data Lines

FWV BINARY format is largely the same as FWV format. The only difference is that the length of a data record is only defined 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 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 according to the position specification in the command for exporting or importing data.

Position Specifications

Data fields are described by the starting and end positions of the byte. The first possible starting position of a data record is 1. You can optionally specify an end position. 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 to the table columns in any order. The order does not affect the processing speed. The positions do not need to be successive when you export or import data. Loader fills any gaps with blank characters when it exports data.