Fixed-length format definition example

Following is a sample data record:

20011228YF2001122814313425 Forest St Marlborough  MA017525083828200600

The record is defined by the following column layout:

• Columns 1 - 8: Date format YYYYMMDD. Null when value is '99991231'.
• Column 9: Boolean Y/N. Null when value is space (' ').
• Column 10: Boolean T/F. Null when value is space (' ').
• Columns 11 - 24: Time stamp format YYYYMMDDHHMMSS. Null when value is '99991231000000'.
• Columns 25 - 39: Character address. Null when value is all spaces.
• Columns 40 - 52: Character city. Null when value is '****NULL*****'.
• Columns 53 - 54: Character state. Null when value is '##'.
• Columns 55 - 59: Number postal code. Null when value is all zeros.
• Columns 60 - 68: Character phone. Null when value is all zeros.
• Columns 69 - 72: Number(3,2). For example, 600 is 6.00. Never null.
• Column 73: Newline end of record.

Following is an example of external table definition for this data:

CREATE EXTERNAL TABLE sample_ext (
Col01 DATE ,
Col09 BOOL ,
/* Skipped col10 */
Col11 TIMESTAMP,
Col26 CHAR(12),
Col38 CHAR(10),
Col48 CHAR(2),
Col50 INT4,
Col56 CHAR(10),
Col67 CHAR(3) /* Numeric(3,2) cannot be loaded directly */
)
USING (
dataobject('/home/test/sample.fixed')
logdir '/home/test'
recordlength 72 /* Does not include end of record delimiter */
recorddelim '
' /* This is actually a newline between the single quotes; really not needed because newline is default */
format 'fixed'
layout (
Col01 DATE YMD '' bytes 8 nullif &='99991231',
Col09 BOOL Y_N bytes 1 nullif &=' ',
FILLER CHAR(1) Bytes 1, /* Was col10 space */
Col11 TIMESTAMP YMD '' 24HOUR '' bytes 14 nullif &='99991231000000',
Col26 CHAR(15) bytes 15 nullif &=' ', /* 15 spaces */
Col38 CHAR(13) bytes 13 nullif &='****NULL*****' ,
Col48 CHAR(2) bytes 2 nullif &='##' ,
Col50 INT4 bytes 5 nullif &='00000' ,
Col56 CHAR(10) bytes 10 nullif &='0000000000',
Col67 CHAR(3) bytes 3 /* Cannot load this directly, so insert-select statement used */
) /* end layout */
); /* end external table definition. */
INSERT INTO sampleTable
SELECT
Col01,
Col09,
Col11,
Col26,
Col38,
Col48,
Col50,
Col56,
(Col67/100)::numeric(3,2) as Col67 /* Convert char to numeric(3,2) */
FROM sample_ext ;