All Forums Tools
rou_oli 4 posts Joined 01/16
18 Jul 2016
TPT read fixed file and convert space to null value

hi,
i search a TPT syntax but i'm not sure if it's possible.
i have an input fixed file's. Two columns as a varchar(15) and a varchar(8) and the second column can contain 8 space caracter wich convert it as null value.
In the block SCHEMA or OPERATOR, is it possible to define the conversion or is it only possible to the APPLY statement with case when syntax ?
 
DEFINE SCHEMA FILE_xxx
(
aaa VARCHAR(15)
, bbb VARCHAR(8)
);
 
DEFINE OPERATOR FILE_xxx_READER
DESCRIPTION ''
TYPE DATACONNECTOR PRODUCER
SCHEMA FILE_xxx
ATTRIBUTES
(
    VARCHAR DirectoryPath = @DirectoryPath,
    VARCHAR FileName = @FileName,
    VARCHAR Format = 'Text',
    VARCHAR TrimColumns = 'Both',
);
 
Thanks lot for response

CarlosAL 512 posts Joined 04/08
18 Jul 2016

Hi.
If file is TEXT the SCHEMA fields should be CHAR.
You must use CASE WHEN in the SELECT ... FROM OPERATOR  FILE_xxx_READER.
HTH.
Cheers.
Carlos.

rou_oli 4 posts Joined 01/16
18 Jul 2016

thanks Carlos

You must sign in to leave a comment.