1.21 COPY : Loading FIXED width files.

COPY : Loading FIXED width files.

To load fixed width files we should use the FIXEDWIDTH parser option.

We must specify the COLSIZES option values to specify the number of bytes for each column.

The last record in a fixed-width data file must include a record terminator to determine the end of the load data.

Following options cannot be used with fixed width files.

• DELIMITER
• ENCLOSED BY

• ESCAPE AS

• TRAILING NULLCOLS

(Note that we can use NULL metadata option with FIXED WIDTH).

Following example shows a fixed width file and how this gets loaded to the table.

Note that 1st column is of width 19 bytes, 2nd of 3 bytes,3rd is 10 bytes and last one is 6 bytes.

<> /home/sukul1 $ cat employeedet3.dat

RAHUL,DRAVID       45 2010-01-01100000

SAURAV,GANGULY     1012010-10-01200000

<> /home/sukul1 $

sukul1=> COPY USER_30_DAY_TABLES.EMPLOYEE_1

sukul1-> (

sukul1(> NAME,

sukul1(> AGE,

sukul1(> JOINING_DATE FILLER VARCHAR(10),

sukul1(> JOINING_YEAR AS TO_NUMBER(TO_CHAR(TO_DATE(JOINING_DATE,'YYYY-MM-DD'),'YYYY')),

sukul1(> SALARY

sukul1(> )

sukul1-> FROM LOCAL '/home/sukul1/employeedet3.dat'

sukul1-> FIXEDWIDTH COLSIZES(19,3,10,6)

sukul1-> DIRECT;

 Rows Loaded

-------------

           2

(1 row)

sukul1=> select * from USER_30_DAY_TABLES.EMPLOYEE_1;

        NAME         | AGE | JOINING_YEAR | SALARY

---------------------+-----+--------------+--------

 RAHUL,DRAVID        |  45 |         2010 | 100000

 SAURAV,GANGULY      | 101 |         2010 | 200000

(2 rows)

Note that if we dont specify correct lengths, the records will be rejected.