Blanks are getting loaded with NULL's in TPT v14.00

Tools & Utilities
Highlighted
Enthusiast

Blanks are getting loaded with NULL's in TPT v14.00

Despit Using QuotedData and NullColumns='No' in TPT script ( DataConnector Producer Operator), Blanks or Empty String in the Source file is getting loaded with NULL.

 

Any advise on this. ( Teradata Parallel Transporter Version 14.00.00.09 )

7 REPLIES
Teradata Employee

Re: Blanks are getting loaded with NULL's in TPT v14.00

The only way to get an empty string loaded into Teradata is to set Quotedsata='yes' and the data for the column must consist of an open quote character immediately followed by a close quote character (e.g. "").

 

-- SteveF
Enthusiast

Re: Blanks are getting loaded with NULL's in TPT v14.00

Thanks For your response. I tried this option but last column of my table is NULL and in the exported file its not getting any quotes. Not sure why ?

 

e.g. 

EXPORT Attributes :

 

DEFINE OPERATOR FILE_WRITER
TYPE DATACONNECTOR CONSUMER
SCHEMA *
ATTRIBUTES
(
VARCHAR PrivateLogName = 'dataconnector_log',
VARCHAR DirectoryPath = '/folder',
VARCHAR FileName = 'Export_Test.txt',
VARCHAR Format = 'Delimited',
VARCHAR OpenMode = 'Write',
VARCHAR QuotedData = 'Yes',
VARCHAR OpenQuoteMark = '"',
VARCHAR CloseQuoteMark = '"',
VARCHAR TextDelimiter = '~!'
);

 

and Exported record in the File :

 

"2017-11-02"~!"2017-11-03 04:05:22.000000"~!

 

and thays why my import is failing.

 

ALso, If I make it Optional and include the parameter VARCHAR NUllColumns='No'

 

VARCHAR QuotedData = 'Optional',
VARCHAR OpenQuoteMark = '"',
VARCHAR CloseQuoteMark = '"',
VARCHAR NullColumns= 'No',

 

No rows are getting inserted into the target table.

Teradata Employee

Re: Blanks are getting loaded with NULL's in TPT v14.00

This appears to be an entirely different question. Are you saying the source table has NULL in the column but you want the value exported as an empty string?

For that, you need to use something like COALESCE in your SELECT statement.

Teradata Employee

Re: Blanks are getting loaded with NULL's in TPT v14.00

I would like to know why you say your import is failing?

Do you have any output to share?

On input, that data file will load exactly what came out of the DBS (2 columns of data and a column that is NULL).

 

-- SteveF
Enthusiast

Re: Blanks are getting loaded with NULL's in TPT v14.00

Hi Steve,

My Table is having 3 columns as EFFECTIVE_DATE,LOAD_TIMESTAMP,INVALIDATED_TIMESTAMP.

 

Table Data :

2017-11-02 |  2017-11-03 04:05:22.000000 | <Empty string as COlumn 3 is not having data for this Row>

 

Exported data in the File :

 

"2017-11-02"~!"2017-11-03 04:05:22.000000"~!

 

Now, I'm saying that, for empty string in the source table, I'm getting empty string in the exported file but withour quote's as you can see above.

 

Ad these empty strings are getting loaded as NULL in the target table and this causing the problem for me as it should be the empty string in target too.

 

 

 

Teradata Employee

Re: Blanks are getting loaded with NULL's in TPT v14.00

Ok, originally you indicated that the last column was NULL, not an empty string.

I will look into it; it is possible there was a bug and we fixed it, but since 14.00 has been out of support for quite a long time, it is possible that this problem was fixed in a later version of TPT.

 

-- SteveF
Teradata Employee

Re: Blanks are getting loaded with NULL's in TPT v14.00

Ok, I just did the following to show what TPT does; it seems to work fine for me.

I am using TPT 16.10.00.00.

 

First, I used BTEQ to create and load the table:

ct onenull (f1 varchar(10), f2 varchar(10), f3 varchar(10));

ins into onenull values ('abc','def',);            <=== inserts null

ins into onenull values ('xyz','123','');          <=== inserts empty string

 

Then, to show the table contents:

 

sel * from onenull;

*** Query completed. 2 rows found. 3 columns returned.
*** Total elapsed time was 1 second.

f1         f2         f3
---------- ---------- ----------
xyz        123
abc        def        ?

 

Now, when I run TPT to export the data and write to the file (setting QuotedData to "yes" in the DC operator), the file contains this:

 

$ cat onenull.txt
"abc"|"def"|
"xyz"|"123"|""

 

TPT seems to be working as expected.

 

 

-- SteveF