Teradata FastLoad Skip Header Line

access_time 12 days ago visibility12 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 6 days 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

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 teradata local_offer SQL local_offer teradata-functions

visibility 230
thumb_up 0
access_time 7 months ago

In SQL Server, TRY_CAST function returns a value cast to a specified data type is the cast is successful or null is the cast is not successful.  In Teradata, the equivalent function is TRYCAST. -- return int 1030 SELECT TRYCAST('01030' AS int); -- return 2019-01-01 as it is a valid date ...

local_offer teradata local_offer SQL local_offer teradata-functions

visibility 72
thumb_up 0
access_time 3 months ago

In article   Teradata SQL - Use OREPLACE to Replace or Remove Characters , it shows how to use OREPLACE function in Teradata to replace or remove strings. This page will show one more alternative way of doing that using regular expressions. select oreplace('Kontext is a website for data ...

local_offer teradata local_offer SQL

visibility 7
thumb_up 0
access_time 4 days ago

A procedure contains a number of SQL statements that will be executed in sequence in Teradata. Procedures can be used to return result sets to the invoking clients. In many other databases, it is very easy to return set records to the client in a procedure as SELECT statement can be directly used.

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS