DSXchange

Hello Sirs,

I have a Sequential file with some fields delimiter vertical bar and few fields with vertical and double quotes,How to handle it.

Please advice me.

Thanks
Narasa

Since the sequential file stage only allows one delimiter you can't do it in just one step.

I would write a job that defines this sequential file as having one column and no field delimiters at all. Run this stream through a transform that does a REPLACE of all pipe and single-quote characters into double quote characters. The transform output goes to a sequential file which is written to as one column and no field delimiters.

Then have a stream coming out of this sequential file stage with the double quote character as delimiter and with the correct column definitions. You can also make this file stage a pipe for performance (that way, no data is actually transferred to and from the disk).

This solution assumes that you don't have occurrences of pipe and single-quote in the data itself.

ArndW wrote:Since the sequential file stage only allows one delimiter you can't do it in just one step.

I would write a job that defines this sequential file as having one column and no field delimiters at all. Run this stream through a transform that does a REPLACE of all pipe and single-quote characters into double quote characters. The transform output goes to a sequential file which is written to as one column and no field delimiters.

Then have a stream coming out of this sequential file stage with the double quote character as delimiter and with the correct column definitions. You can also make this file stage a pipe for performance (that way, no data is actually transferred to and from the disk).

This solution assumes that you don't have occurrences of pipe and single-quote in the data itself.

Hi Andrew,

Thanks for quick response.

How to convert pipe and double quotes into single quote using a single function.

Thanks for help.
Narasa

Reddy,

the function would be CONVERT("'|","''",In.BigColumn). Please note the string delimiter in the first parameter is a double quote and the second paramater uses a single quote. This function takes all occurrences of ' and | and replaces them with " and " respectively in the string In.BigColumn

Maybe you also have to replace your quote char to some other value than dquotes.

I agree with Sainath, the best option would be to use a character, thats very unlikely to appear as data in your file. May be ~ or `

Just a feeling, are you sure that you are getting two types of delimeter?
Coz, I have a feeling that it could be Quote Character as double quotes and pipe as delimeter.