MLOAD and loading of: Empty Strings and Null Strings

Tools & Utilities
Enthusiast

MLOAD and loading of: Empty Strings and Null Strings

When loading data from flat files (text ascii) to tables I find that if the file is a delimited file with all the fields defined as VARCHAR for the columns the columns with blank values (all spaces not just two consecutive delimitters for a column which is defined in the target table as CHAR or VARCHAR) are loaded as NULLs wheres if the file is a fixed type file with the layout dfined as CHAR for the columns the columns are loaded with an 'empty string'.  Question is: (1) what's the theory behind this behavior if this is really by design? (2) how do I a make MLOAD load (without using a CASE Expression) emptry string when loading data from delimitted files.

9 REPLIES
Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

Hi,

I tried a MultiLoad script, MultiLoad can load blank space charcaters and NULLs respectively.

Here is the MultiLoad script:

.LOGTABLE logt_a;

.LOGON <tdpid>/<userid>, <password>;

drop table testtbl;

drop table et_testtbl;

drop table wt_testtbl;

drop table uv_testtbl;

/*************************************************/

/* Create and insert table */

/*************************************************/

CREATE MultiSET TABLE testtbl, FALLBACK (

c1 varchar(5),

c2 varchar(5),

c3 varchar(5))

PRIMARY INDEX(C1);

.BEGIN IMPORT MLOAD TABLES testtbl

;

.LAYOUT LAY1A;

.FIELD c1 * varchar(5) ;

.FIELD c2 * varchar(5) ;

.FIELD c3 * varchar(5) ;

.DML LABEL LABELA;

INSERT INTO testtbl VALUES (:c1,:c2,:c3);

.IMPORT INFILE atrdata.txt

FORMAT VARTEXT '|' NOSTOP

LAYOUT LAY1A

APPLY LABELA;

.END MLOAD;

.logoff ;

The data file atrdata.txt looks like:

12345|12345|12345

     ||    

12|as  |123

After MultiLoading, here is the result:

 *** Query completed. 3 rows found. 3 columns returned.

 *** Total elapsed time was 1 second.

c1     c2     c3

-----  -----  -----

12     as     123

12345  12345  12345

       ?

"?" means a NULL charcater was inserted,

Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

This behavior is documented in the MultiLoad manual. Supplying NULL rather than a zero-length string was a conscious choice.

As the original poster noted, you can use CASE/COALESCE in the INSERT statement to convert the NULL values to something else.

Enthusiast

Re: MLOAD and loading of: Empty Strings and Null Strings

Ivyuan/Fred, Thanks for your reply. 

The behavior of loading NULL is what I expect. Try loading a fixed length file with a column value all blanks. this column is loaded as an empty-string rather than NULL string. So there's no way except using a CASE statement to load NULL instead of empty-string when loading from a fixed format text file.

I am not sure if this inconsitancy in the treatment of loading blank column is documented.

Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

Any column value with all blanks should be loaded as a column of all blanks. Not NULL or empty string. The only way to load an empty string is to use the quoted vartext feature and quote your data. The empty string would then be denoted by "".

-- SteveF
Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

There might be another way.

You mentioned that you have fixed length fields.

This usually means you are using the TEXT format and not VARTEXT.

Have you tried the TRIM function.

A field of all blanks when trimming the blanks should yield an empty string.

-- SteveF
Enthusiast

Re: MLOAD and loading of: Empty Strings and Null Strings

Thanks Stevef.  Even without the TRIM the blank columns are loaded as empty string with the TEXT format.  With the VARTEXT format both the blanks and the NULL (two adjacent delimitter char) columns are loaded as NULL. This is what I find as inconsistant, agree?

Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

After the holidays we will take a look. Inconsistent? I would just call it wrong, if it is true.

Ivy posted above that she showed a VARTEXT job loading blanks into the DBS and not NULL when the source data had blanks.

-- SteveF
Teradata Employee

Re: MLOAD and loading of: Empty Strings and Null Strings

Hi TDDeveloper,

Can you share with me your MultiLoad script and the target table definition? Thanks!

--Ivy.

Enthusiast

Re: MLOAD and loading of: Empty Strings and Null Strings

I tried Ivy's script and data above and it worked as expected.. :)

Puts a null string only if the column is blank and trimmed with VARTEXT..

Thanks you both!

data from the Ivy's script loaded as is

    c1       c2       c3          lc1  lc2  lc3

1  12       as       123          2    4   3

2  12345 12345  12345      5    5   5

3              ?                       5    ?   4

with TRIM aplied to C3

    c1        c2        c3         lc1  lc2  lc3

1  12        as       123         2    4    3

2  12345  12345  12345     5    5    5

3              ?                       5    ?    0