Export CSV File via TPT

access_time 3 months ago visibility258 comment 0

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:

DESCRIPTION 'Export table TestDb.FASTLOAD_CSV to a CSV file named tpt_export.csv'
		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

		ID_0 VARCHAR(10),
		Col_Attr VARCHAR(1),
		Col_Value VARCHAR(10),
		CreatedDate VARCHAR(10),
		Comments VARCHAR(255)

		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 TraceLevel, 
		VARCHAR WorkingDatabase


			FileName = 'F:\tmp\tpt_export.csv', 
			Format = 'DELIMITED', 
			OpenMode = 'Write', 
			IndicatorMode = 'N', 
			TextDelimiter = ','

			UserName = 'dbc', 
			UserPassword = 'dbc', 
			LogonMech = 'TD2', 
			SelectStmt = 'SELECT ID,Col_Attr,Col_Value,CreatedDate,Comments FROM TestDb.FASTLOAD_CSV;', 
			TdpId = ''
Click OK button to submit the job.
Wait until the job complete successfully as the following screenshot shows:

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:


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

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 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
W_0_o_EXPORT_CSV: private log not specified
Teradata Parallel Transporter DataConnector Operator Version
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.


Teradata Parallel Transporter User Guide

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 SQL

visibility 1382
thumb_up 1
access_time 2 years ago

This code snippet shows how to convert string to date in Teradata.

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 979
thumb_up 0
access_time 2 years ago

This code snippet shows how to calculate time differences.

About column

Tutorials and information about Teradata.

rss_feed Subscribe RSS