Netezza Fixed-Width File Loading and Examples

  • Post author:
  • Post last modified:February 28, 2018
  • Post category:Netezza
  • Reading time:4 mins read

Fixed width text files are special cases of text files where the format is specified by column widths, pad character and left or right alignment. In this format column width are in terms of units of characters. In this post we will learn about Netezza Fixed-Width file loading.

netezza fixed-width file loading

Fixed-Width File Overview

All data is a series of byte-sequences and has an associated data type, used here as a conceptual or abstract attribute of the data. Fixed-length format files use ordinal positions, which are offsets to identify where fields are within the record of the file.

There are no field delimiters, and there may or may not be end-of-record delimiter. Usually Netezza requires end of record delimiter if you are reading entire row with using ordinal positions. Data in fixed width format do not use any date delimiter as the file is fixed-length and does not require delimiter It will not accept space in between data. If you want proper date format then you have to explicitly type convert the data and store in database table.

Netezza Fixed-Width File Loading

Loading fixed format data file into the Netezza database requires that you define the target data type for the field, as well as the location within the record. You can skip the fields in the fixed-width format using ‘filler’ specification in layout definition.

The order of fields in the data file must match the order of the columns in target table, or an external table definition must be defined, which specifies the order of the fields as database columns. Using an external table definition in combination with an insert-select statement allows field order to be changed and insert data to database table.

Read;

Netezza Fixed-width File Loading Example

Below is the example that create external table on top of fixed-width file and load it to database table.

–Create external table on top of fixed-width file

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 as 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 /* We cannot load this directly, so we use an insert-select */
) /* end layout */
); /* end external table definition. */

–Script Example For Loading Data Using Fixed-Length Format

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 ;

You can even automate all these steps by encapsulating the above mentioned steps in shell scripts. Feel free to comment on the any other methods you know.

Tags: ,