Importing and Exporting Fixed Width Data
Each column/field in fixed-width text data contains a certain number of character positions. Use a Greenplum Database custom format for fixed-width data by specifying the built-in formatter functions fixedwith_in (read) and fixedwidth_out (write).
The following example creates an external table that specifies the file protocol and references a directory. When the external table is SELECTed, Greenplum invokes the fixedwidth_in formatter function to format the data.
CREATE READABLE EXTERNAL TABLE students (
name varchar(20), address varchar(30), age int)
LOCATION ('file://<host>/file/path/')
FORMAT 'CUSTOM' (formatter=fixedwidth_in, name='20', address='30', age='4');
The following options specify how to import fixed width data.
-
Read all the data.
To load all of the fields on a line of fixed-width data, you must load them in their physical order. You must specify
= for each field; you cannot specify a starting and ending position. The field names that you specify in the FORMAT options must match the order in which you define the columns in the CREATE EXTERNAL TABLE command. -
Set options for blank and null characters.
Trailing blanks are trimmed by default. To keep trailing blanks, use the preserve_blanks=on option. You can reset the trailing blanks option back to the default by specifying the preserve_blanks=off option.
Use the null='null_string_value' option to specify a value for null characters.
-
If you specify preserve_blanks=on, you must also define a value for null characters.
-
If you specify preserve_blanks=off, null is not defined, and the field contains only blanks, Greenplum writes a null to the table. If null is defined, Greenplum writes an empty string to the table.
-
Use the line_delim='line_ending' option to specify the line ending character. The following examples cover most cases. The E specifies an escape string constant.