nls_read_delimited() - invalid quotes

[vinaymanchinila]

Hi,
How do I take out " in incomming data , as I am writing a flat file and then loading into a DB .
It gives me a error:

nls_read_delimited() - invalid quotes

[pnchowdary]

Hi Vinay,

Your question is not clear. I am suggesting three solutions based on different interpretations of your problem.

1) If you meant that your source file is having quotes and you want to read it

- In format tab in the sequential file, make sure you put the quote character field to " and appropriate value for the delimeter field. Then you will be able to read the file properly.

2) If you meant that while writing your output to a sequential file, you have " appearing in your data

- Similar to the above solution, you need to make sure in the format tab of your output sequential file that you put the quote character as 000 or 009 depending on your requirement.

3) If you mean that you have " characters in some of the data in the columns.

- You can use functions like ereplace,replace,convert etc to replace the " character with space

Thanks,
Naveen

[vinaymanchinila]

Thanks Chowdary,
I have already extracted the source data intoa flat file with quote character " . Now I am loading into the database table, where I want to take the " from the data where ever it appears in the source. I have used Change(Column,""","") and it doesnt work !

[pnchowdary]

Hi Vinay,

You can use the Exchange function that accepts hex characters as the string to replace, string to be replaced

Code: Select all

Exchange(INPUTLINK.COLA,"22","20") 

where 22 is the Hex equivalent of " and 20 is the Hex equivalent of space
Make sure you replace INPUTLINK.COLA with the appropriate values.

Try it and let me know whether it works for ya.

Thanks,
Naveen

[vinaymanchinila]

Hey Navin,
So will it replace all the occurences? because my row data looks like
""Column value""
Thanks,

[pnchowdary]

Hi Vinay,

Yes, It should replace all the occurences.

Thanks,
Naveen

[vinaymanchinila]

there are 2 jobs,
1) One that extract the data into flat file (Because this file is used by others too)

2) Load from this flat file into Oracle table.

I am using the Exchange function in the first job and running it currently , it takes 4 hrs .

I really hope it works.

Thanks for the solution!

[vinaymanchinila]

Hey ,
I am also using quote ' instead of " for the both the flat files , will it be a problem .

[pnchowdary]

Hi Vinay,

Hey ,
I am also using quote ' instead of " for the both the flat files , will it be a problem .

Your question is ambiguous.

1) If your question meant "Will it work if I want to replace ' quote with space?"

- No it will not work as the hex code for ' is different from " . Therefore you need to change your Exchange function to

Code: Select all

Exchange("ABABC", "27", "20")

Hex code for ' is 27

If that was not your question, please clarify it and I will be able to answer it.

Thanks,
Naveen

[vinaymanchinila]

No, I have " in my source data, but whenI write to the flat file

1) I am using exchange on that particular column

2) Instead of the " quote character in the FORMAT tab of the flat file , using '

[pnchowdary]

Hi Vinay,

I think you should have no problem using ' character in the format tab, when you write to the flat file.

Thanks,
Naveen

[amsh76]

Hi Vinay,

If you are trying to remove quotes from your data then try this

Convert('"',"",String)

it will replace all occurences of " with empty string('')

HTH

[vinaymanchinila]

Finally trim(Colum,'"',"A") works bout it is also taking the space out if the value!
I did try Exchange and convert, have no idea why they are not working, I know they should as I read the documentation
Thanks

[amsh76]

Hey Vinay,

Convert works..i have used it in past, just make sure you are giving arguments in right order..

Convert ('doublequotes','',string)

[pnchowdary]

Hi Vinay,

I just tried the Exchange in a sample server job and it worked fine for me. Its very strange that it is not working for you.

Thanks,
Naveen