DSXchange

Hi,
Storing extracted data into (temporary) delimited files for subsequent processing within a DataStage job seems to be problematic when the data contains the delimiting character or embedded <CR><LF>.

I am wondering if there is a way around this problem.


Thanks Nima

When the data contains delimiter char, use a different delim char.

When the data contains line terminator, use convert to replace to blank.

Sainath.Srinivasan wrote:When the data contains delimiter char, use a different delim char.

When the data contains line terminator, use convert to replace to blank.

Thanks for the reply. The problem is that we do not know before hand if the data includes the delimiter character or not.

Cheers Nima

Actually, if you define a quote character for your string data you can have any number of occurrences of your delimiter character (as well as your quote character).

The difficulty is with your line terminator character, the parsing algorithm cannot tell the difference between an embedded character and a true line break. If you know you will have line or record terminators in your data then the easiest solution is to define your data as fixed length and remove both quote and terminator characters -- that way DS just reads and writes <line-length> characters per record irrespective of the contents. Not necessarily a great solution if you have a varchar(1024) column that only contains 4 characters, but it does work.

Use a hash file. Should not be a problem to have CR/LF. It will slow it down but it will not change the column.

Thank you very much for the replies. I was away for a week and could not check out your responds.

cheers nima

In the Sequential File Stage, within the column properties....you can specify whether that column contains the line terminator.

Once you read the file successfully..you can use the convert function to convert the line terminator into space or some other character.

You can use field delimiter, which cannot be entered by a single key in the keyboard. So that the chances are very minimal to have the delimited string unless your source application has specific requirement to store that.

Regards
Saravanan