Teradata Parallel Transporter ~ OPERATOR FILE_WRITER ~ escape characters in output

Tools & Utilities
Enthusiast

Teradata Parallel Transporter ~ OPERATOR FILE_WRITER ~ escape characters in output

I needed to unload data from Teradata and escape certain characters.  For example some character strings contain a pipe symbol ('|') but I am using the pipe as my delimiter.   The documentation at http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/Load_and_Unload_Utilities/B035_24... leads me to believe that if I specify EscapeTextDelimiter it should work.

...

  DEFINE OPERATOR FILE_WRITER
  TYPE DATACONNECTOR CONSUMER
  SCHEMA *
  ATTRIBUTES
  (
    VARCHAR PrivateLogName = 'dataconnector_log',
    VARCHAR DirectoryPath  = '${WorkDir}',
    VARCHAR FileName       = '${pipefilename}',
    VARCHAR Format         = 'Delimited',
    VARCHAR OpenMode       = 'Write',
    VARCHAR EscapeTextDelimiter  = '\',
    VARCHAR TextDelimiter  = '|'
  )
  ;

  APPLY TO OPERATOR (FILE_WRITER)
  SELECT * FROM OPERATOR (EXPORT_OPERATOR);
)
;

It turns out that I needed to do the obvious, and "escape my escape" like this:

...

  DEFINE OPERATOR FILE_WRITER
  TYPE DATACONNECTOR CONSUMER
  SCHEMA *
  ATTRIBUTES
  (
    VARCHAR PrivateLogName = 'dataconnector_log',
    VARCHAR DirectoryPath  = '${WorkDir}',
    VARCHAR FileName       = '${pipefilename}',
    VARCHAR Format         = 'Delimited',
    VARCHAR OpenMode       = 'Write',
    VARCHAR EscapeTextDelimiter  = '\',
    VARCHAR TextDelimiter  = '|'
  )
  ;
3 REPLIES
Enthusiast

Re: Teradata Parallel Transporter ~ OPERATOR FILE_WRITER ~ escape characters in output

Is the only way to escape the EscapeTextDelimiter itself in the output data, to wrap REPLACE UDF's around all the CHAR/VARCHAR(x) columns and substitute "EscapeTextDelimiter concatenated with EscapeTextDelimiter"  for each occurrence of the "EscapeTextDelimiter "?

Enthusiast

Re: Teradata Parallel Transporter ~ OPERATOR FILE_WRITER ~ escape characters in output

Mention "QuotedData" attribute as "Yes" in your FILE_WRITER attribute list.

Mention the value for OpenQuote Mark and CloseQuoteMark attribute as double quote character ( " ), in your FILE_WRITER attribute list.

When you execute the TPT script now, you should be able to unload the data in quoted format, without specifying the EscapeTextDelimiter. 

Teradata Employee

Re: Teradata Parallel Transporter ~ OPERATOR FILE_WRITER ~ escape characters in output

The double-quote character is treated as the OpenQuoteMark and CloseQuote Mark by default.

No need to set those attributes.

-- SteveF