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
Seq File Delimiter
Moderators: chulett, rschirm, roy
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.
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.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
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
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
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Participant
- Posts: 3337
- Joined: Mon Jan 17, 2005 4:49 am
- Location: United Kingdom
-
- Charter Member
- Posts: 199
- Joined: Tue Jan 18, 2005 2:50 am
- Location: India