All Forums Tools
stevehager 7 posts Joined 11/11
05 Feb 2014
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_2436_071A/2436ch03.078.09.html 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  = '|'
  )
  ;

 
 

stevehager 7 posts Joined 11/11
05 Feb 2014

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 "?

ratchetandclank 49 posts Joined 01/08
06 Feb 2014

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. 

feinholz 1234 posts Joined 05/08
06 Feb 2014

The double-quote character is treated as the OpenQuoteMark and CloseQuote Mark by default.
No need to set those attributes.
 

--SteveF

You must sign in to leave a comment.