How to remove double quotes with in the value

ravij · Post by **ravij** » Mon Mar 17, 2008 12:13 am

Hi,

I have a column which contain varchar value and is double quoted. But with in the value again I have double quote character, when I run the job its getting aborted due to this another quote. I searched for help in the search and found functions like

Ereplace(source.Field001, ' " ' , ' ' )
EReplace(Iconv(Linkname.fieldname,"MCP"),'"', "")

but its not working. Please help me in this regard.
The error I am getting is

TSFM_BALANCE..BALANCE.DSLink5: read_delimited() - invalid quotes, column Name = "TOUCH \"

Thanks in advace..

ArndW · Post by **ArndW** » Mon Mar 17, 2008 1:59 am

The error message comes from the file read stage, so before any replace functions could be used. Could you post the line of source data causing the error?

ray.wurlod · Post by **ray.wurlod** » Mon Mar 17, 2008 2:03 am

The first question you need to ask is whether the double quote character within the data is validly there - if it is, then removing it is not the correct approach. Some examples of the source data would help. In the meantime I would suggest specifying no quote character (000) and stripping the leading and trailing double quote character from the received data, probably using Trim() function.

ravij · Post by **ravij** » Mon Mar 17, 2008 3:00 am

Hi ArndW,

Thanks for your reply.

The source of line is

"TOUCH \"N\" TALK"

.

Ray,
As per your Suggestion, I used 000 in quote character and used Trim function but some of the values in the source have comma(,) with in the value itself and as my file comma seperated, its giving some warnings when I view data from sequential file stage.
The warning msg is
.

TSFM_BALANCE..BALANCE.DSLink5: read_delimited() - row 292, too many columns in record

How should I handle this..
Thanks for your suggestion in advance

ArndW · Post by **ArndW** » Mon Mar 17, 2008 3:06 am

Read the file as just one big column with no quotes. Use "EREPLACE(In.BigColumn,'\"','""') to convert all \" to "" (the correct representation) and write that back to a file with no quotes. You can then use this new file as your input and DataStage will correctly convert the "" to single " in the strings. You can also use named pipes instead of actually landing the data in the interim file.

ravij · Post by **ravij** » Mon Mar 17, 2008 3:28 am

Hi Arndw,

I tried as you suggested, like I took single column as Dummy n delimiter and Quote character taken as "000" and Ereplace function in Transformer stage and sent output as single columun. But when I run this job, getting the error like

Test_Quotes..Transformer_0: |Test_Quotes..BAL_HIERARCHY.DSLink6: DSD.SEQOpen Unable to create file #STG_FILE_PATH#/Stg_KK_Balance_Test.txt.|

Please correct me if something went wrong

ravij · Post by **ravij** » Mon Mar 17, 2008 3:30 am

Hi Arndw,

I tried as you suggested, like I took single column as Dummy n delimiter and Quote character taken as "000" and Ereplace function in Transformer stage and sent output as single columun. But when I run this job, getting the error like

Test_Quotes..Transformer_0: |Test_Quotes..BAL_HIERARCHY.DSLink6: DSD.SEQOpen Unable to create file #STG_FILE_PATH#/Stg_KK_Balance_Test.txt.|

Please correct me if something went wrong

ravij · Post by **ravij** » Mon Mar 17, 2008 3:32 am

Hi Arndw,

I tried as you suggested, like I took single column as Dummy n delimiter and Quote character taken as "000" and Ereplace function in Transformer stage and sent output as single columun. But when I run this job, getting the error like

Test_Quotes..Transformer_0: |Test_Quotes..BAL_HIERARCHY.DSLink6: DSD.SEQOpen Unable to create file #STG_FILE_PATH#/Stg_KK_Balance_Test.txt.|

Please correct me if something went wrong

ArndW · Post by **ArndW** » Mon Mar 17, 2008 3:53 am

How did the pipe character get into the output file path name? A "named pipe" is something very different. Try it with a normal sequential file first.

ravij · Post by **ravij** » Mon Mar 17, 2008 4:21 am

Hi ArndW,

Thanks for your help. Now I amgetting the data into output file. But one question..
1) Now how shoud I get the data into my actual columns(6 columns) from this one column?
2) My source file is comma seperated, Some of the records have commas with in the values, how shoud I handle those commas while taking the output into my actual columns. Should I use Quote character while reading the data back from output file into actual columns to avoid these commas?

Any help would be greatly appreciated.

ray.wurlod · Post by **ray.wurlod** » Mon Mar 17, 2008 5:01 am

OK, stop right there.

This could turn into an infinite regression.

Get "them" to provide you with a proper file, one where the delimiter character is not one that occurs in the data, and without quotes around strings. Tab-delimited suggests itself immediately, and is one of the options for exporting from most tools.

ArndW · Post by **ArndW** » Mon Mar 17, 2008 5:09 am

You have now re-written your file without delimiters and quotes, so it will look like your original file except that all occurrences of '\"' are now '""'. Now read it with your original 6 column definition.