Teradata FastLoad Skip Header Line

Teradata FastLoad can be used to load CSV/TSV or other delimited files into database. Refer to article Teradata FastLoad - Load CSV File for more details about how to load CSV into Teradata.

This article shows how to skip header line or multiple lines in the input file. 

Example CSV file

Create a sample CSV file named test_fastload2.csv with the following content:

ID, Attr, Value, CreatedDate, Comments
1,A,10,2020-09-13,This is a comment without comma.
2,B,20,2020-09-13,This is a comment without comma.
3,C,30,2020-09-13,This is a comment without comma.
4,D,40,2020-09-13,This is a comment without comma.

Skip header line

Create a FastLoad script file named load-csv2.fastload. The content looks like the following:

ERRLIMIT 1;
LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
DROP TABLE FASTLOAD_CSV2;
DROP TABLE FASTLOAD_CSV2_ERR_1;
DROP TABLE FASTLOAD_CSV2_ERR_2;
CREATE SET TABLE FASTLOAD_CSV2
(
ID VARCHAR(10) NOT NULL,
Col_Attr VARCHAR(1),
Col_Value VARCHAR(10),
CreatedDate VARCHAR(10),
Comments VARCHAR(255)
)
UNIQUE PRIMARY INDEX(ID);

BEGIN LOADING FASTLOAD_CSV2 ERRORFILES FASTLOAD_CSV2_ERR_1, FASTLOAD_CSV2_ERR_2;
	SET RECORD VARTEXT DELIMITER ',';
	RECORD 2;
	DEFINE	ID (VARCHAR(10)), 
			Col_Attr (VARCHAR(1)), 
			Col_Value(VARCHAR(10)), 
			CreatedDate (VARCHAR(10)),
			Comments (VARCHAR(255))
			FILE = test_fastload2.csv;
   
	INSERT INTO FASTLOAD_CSV2 (ID, Col_Attr, Col_Value, CreatedDate, Comments) 
		VALUES (:ID, :Col_Attr, :Col_Value, :CreatedDate, :Comments) ; 
END LOADING;
LOGOFF ;

One additional command is added:

RECORD 2;

The RECORD command defines starting and ending record number of the input data source to process. The above command specifies the process to begin with second record.

Run FastLoad script

Command fastload can be used to run the script file:

fastload < load-csv2.fastload

Output:

...
0010    RECORD 2;

**** 23:06:55 Starting record number set to  : 2
...
**** 23:07:54 END LOADING COMPLETE

     Total Records Read              =  5
      - skipped by RECORD command    =  1
      - sent to the RDBMS            =  4
     Total Error Table 1             =  0  ---- Table has been dropped
     Total Error Table 2             =  0  ---- Table has been dropped
     Total Inserts Applied           =  4
     Total Duplicate Rows            =  0

     Start:   Sun Sep 13 23:07:54 2020
     End  :   Sun Sep 13 23:07:54 2020

Verify the data

Run the following SQL statement to verify the result:

SELECT * FROM TestDb.FASTLOAD_CSV2;

The result set looks like the following screenshot:

Load first N records only

To load first N records only, RECORD command can be updated accordingly.

For example, the following command specify the process to load 3 records only (2, 3, 4).

RECORD 2 THRU 4;

Similarly, the following command loads only 4 records (assuming there is no header line in the source file):

RECORD THRU 4;

References

Teradata FastLoad Reference