DSXchange

Hi,

I am a newbee in datastage. I am trying to read a simple text file through a sequential file stage. Till now everything is working fine. My question is can a .txt file hold null value? If it contains I need to handle them.

I have searched other forums, there some one had mentioned that null values are not possible in .txt.

Can anybody please confirm?

Thanks in advance.

Thanks,
Pratik

Of course null values are possible in text files. As an example, you have a pipe-delimited text file with 4 columns .... you get data like:

1|A|B|C
2||D|E
3|F|G|H

On the second row, you have a null on the second column.

Actually, they're not. What you have in sequential files are empty fields rather than nulls.

chulett wrote:Actually, they're not. What you have in sequential files are empty fields rather than nulls. ...

<sigh> Picky, picky,picky

True, they are empty fields, but when you are processing them into a transformer and writing them out you need to handle them as being null, which is what I believe the OP was worried about.

chulett wrote:Actually, they're not. What you have in sequential files are empty fields rather than nulls. ...

Craig,

You mean to say IsNull will not treat this field as NULL? I believe they do.

Craig is correct. ISNULL() wont treat it as null. The ascii value for null and empty space are different.

I tried with ISNULL() but it is not working.

Whether or not an empty field is NULL depends on how you import it or modify it after importing.

1) Imported as a non-nullable varchar() field? It won't be null, it will be empty (Len = 0). make_null() with a modify stage, setNull() within a transformer to convert it to a true null (in a NULLABLE field). Simply changing the field to nullable will leave you with a nullable empty string...you must force it to a NULL.

2) Imported as a nullable varchar() field? I believe the default conversion is empty string to NULL, so it should be converted to a null. You can make sure by setting the appropriate NULL value in the Format Tab or metadata options.

3) Imported as a char()? Not recommended practice with a variable-length source field.

Regards,

My rule of thumb is, if I am working with an external FLAT file and I have to do null checks, I perform isnull() and len(trim()) < 1.

Text files don't really have data types at all (they just have text). Therefore it is not possible for them to contain null.

The Sequential File stage allows you to specify how "null" is represented in data, both globally and at the individual field level. This does not mean that null now exists in the text file, it means that the stage will interpret the Null Field Value value as null when encountered. The Null Field Value value does not have to be "".

NULL means unknown value and empty means there is no value.A text file can have empty but it cannot have NULLs.if a field is empty or possible to be empty then select the option Null field value=''
and make the possible columns to NULL yes