spaces became <null> after mload/tpump

Tools
Enthusiast

spaces became <null> after mload/tpump

I have a binary format data files. Some columns are varchar and only spaces in the field. The space became after mload and tpump. It's correct output by fastload and tbuild with the same data file. It looks like the NULLIF clause took spaces as null in .FIELD. Any suggestion for this issue? Thanks in advance

Here is the script I used for the mload.
================================

.LogTable "TDTEST"."TDTGT1_LT";

.LOGON xx.xx.xx.xx/TDTEST,xxxx;

.BEGIN LOAD
SESSIONS 3
ErrorTable "TDTEST"."ET_TDTGT1"
CHECKPOINT 0
MACRODB TDTEST
;

.LAYOUT file_layout;
.FIELD C0_ * VARCHAR(20) NULLIF C0_ = '';
.FIELD C1_ * VARCHAR(20) NULLIF C1_ = '';
.FIELD C2_ * VARCHAR(20) NULLIF C2_ = '';

.DML LABEL insert_label;
INSERT INTO "TDTEST"."TDTGT1"(
"ID"
,"TEXT1"
,"TEXT2"
)
VALUES (
:C0_,
:C1_,
:C2_
);

.IMPORT INFILE c:/blkload/td_test_mload_binary.dat
Format BINARY
LAYOUT file_layout
APPLY insert_label;

.END LOAD;

.LOGOFF;
5 REPLIES
Enthusiast

Re: spaces became <null> after mload/tpump

I still got the wrong output even using CASE WHEN instead. :-(

====================
INSERT INTO "TDTEST"."TDTGT1"(
"ID"
,"TEXT1"
,"TEXT2"
)
VALUES (
:C0_,
CASE WHEN (:C1_ <> '') THEN :C1_ ELSE NULL END,
CASE WHEN (:C2_ <> '') THEN :C2_ ELSE NULL END
);
Senior Apprentice

Re: spaces became <null> after mload/tpump

Do you mean strings like ' ' are converted to NULL, too?

It's based on the comparison rules for strings:
The shorter string will be padded with strings before comparison.

It seems like FastLoad is not following that rule.

LIKE rules are different (no padding) so this should work:

CASE WHEN :C1_ NOT LIKE '' THEN :C1_ ELSE NULL END,

Dieter
Enthusiast

Re: spaces became <null> after mload/tpump

Hi ,

I have a similar problem where empty strings are getting loaded as blanks instead of loading them as NULLS in my target table.BODS is generating my mload script and this happens only with varchar columns.all other integer columns are getting loaded as NULLS where ever there were blanks in the source data.

Pls help me out as how to store the blanks as NULL using MLOAD..

Senior Apprentice

Re: spaces became <null> after mload/tpump

You need NULLIF in MLoad's LAYOUT or in the INSERT command:

.FIELD C0_ * VARCHAR(20) NULLIF C0_ = '';

INSERT  ... NULLIF(col, '')

Dieter

Enthusiast

Re: spaces became <null> after mload/tpump

Hi Dieter,

Thanks a lot for your inputs..forgot to mention that this is happening through a TPT load..So is it the same that we need to do?

So we need to manually handle such cases whenever there are blanks in the source data( for varchar columns) and no other go other than this?