Export CSV File via TPT

Teradata Parallel Transporter (TPT) provides rich functions to load data into Teradata and to export data. In article Load CSV into Teradata via TPT, it shows how to load CSV files into Teradata. This page provides examples to export data from Teradata to CSV.

Create TPT job

Use TPT wizard GUI tool to create a TPT job that does the following:

1. Logon to Teradata server.
2. Export data from table TestDb.FASTLOAD_CSV to local CSV file named tpt_export.csv.
3. Logoff.

Follow these steps to create the TPT export job.

1) Input job name and description. 

2) Click Next button.

3) Configure job source as the Teradata table.

4) Click Select All button to include all the columns.

5) Click Next button.

6) Configure job destination as local CSV file. Choose Format as DELIMITED.

7) Click Next button.

8) View the summary of the job and click Finish button to complete the wizard.

In TPT wizard main window, the newly created job will show up:

Run TPT job

Click job submit button in the wizard to run the job.

The popup window also contains the TPT script:

USING CHARACTER SET ASCII
DEFINE JOB EXPORT_CSV
DESCRIPTION 'Export table TestDb.FASTLOAD_CSV to a CSV file named tpt_export.csv'
(
	DEFINE OPERATOR W_1_o_EXPORT_CSV
	TYPE DATACONNECTOR CONSUMER
	SCHEMA *
	ATTRIBUTES
	(
		VARCHAR FileName, 
		VARCHAR Format, 
		VARCHAR OpenMode, 
		INTEGER BlockSize, 
		INTEGER BufferSize, 
		INTEGER RetentionPeriod, 
		INTEGER RowsPerInstance, 
		INTEGER SecondarySpace, 
		INTEGER UnitCount, 
		INTEGER VigilElapsedTime, 
		INTEGER VigilWaitTime, 
		INTEGER VolumeCount, 
		VARCHAR AccessModuleName, 
		VARCHAR AccessModuleInitStr, 
		VARCHAR DirectoryPath, 
		VARCHAR ExpirationDate, 
		VARCHAR IndicatorMode, 
		VARCHAR PrimarySpace, 
		VARCHAR PrivateLogName, 
		VARCHAR RecordFormat, 
		VARCHAR RecordLength, 
		VARCHAR SpaceUnit, 
		VARCHAR TextDelimiter, 
		VARCHAR VigilNoticeFileName, 
		VARCHAR VigilStartTime, 
		VARCHAR VigilStopTime, 
		VARCHAR VolSerNumber, 
		VARCHAR UnitType
	);

	DEFINE SCHEMA W_0_s_EXPORT_CSV
	(
		ID_0 VARCHAR(10),
		Col_Attr VARCHAR(1),
		Col_Value VARCHAR(10),
		CreatedDate VARCHAR(10),
		Comments VARCHAR(255)
	);

	DEFINE OPERATOR W_0_o_EXPORT_CSV
	TYPE EXPORT
	SCHEMA W_0_s_EXPORT_CSV
	ATTRIBUTES
	(
		VARCHAR UserName, 
		VARCHAR UserPassword, 
		VARCHAR LogonMech, 
		VARCHAR LogonMechData, 
		VARCHAR SelectStmt, 
		INTEGER BlockSize, 
		INTEGER MaxSessions, 
		INTEGER MinSessions, 
		INTEGER TenacityHours, 
		INTEGER TenacitySleep, 
		INTEGER MaxDecimalDigits, 
		VARCHAR AccountID, 
		VARCHAR DateForm, 
		VARCHAR NotifyExit, 
		VARCHAR NotifyExitIsDLL, 
		VARCHAR NotifyLevel, 
		VARCHAR NotifyMethod, 
		VARCHAR NotifyString, 
		VARCHAR PrivateLogName, 
		VARCHAR TdpId, 
		VARCHAR TraceLevel, 
		VARCHAR WorkingDatabase
	);

	APPLY
	TO OPERATOR
	(
		W_1_o_EXPORT_CSV[1]

		ATTRIBUTES
		(
			FileName = 'F:\tmp\tpt_export.csv', 
			Format = 'DELIMITED', 
			OpenMode = 'Write', 
			IndicatorMode = 'N', 
			TextDelimiter = ','
		)
	)
	SELECT * FROM OPERATOR
	(
		W_0_o_EXPORT_CSV[1]

		ATTRIBUTES
		(
			UserName = 'dbc', 
			UserPassword = 'dbc', 
			LogonMech = 'TD2', 
			SelectStmt = 'SELECT ID,Col_Attr,Col_Value,CreatedDate,Comments FROM TestDb.FASTLOAD_CSV;', 
			TdpId = '192.168.119.128'
		)
	);
);

Verify the exported CSV file

A new file named tpt_export.csv is created with the following content:

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

Create TPT script file

Based on the script generated by the wizard, we can further enhance it.

1) Create a text file named tpt-export.tpt with the content generated by the wizard.

2) Add three attributes to the consumer operator:

VARCHAR OpenQuoteMark,
VARCHAR CloseQuoteMark,
VARCHAR QuotedData

3) In APPLY TO OPERATOR section, add the following values to always quote the attributes:

APPLY
	TO OPERATOR
	(
		W_1_o_EXPORT_CSV[1]

		ATTRIBUTES
		(
			FileName = 'F:\tmp\tpt_export.csv', 
			Format = 'DELIMITED', 
			OpenMode = 'Write', 
			IndicatorMode = 'N', 
			TextDelimiter = ',',
			OpenQuoteMark = '"',
			CloseQuoteMark = '"',
			QuotedData='Yes'
		)
	)

Different characters can be used for quoting. 

4) Save the script file.

Run TPT script

Run the following command to execute the script:

tbuild -f tpt-export.tpt

The output looks like the following:

tbuild -f tpt-export.tpt
Teradata Parallel Transporter Version 16.10.00.03 64-Bit
Job log: D:\Program Files\Teradata\client\16.10\Teradata Parallel Transporter/logs/***-19.out
Job id is ***-19, running on ***
Teradata Parallel Transporter Export Operator Version 16.10.00.03
W_0_o_EXPORT_CSV: private log not specified
Teradata Parallel Transporter DataConnector Operator Version 16.10.00.03
W_1_o_EXPORT_CSV[1]: Instance 1 directing private log report to 'dtacop-***-20652-1'.
W_1_o_EXPORT_CSV[1]: DataConnector Consumer operator Instances: 1
W_1_o_EXPORT_CSV[1]: ECI operator ID: 'W_1_o_EXPORT_CSV-20652'
W_1_o_EXPORT_CSV[1]: Operator instance 1 processing file 'F:\tmp\tpt_export.csv'.
W_0_o_EXPORT_CSV: connecting sessions
W_0_o_EXPORT_CSV: sending SELECT request
W_0_o_EXPORT_CSV: entering End Export Phase
W_0_o_EXPORT_CSV: Total Rows Exported:  4
W_0_o_EXPORT_CSV: Total Rows Discarded: 0
W_0_o_EXPORT_CSV: disconnecting sessions
W_1_o_EXPORT_CSV[1]: Total files processed: 1.
W_0_o_EXPORT_CSV: Total processor time used = '0.203125 Second(s)'
W_0_o_EXPORT_CSV: Start : Mon Sep 14 15:14:43 2020
W_0_o_EXPORT_CSV: End   : Mon Sep 14 15:14:48 2020
Job step MAIN_STEP completed successfully
Job *** completed successfully
Job start: Mon Sep 14 15:14:43 2020
Job end:   Mon Sep 14 15:14:48 2020

The generated file has the following content (with all the fields double quoted):

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

Include header line in export CSV file

Unfortunately TPT export operator doesn't support including header line.

One way to address this issue is to hard code the header line as a record and use UNION to join to the actual data SELECT statement.

References

Teradata Parallel Transporter User Guide