To Eliminate Double Quotes from the Source file
Moderators: chulett, rschirm, roy
To Eliminate Double Quotes from the Source file
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.
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.
-
- Participant
- Posts: 43
- Joined: Mon Jan 15, 2007 10:53 pm
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.
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.
Regards,
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
In the properties of the sequential file give the quote character as "
It will automatically ignore the quote " characters.
Hope it helps.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
-
- Participant
- Posts: 43
- Joined: Mon Jan 15, 2007 10:53 pm
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.
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.
Regards,
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
Using Field String Function
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
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
-
- Participant
- Posts: 43
- Joined: Mon Jan 15, 2007 10:53 pm
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.
Regards,
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
Vivek D. Reddy
__________________________________________
If knowledge can create problems, it is not through ignorance that we can solve them. - Isaac Asimov
-
- Participant
- Posts: 467
- Joined: Tue Mar 20, 2007 6:36 am
- Location: Chennai
- Contact:
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.
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.
Minhajuddin
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>
<a href="http://feeds.feedburner.com/~r/MyExperi ... ~6/2"><img src="http://feeds.feedburner.com/MyExperienc ... lrow.3.gif" alt="My experiences with this DLROW" border="0"></a>