Control character as delimiter in Mload

Database
Enthusiast

Control character as delimiter in Mload

I want to use Control+A (x01) as delimiter in my mload script and also i want to define my target teradata columns datatypes are ( integer, varhcar, date ). How can i accomplish both of this ? I saw in mload reference manual, VARTEXT is not an option for both of these, i am still trying to undersatnd other formats available. Can you anyone suggest what is the option?

 

I should be able to do both below ideally if not atleast delimiter option...

My source file is CTRL+A (x01) delimited

My target table has data types (Integer, date, varhcar)



Appreciate your inputs. Thank You

Tags (1)

Accepted Solutions
Teradata Employee

Re: Control character as delimiter in Mload

What data is in the input where you want NULL in the table? Is it two consecutive delimiters (which is considered NULL for delmited input), spaces, 9999-01-01?

What do the valid dates look like?

 

You might need to explicitly specify a format for the type conversion, e.g.  CAST(:STB_EXPIRATION as DATE FORMAT'yyyy-mm-dd') in the SQL.

You could also use NULLIF or CASE expression inside the CAST to convert dummy values to NULL.

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Control character as delimiter in Mload

For VARTEXT, the FIELD definitions in your script must all be VARCHAR. The target columns need not be. Teradata will attempt implicit conversion or your DML statement can explicitly CAST / reformat the data to match the target.

 

As noted in the manual, you can use a single-character hex literal to specify the field delimiter: '01'xc

Enthusiast

Re: Control character as delimiter in Mload

Thanks Fred. I am now stuck with date field in target not getting loaded with NULL source. Implicit conversion is failing i guess.

2,665 Invalid date. is the error. I tried coalesce, NULLIF functions just to see atleast if valid date gets in but they as well failed

Ex:

NULLIF(:STB_EXPIRATION,cast('9999-01-01' as date)) 

 

My goal is to load NULL to these columns (Target is NULLABLE). how do i do this?

Teradata Employee

Re: Control character as delimiter in Mload

What data is in the input where you want NULL in the table? Is it two consecutive delimiters (which is considered NULL for delmited input), spaces, 9999-01-01?

What do the valid dates look like?

 

You might need to explicitly specify a format for the type conversion, e.g.  CAST(:STB_EXPIRATION as DATE FORMAT'yyyy-mm-dd') in the SQL.

You could also use NULLIF or CASE expression inside the CAST to convert dummy values to NULL.

Enthusiast

Re: Control character as delimiter in Mload

Thank You. I see the problem was with source data itself. NULL were coming as \N characters and i had to replace them with nothing.