All Forums Tools
TDDeveloper 23 posts Joined 09/09
17 Dec 2014
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.

Ivyuan 63 posts Joined 01/10
18 Dec 2014

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 */
c1 varchar(5),
c2 varchar(5),
c3 varchar(5))
.FIELD c1 * varchar(5) ;
.FIELD c2 * varchar(5) ;
.FIELD c3 * varchar(5) ;
INSERT INTO testtbl VALUES (:c1,:c2,:c3);
.IMPORT INFILE atrdata.txt
.logoff ;
The data file atrdata.txt looks like:
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,

Fred 1096 posts Joined 08/04
19 Dec 2014

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.

TDDeveloper 23 posts Joined 09/09
23 Dec 2014

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.

feinholz 1234 posts Joined 05/08
24 Dec 2014

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 "".


feinholz 1234 posts Joined 05/08
24 Dec 2014

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.


TDDeveloper 23 posts Joined 09/09
31 Dec 2014

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?

feinholz 1234 posts Joined 05/08
31 Dec 2014

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.


Ivyuan 63 posts Joined 01/10
05 Jan 2015

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

TDDeveloper 23 posts Joined 09/09
09 Jan 2015

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

You must sign in to leave a comment.