To Eliminate Double Quotes from the Source file

[sanpete]

I have a source file with a columns namely, Id and Name. The name comes with First name and Last name with quotes. I would like to Trim the string by eliminating the double quotes.

Input
ID Name
101 John "Mathews"
102 Leander "Pius"

Required Output
ID Name
101 John Mathews
102 Leander Pius

I would really appreciate if anyone could provide me the solution ASAP.

[DSguru2B]

Welcome Aboard
Please avoid using words that demand urgency. This will only delay your response. Since you are new I will cut you some slack.
You can look into the CONVERT() function in the transformer stage.

[vivekreddy]

While you could always use the Transformer stage to remove the "" characters, in the Sequential File stage itself, there is an option that specifies that variable length fields are enclosed in single
quotes, double quotes, or another character or pair of characters. Of course in this case, it isn't clear whether the name and the surname are different fields by themselves, or simply formatted differently within the same field.

If they are different fields, then this option could be of assistance, if not, then the transformer stage would be your best option.

[Minhajuddin]

The easiest way to do what you want is:

In the properties of the sequential file give the quote character as "

It will automatically ignore the quote " characters.

Hope it helps.

[DSguru2B]

No. The sequential file properties "quote character" is something totally different. The OP gave us sample data that shows very clearly that the data has two fields, the "ID" and the "Name". The name has quotes within the data, inpite of surrounding the data. Hence the quote character option within the sequential file stage is rendered useless.

[Minhajuddin]

Oh I am sorry!

I thought the quotes were just around the data, Not in between.
I should have read it clearly before posting the answer.
And as already said you can use the CONVERT() function.

[vivekreddy]

Hi.

My mistake I didn't go though the actual metadata information.

The transformer is your ONLY option at this juncture. The Convert function should be helpful

Convert
- Converts specified characters in a string to designated replacement
characters

- parameters: fromlist (string) tolist (string) expression (string)

- result (string)

Your fromlist could contain the "" characters, whereas your tolist could contain the null character.

[sanpete]

Hi All,

Thanks for all your valuable information.

I have used Field String function to eliminate Double Quotes from the Name Field,
ie. If the name comes in as John 'Doe' the Result should be John Doe

Solution is : Field(String,'"',1):" ":Field(String,'"',2)

It Works :D

Thanks

[chulett]

It may 'work' but Convert would be a 'better' as in a more efficient answer.

[vivekreddy]

Craig is right there. Your solution is a good workaround, but it is always wise to understand how a problem can be best solved, and not just solved for the sake of solving it.

[Minhajuddin]

And moreover if you have data which has more than one quote your logic wouldn't work.

Say you have

--> Goo"d L"ogic

as the data.

And if you apply your logic the result will be

--> Good L

Which is not the right answer and as Chulett and vivek say it's not efficient. So use of CONVERT() would be the best option for you.

[sanpete]

I agree that the point you have mentioned about solving it with efficiency rather just solve it. I have used CONVERT() Function and it works.

Thanks Again,

Pete

[DSguru2B]

Great. You can mark the post as "Resolved" then.