Teradata FastLoad with Optional Quotes

access_time 3 months ago visibility157 comment 0

In article Teradata FastLoad - Load CSV File, it shows how to load CSV into Teradata. The input file is very basic CSV file. This article expands on that to provide examples about loading CSV files with optional quoted fields to skip delimiters. It also shows how to load files with multi-character quotes. 

Example CSV file

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

ID, Attr, Value, CreatedDate, Comments
1,A,10,2020-09-13,"This is a comment with 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.

Column 'Comments' in the first record is double quoted as there is a comma in the content. 

Optional double quoted fields

FastLoad command SET RECORD has options available to specify quote characters. It also provides options about mandatory and optional quotes. 

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

LOGON 192.168.119.128/DBC,DBC;
DATABASE TestDb;
DROP TABLE FASTLOAD_CSV3;
DROP TABLE FASTLOAD_CSV3_ERR_1;
DROP TABLE FASTLOAD_CSV3_ERR_2;
CREATE SET TABLE FASTLOAD_CSV3
(
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_CSV3 ERRORFILES FASTLOAD_CSV3_ERR_1, FASTLOAD_CSV3_ERR_2;
	SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '"';
	RECORD 2;
	DEFINE	ID (VARCHAR(10)), 
			Col_Attr (VARCHAR(1)), 
			Col_Value(VARCHAR(10)), 
			CreatedDate (VARCHAR(10)),
			Comments (VARCHAR(255))
			FILE = test_fastload3.csv;
   
	INSERT INTO FASTLOAD_CSV3 (ID, Col_Attr, Col_Value, CreatedDate, Comments) 
		VALUES (:ID, :Col_Attr, :Col_Value, :CreatedDate, :Comments) ; 
END LOADING;
LOGOFF ;

SET RECORD command is set as this:

SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '"';

Quote character is specified as double quotes (") and it is optional. If all fields are all quoted, the value can be specified as YES:

SET RECORD VARTEXT DELIMITER ',' QUOTE YES '"';

Run FastLoad script

Command fastload can be used to run the script file:

fastload < load-csv3.fastload

Output:

...
0008    SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '"';

**** 23:30:14 Now set to read 'Variable-Length Text' records
**** 23:30:14 Delimiter character(s) is set to ','
**** 23:30:14 Field values are optionally quoted with '"'
**** 23:30:14 Command completed successfully

**** 23:06:55 Starting record number set to  : 2
...

**** 23:30:14 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:30:14 2020
     End  :   Sun Sep 13 23:30:14 2020

Verify the data

Run the following SQL statement to verify the result:

SELECT * FROM TestDb.FASTLOAD_CSV3;

The result set looks like the following screenshot:


Multi-characters quotes

FastLoad can also handle fields are quoted with multiple character. For example, column 'Comments' in the first record is quoted with string '"|"'.

ID, Attr, Value, CreatedDate, Comments
1,A,10,2020-09-13,"|"This is a comment with 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.

To load this file, SET RECORD command can be changed to the following:

SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '"|"';

Run the FastLoad script and it will parse the command differently:

0008    SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '"|"';

**** 23:35:19 Now set to read 'Variable-Length Text' records
**** 23:35:19 Delimiter character(s) is set to ','
**** 23:35:19 Field values are optionally quoted with '"|"'
**** 23:35:19 Command completed successfully

Different characters for opening and closing quotes

FastLoad can also load delimited files with different opening and closing quote characters.

For example the following file is optionally quoted by '[...]'.

ID, Attr, Value, CreatedDate, Comments
1,A,10,2020-09-13,[This is a comment with 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.

Change SET RECORD command to the following:

SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '[' ']';

The output for SET RECORD command looks like the following lines:

0008    SET RECORD VARTEXT DELIMITER ',' QUOTE OPTIONAL '[' ']';

**** 23:40:23 Now set to read 'Variable-Length Text' records
**** 23:40:23 Delimiter character(s) is set to ','
**** 23:40:23 Field values are optionally quoted with
              opening quote '[' and closing quote ']'
**** 23:40:23 Command completed successfully

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 teradata local_offer fastload local_offer teradata-utilities

visibility 80
thumb_up 0
access_time 3 months ago

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.  Create a ...

local_offer teradata

visibility 638
thumb_up 0
access_time 4 years ago

This post summarizes the tutorials I created in the past about Teradata. It is trying to help you gain the basic SQL skills with Teradata especially if you are a new beginner or you just move from SQL Server or Oracle. It also includes some guides about setting up your own Teradata server (latest Teradata Express version TDExpress16.10.00.03_Sles11_40GB.7z).

local_offer teradata local_offer SQL

visibility 198
thumb_up 0
access_time 3 months ago

Function CURRENT_TIMESTAMP can be used to retrieve the current timestamp: SELECT CURRENT_TIMESTAMP; Sample output: 20/09/2020 20:55:35.390000-04:00 Function CAST can be used to convert TimeStamp to DATE. SELECT CAST(CURRENT_TIMESTAMP AS DATE) Sample output: 20/09/2020 SELECT ...

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS