topic Re: Replace special characters on multiple columns in Talend Studio

Replace special characters on multiple columns

Anonymous — Thu, 21 Jan 2016 11:48:58 GMT

Hi Shong,
I have a situation,
i am having source table which contains lots of special characters in multiple columns.
i handled for single column using tReplace component but its very lengthy to take every column in tReplace which contains Special characters.
Is there any other way to handle multiple columns at a time for special characters.
This is the list of Special Characters that i have in my data and Replace_By value for every special characters

Waiting for positive reply.
Thanks,
Shri_Kul1
Talend User

Re: Replace special characters on multiple columns

Anonymous — Sat, 23 Jan 2016 12:49:52 GMT

Hi
Take a look at tReplaceList component, this component allows you to replace special characters for multiple columns at a time.

Re: Replace special characters on multiple columns

Anonymous — Mon, 25 Jan 2016 06:15:07 GMT

Hi Shong,
Thanks for your reply, but i am not able to solve the issue using tReplaceList component , the values should replace with original are not replacing.
i think i am missing something over there. could you please help me out on the same?
This is my job design... and the settings i applied in tReplaceList component.
i want to replace values of NAME column to values of NAME_A.

Re: Replace special characters on multiple columns

Anonymous — Tue, 26 Jan 2016 04:04:51 GMT

Hi
The file you provided in your first post will be lookup table. In this case, the search column is SPL_CHAR, the replacement column is REPLAY_BY.

Re: Replace special characters on multiple columns

Anonymous — Wed, 27 Jan 2016 06:32:51 GMT

Hey Shong,
Thanks for your reply. I am trying same as you told , i took that file as lookup & the search column is SPL_CHAR & the replacement column is REPLACE_BY but there is problem that job giving error regarding null values in REPLACE_BY column.

Actually i am having Null values in REPLACE_BY column so how could we handle this?
Kindly Suggest.

Re: Replace special characters on multiple columns

Anonymous — Mon, 08 Feb 2016 07:41:03 GMT

Hi,
Before passing to tReplaceList , pass "replace by row" to tmap and replace null values by " ". So you won't get null pointer exception.

Re: Replace special characters on multiple columns

Anonymous — Tue, 09 Feb 2016 05:39:11 GMT

Hi Amol,
Could you plz elaborate the logic you are telling me to implement?

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 05:08:37 GMT

Hi SK,
My logic is to avoid null pointer exception. In above post, "Replace by" Column in excel contains null value. So, I thought that may be that is reason. In tmap ,use this expression row1.column==null?"":row1.column before mapping. so You can avoid null pointer exception

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 05:59:15 GMT

Hi Amol,
Thanks for your reply, yes That column contains null values in Lookup file. but that lookup file need to connect to tReplaceList component not to tMap & that null values are coming from Lookup File.
As per you suggestions i made job like this. But its not correct i guess.
correct me if am doing anything wrong.

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 06:34:29 GMT

Hi SK,
I think, Metadata1 contains null value. Your job is correct. Only change is that Connect row3 to tReplaceList instead of tMap Becoz tReplaceList expects two input and you have connected only one input.
Note: In tMap, replace null value by "" as per above post.
Let me Know if it works.

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 06:39:58 GMT

Hi Amol,
No, Metadata1 doesn't contain any null values. Null values are in metadata file which is lookup value and contains replace by column which contains null values.
My original job is like this.

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 07:03:45 GMT

Hi Sk,
I will try to make rough job.
metadata1 ------------- tReplaceList --------------------------- tLogRow
|
tMap
|
metadata
Now, try to implement this logic and make sure that replace null values in tMap before mapping.
I hope, it will work.
Regards,
Amol

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 07:40:11 GMT

Hi Amol,
I already tried this but its not working...it still giving null pointer exception.

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 09:13:54 GMT

Hi SK,
I try to see another workaround and Let u know if it is possible.
Regards,
Amol

Re: Replace special characters on multiple columns

Anonymous — Wed, 10 Feb 2016 12:33:00 GMT

Hi Amol,
Sure.

Re: Replace special characters on multiple columns

Anonymous — Mon, 15 Feb 2016 12:50:04 GMT

Hi Everybody,
I need help from you,
I am trying to implement an ETL job. I have REMARK column in target table which holds remarks for every column if that particular column failed at validation.
Ex - Suppose that I Have ID column in source (Excel), it should hold only Numeric values but in case if it contains any character then that particular record should go under error table and Remark column hold remark that"ID should be Numeric."
Like wise, i need to implement it for multiple columns & REMARK column should be updated for every column if they all got failed at validation.
like Date Fields - Start_Date Should be less than End_Date.
Description Field - Should contain only 1000 characters max to max.
Any Required (*) field should not be null.
Boolean fields contain only TRUE & FALSE in capital form, should not contain any 'Yes', 'No',123 or null

Waiting for positive reply..

Re: Replace special characters on multiple columns

Anonymous — Thu, 18 Feb 2016 06:50:21 GMT

Hi All,

I have done it after all, Please see image attached here and solution for it as follows-
1)Sort out data on the basis of EXPECTED & REJECTED records through your business rule like NAME should not be null if it is null then it should go under ERROR table. After that take 1 variable and put this logic - Source.triNameTX==null?"(triNameTX Should not be Null)":"" and use this variable in REMARK column of ERROR table.
2)START date should less that END date - Var2- (Source.triSTARTDA != null && (Source.triSTARTDA.before(Source.triENDDA))) ||
Source.triSTARTDA == null (Boolean Type)
Var3 -(Source.triSTARTDA!= null && (Source.triSTARTDA.after(Source.triENDDA)))||Source.triSTARTDA == null?"(triSTARTDA Should'nt be NUll or Less than triENDDA)":""
3) Now, Concatenate Var.var1+Var.var2 and run your job & after completion of your job take look into REMARK column , you will see -(triNameTX Should not be Null)(triSTARTDA Should'nt be NUll or Less than triENDDA).
so like wise you can do it for many columns as per your Business Rule.

Rejected Output File..

Re: Replace special characters on multiple columns

Anonymous — Thu, 18 Feb 2016 22:21:09 GMT

tGroovyRow allows you to do row-based processing in the Groovy language (essentially a superset of Java). Since the tGroovyRow component gets its input as a HashMap named input_row, you can process each field of each row as a closure. That makes it very efficient to apply the same search/replace or other transformations to multiple fields on the same row.
See below for how I use this technique to remove all carriage returns and newlines from each field of type String. I'm also doing a null check and transforming that into an empty String before doing the replace operations.
FYI, to use this, you'll need " tGroovyRow (updated)" (available at this link). It should be available in Talend Exchange soon once approved. I've tested it in TOS 6.1.1, Groovy 2.4.5 (bundled in my updated version), and Java 1.8.
The original " tGroovyRow" is available in Talend Exchange, but I needed to tweak it before it would run in TOS 5.6.x and it uses the default version of Groovy (1.0) bundled with TOS.

 input_row.each { key, value ->
    if (value.getClass() == String) 
	value = value?.replace("\r","").replace("\n","")
    output_row = value
 }

Re: Replace special characters on multiple columns

Anonymous — Fri, 19 Feb 2016 08:03:03 GMT

Hi dbh,
i have downloaded new version of tGroovyRow, could you plz tell me how to install it??

Re: Replace special characters on multiple columns

Anonymous — Fri, 19 Feb 2016 14:01:45 GMT

Here is a link with instructions https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en