Teradata FastLoad Skip Header Line

access_time 3 months ago visibility77 comment 0

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

info Last modified by Administrator at 3 months ago copyright This page is subject to Site terms.
Like this article?
Share on

Please log in or register to comment.

account_circle Log in person_add Register

Log in with external accounts

Want to publish your article on Kontext?

Learn more

Kontext Column

Created for everyone to publish data, programming and cloud related articles.
Follow three steps to create your columns.


Learn more arrow_forward

More from Kontext

local_offer Azure local_offer teradata local_offer teradata-tool

visibility 1014
thumb_up 0
access_time 4 years ago

This page provides the steps to create a Teradata database in Microsoft cloud computing platform Azure.

local_offer teradata local_offer SQL local_offer teradata-sql-query

visibility 592
thumb_up 0
access_time 9 months ago

In Teradata, Common Table Expression (CTE) is supported as other databases. You can create recursive CTE or use a reference of a CTE to another CTE. However there is a slight differences compared with other databases - The referenced CTE must be present after the referencing CTE. For example, CTE ...

local_offer teradata local_offer SQL local_offer teradata-sql-query

visibility 136
thumb_up 0
access_time 3 months ago

XML data type is commonly supported in Teradata. Together with native XML type, a number of XML functions are added to support extracting values from XML, shredding and publishing JSON, etc.  info The following code snippets use string literal to demonstrate the usage of these functions; you ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS