TSV Input Format Fields

The structure of the input records generated by the TSV input format is determined at run time, depending on the data being parsed, and on the values specified for the input format parameters.

The first two input record fields are fixed, and they are described in the following table:

Name	Type	Description
Filename	STRING	Full path of the file containing this entry
RowNumber	INTEGER	Line in the file containing this entry

These two fields are then followed by the fields detected by the TSV input format in the file(s) being parsed. The number, names, and data types of the fields are determined by examining initially the input data according to the values specified for the input format parameters.

The number of fields detected by the TSV input format during the initial inspection phase dictates how the record fields will be extracted from the input data during the subsequent parsing stage.
If a line contains less fields than the number of fields established, the missing fields are returned as NULL values.
On the other hand, if a line contains more fields than the number of fields established, the extra fields are parsed as if they were part of the value of the last field expected by the TSV input format.

Number of Fields

The number of fields in an input record is determined by the input data and by the value of the nFields parameter.

When the "nFields" parameter is set to -1, the TSV input format determines the number of fields by inspecting the first line of the input data, or the first line of the header file specified with the "iHeaderFile" parameter.
As an example, the following TSV file contains a variable number of fields:

Name City AreaCode
Jeff Redmond 425
Steve Seattle 206 98101
Edward Olympia 360

When parsed with the "nFields" parameter set to -1, this TSV file would yield three fields ("Name", "City", and "AreaCode").
In this case, the extra fourth field in the second record would be parsed as part of the third "AreaCode" field, whose value would then be "206 98101".

When the "nFields" parameter is set to a value greater than zero, the TSV input format uses the specified value as the number of fields in the input data. Considering again the previous example file, parsing the file with the "nFields" parameter set to 4 would yield four fields.

Field Names

The names of the fields in an input record is determined by the input data and by the values of the headerRow and iHeaderFile parameters.

When the "headerRow" parameter is set to "ON", the TSV input format assumes that the first line in the file being parsed is a header containing the field names.
In this case, if the "iHeaderFile" parameter is left unspecified, the TSV input format extracts the field names from the header line.
On the other hand, if the "iHeaderFile" parameter is set to the path of a TSV file containing at least one line, then the TSV input format assumes that the specified file contains a header, parses its first line only, and extracts the field names from this line, ignoring the first line of the file being parsed.

If the number of field names extracted is less than the number of fields detected, the additional fields are automatically named "FieldN", with N being a progressive index indicating the field position in the input record.

Considering the previous example file, setting the "headerRow" parameter to "ON" would cause the TSV input format to use the first line of the file as a header containing the field names.
With the "nFields" parameter set to -1, the TSV input format would detect three fields, whose names would be "Name", "City", and "AreaCode".
On the other hand, with the "nFields" parameter set to 4, the TSV input format would detect four fields, named "Name", "City", "AreaCode", and "Field4".

When the "headerRow" parameter is set to "OFF", the TSV input format assumes that the file being parsed does not contain a header, and that its first line is the first data record in the file.
In this case, if the "iHeaderFile" parameter is set to the path of a TSV file containing at least one line, then the TSV input format assumes that the specified file contains a header, parses its first line only, and extracts the field names from this line.
On the other hand, if the "iHeaderFile" parameter is left unspecified, the fields are automatically named "FieldN", with N being a progressive number indicating the field position in the input record.

As an example, the following TSV file does not contain a header line:

Jeff Redmond 425
Steve Seattle 206
Edward Olympia 360

When parsed with the "headerRow" parameter to "OFF", the TSV input format assumes that the first line of the TSV file is the first data record in the file. In this case, the three fields would be named "Field1", "Field2", and "Field3".

Field Types

The data type of each field extracted from the input data is determined by examining the first n data lines, where n is the value specified for the dtLines parameter, in the following way:

If all the non-empty field values in the first n lines are formatted as decimal numbers, then the field is assumed to be of the REAL type.
If all the non-empty field values in the first n lines are formatted as integer numbers, then the field is assumed to be of the INTEGER type.
If all the non-empty field values in the first n lines are formatted as timestamps in the format specified by the iTsFormat parameter, then the field is assumed to be of the TIMESTAMP type.
Otherwise, the field is assumed to be of the STRING type.

Empty field values are returned as NULL values.