TPT 16 - escape character escaping is missing compare to TPT 15.10

Tools

TPT 16 - escape character escaping is missing compare to TPT 15.10

 hi,

 

With TTU 16.0 (16.00.03) there is a difference on the CSV output compare to 15.10..

It looks like that the escae character itself (‘\’ – backslash in my case) is not escaped if it is part of the data. On TPT 15.10 it was, detailed example below.

 

Am I missing a new configuration option that I need to set? 

 

Thanks, Gilad.

 

Source table:

CREATE TABLE gilad.test_escape (a INTEGER, b VARCHAR(30));
insert into gilad.test_escape  values (1, 'asd\');
insert into gilad.test_escape  values (2, 'a"sd\');
insert into gilad.test_escape  values (3, 'a,sd\');
insert into gilad.test_escape  values (4, 'a\nsd\');

 

TPT 15.10 output file:

"1","asd\\"
"2","a\"sd\\"
"3","a,sd\\"
"4","a\\nsd\\"

 

TPT 16.0 output file:

"1","asd\"
"2","a\"sd\"
"4","a\nsd\"
"3","a,sd\"

 

Full TPT script:

USING CHARACTER SET UTF8 
DEFINE JOB EXPORT_TABLE_TO_FILE (
	DEFINE SCHEMA TABLE_SCHEMA 
	(
		 "a"	INTEGER,
		 "b"	VARCHAR(90)
	);
	DEFINE OPERATOR FILE_WRITER()
	TYPE DATACONNECTOR CONSUMER
	SCHEMA *
	ATTRIBUTES
	(
		VARCHAR PrivateLogName = 'file_writer_privatelog',
		VARCHAR FileName = 'C:\temp\test_escape.csv',
		VARCHAR IndicatorMode = 'N',
		VARCHAR OpenMode = 'Write',
		VARCHAR Format = 'DELIMITED',
		VARCHAR EscapeQuoteDelimiter = '\',
		VARCHAR QuotedData = 'Yes',
		VARCHAR TextDelimiter = ','
	);
	DEFINE OPERATOR EXPORT_OPERATOR()
	TYPE EXPORT
	SCHEMA TABLE_SCHEMA
	ATTRIBUTES
	(
		VARCHAR PrivateLogName	= 'export_privatelog_1',
		INTEGER MaxSessions       = 1,
		INTEGER MinSessions       = 1,
		VARCHAR TdpId             = @SourceTdpId,
		VARCHAR UserName          = @SourceUserName,
		VARCHAR UserPassword      = @SourceUserPassword,
		VARCHAR DateForm          = 'ANSIDATE',
		VARCHAR SelectStmt       = 'SELECT a,b FROM "gilad"."test_escape";'
	);
	STEP export_to_file
	(
		APPLY TO OPERATOR (FILE_WRITER() [1])
		SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
	);
);

 

Tags (3)
3 REPLIES
Teradata Employee

Re: TPT 16 - escape character escaping is missing compare to TPT 15.10

We actually "fixed" the Data Connector operator, in the way that it treats the escape characters.

The behavior is actually now correct.

As long as you use the same version of TPT to both read and write, you will be fine.

 

 

-- SteveF

Re: TPT 16 - escape character escaping is missing compare to TPT 15.10

1. In order to Post process the file I prefer the previous behavior. - is there a configuration option to get the previous behavior? Backward comparability option?

2. Can you please explain how to parse the new output file ?
If the last character in the data is backslash and the data is quoted the backslash (that now with the mew version is not escaped) will escape the quotes (like other quotes in the data).

Thanks,
Gilad

Re: TPT 16 - escape character escaping is missing compare to TPT 15.10

hi,

 

More datails - trying to export and import using the same TPT version as suggested - succeeded with TPT 15.10 and failed with TPT 16.0

 

reproduction steps:

  1. create table MAYDB.escape_p(id integer, a varchar(10), b integer, c varchar(10))
    insert into MAYDB.escape_p values (1, 'abc\', 2, 'def')
  2. export the table using 'export_table.ctl' (full script below)
  3. exported file data is:
    "1","abc\","2","def"
  4. create table MAYDB.escape_p2(id integer, a varchar(10), b integer, c varchar(10))
  5. import table using 'import_table.ctl' (full script below) cause parsing error:
    op_EMP_NAME[1]: TPT19134 !ERROR! Fatal data error processing file 'C:\temp\TABLE_1.txt'. Delimited Data Parsing error: Delimiter did not immediately follow close quote mark in row 1, col 2.

Same process with the same scripts using TPT 15.10:
exported file data is:
"1","abc\\","2","def"
data imported successfully.

 

export_table.ctl:

 

USING CHARACTER SET UTF8 
DEFINE JOB EXPORT_TABLE_TO_FILE (
	DEFINE SCHEMA TABLE_SCHEMA 
	(
		 "id"	INTEGER,
		 "a"	VARCHAR(30),
		 "b"	INTEGER,
		 "c"	VARCHAR(30)
	);
	DEFINE OPERATOR FILE_WRITER()
	TYPE DATACONNECTOR CONSUMER
	SCHEMA *
	ATTRIBUTES
	(
		VARCHAR PrivateLogName = 'file_writer_privatelog',
		VARCHAR FileName = 'TABLE_1.txt',
		VARCHAR IndicatorMode = 'N',
		VARCHAR OpenMode = 'Write',
		VARCHAR Format = 'DELIMITED',
		VARCHAR QuotedData = 'Yes',
		VARCHAR EscapeQuoteDelimiter = '\',
		VARCHAR TextDelimiter = ','
	);
	DEFINE OPERATOR EXPORT_OPERATOR()
	TYPE EXPORT
	SCHEMA TABLE_SCHEMA
	ATTRIBUTES
	(
		VARCHAR PrivateLogName	= 'export_privatelog_1',
		INTEGER MaxSessions       = 10,
		INTEGER MinSessions       = 1,
		VARCHAR TdpId             = @SourceTdpId,
		VARCHAR UserName          = @SourceUserName,
		VARCHAR UserPassword      = @SourceUserPassword,
		VARCHAR DateForm          = 'ANSIDATE',
		VARCHAR SelectStmt       = 'SELECT id,a,b,c FROM "MAYDB"."escape_p";'
	);
	STEP export_to_file
	(
		APPLY TO OPERATOR (FILE_WRITER() [1])
		SELECT * FROM OPERATOR (EXPORT_OPERATOR() [1] );
	);
);

 

import_table.ctl

 

DEFINE JOB LOAD_TABLE_FROM_FILE (
	DEFINE SCHEMA SCHEMA_EMP_NAME
	(
		 "id"	VARCHAR(30),
		 "a"	VARCHAR(30),
		 "b"	VARCHAR(30),
		 "c"	VARCHAR(30)
	);

	DEFINE OPERATOR op_EMP_NAME
	TYPE DATACONNECTOR PRODUCER
	SCHEMA SCHEMA_EMP_NAME
	ATTRIBUTES
	(
		VARCHAR  DirectoryPath= 'C:\temp',
		VARCHAR  FileName = 'TABLE_1.txt',
		VARCHAR  Format   = 'Delimited',
		VARCHAR  OpenMode = 'Read',
		VARCHAR  QuotedData = 'Yes',
		VARCHAR  EscapeQuoteDelimiter = '\',
		VARCHAR  TextDelimiter =','
	);

	DEFINE OPERATOR ol_EMP_NAME
	TYPE LOAD
	SCHEMA *
	ATTRIBUTES
	(
		VARCHAR TdpId          = @SourceTdpId,
		VARCHAR UserName       = @SourceUserName,
		VARCHAR UserPassword   = @SourceUserPassword,
		VARCHAR  TargetTable  =  'MAYDB.escape_p2'
	);

	STEP stLOAD_FILE_NAME
	(
		APPLY
		('INSERT INTO MAYDB.escape_p2
		(id,a,b,c)
		VALUES
		(:id,:a,:b,:c);
		')
		TO OPERATOR (ol_EMP_NAME)
		SELECT * FROM OPERATOR(op_EMP_NAME);
		);
	);
);

 

Thanks,

Gilad