One Star

[resolved] Tmap from Sql to tfilidelimited

Hello.
The problem is that the csv file creates new line if ascii_char(13) and ascii_char(10) is found on adress column
I want to replace all ascii_char(13) and ascii_char(10) from that string.
row1.ADRESS.replaceAll(" "," "); How can I express Ascii Char here?
File is ISO-8859-15.
Thanks in advance.
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

re-plug your tMap and test with row1.ADDRESS.replaceAll("\r\n"," ").replaceAll("\n"," ");
24 REPLIES
Community Manager

Re: [resolved] Tmap from Sql to tfilidelimited

Hi
Do the transform on tMap, type in the following expression in the expression field of column of output table.
row1.ADRESS.replaceAll("\\your Ascii char ","new string")
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hello Shong.
Thanks for your reply.
Not working yet. I am using the folloing expression on the tmap: row1.address.replaceAll("\\ASCII_CHAR(13)","-");
I also try: row1.address.replaceAll("\\CR","-"); for carriage return, but not supported.
Thanks again
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

If your purpose is to replace all carriage return test:
row1.ADRESS.replaceAll("\r"," ");
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

you'll need \n for new line as well
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hello and many thanks for your reply.
Not working: on tmap: row1.ADDRESS.replaceAll("\\r\\n"," ");
result:
Name;address;etc
Ruben;Street
name;etc
I want:
Name;address;etc
Ruben;Street name;etc
Thanks again
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

try row1.ADDRESS.replaceAll("\r\n"," "); instead
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hello and thanks for your reply.
Yes, that almost worked.
For this:
Name;address;etc
Ruben;Street
name;etc
The result is:
Name;address;etc
Ruben;Street name;etc
And now it's ok! Thanks.
For:
Name;address;etc
Ruben;Street
name
and number;etc
Not ok. And I don't understand beacause we are using a "replaceall"
For more than 1 Carriage return it's not working.
Sorry, I am new In Talend.
Thanks again
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Can you post a screenshot with component properties of the component behind row1?
the replaceAll will not work if you put "\n" as row separator because it will do a replaceAll for each line
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Yes,
That's what i was thinking
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

You forgot, it happens ;p
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Here goes the screen shot
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

can you unplugged the tmap and place a tlogrow instead with a screenshot of the result ?
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

can you unplugged the tmap and place a tlogrow instead with a screenshot of the result ?
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Here goes
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

re-plug your tMap and test with row1.ADDRESS.replaceAll("\r\n"," ").replaceAll("\n"," ");
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Perfect.
Thank YOU!
Have a nice Xmax!
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Thx.
That was your present ;p
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

re-plug your tMap and test with row1.ADDRESS.replaceAll("\r\n"," ").replaceAll("\n"," ");

Probably best to do row1.ADDRESS.replaceAll("\r"," ").replaceAll("\n"," "). That way you catch any lone carriage returns.
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hi janhess,
re-plug your tMap and test with row1.ADDRESS.replaceAll("\r\n"," ").replaceAll("\n"," ");

Probably best to do row1.ADDRESS.replaceAll("\r"," ").replaceAll("\n"," "). That way you catch any lone carriage returns.
I have similar problem and tried with your solution. It's almost work but there are two blank spaces everywhere. I've tried with replaceAll(" +",""), but no luck.
Could you help me on this?
Thanks in advance.
Employee

Re: [resolved] Tmap from Sql to tfilidelimited

I have similar problem and tried with your solution. It's almost work but there are two blank spaces everywhere. I've tried with replaceAll(" +",""), but no luck.

Try this : Pattern.compile("\\s*").matcher(s).replaceAll("") !
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hi jizhou.
Thanks for the answer.
I have similar problem and tried with your solution. It's almost work but there are two blank spaces everywhere. I've tried with replaceAll(" +",""), but no luck.

Try this : Pattern.compile("\\s*").matcher(s).replaceAll("") !
I have another problem. How to remove blank space at the front and also comma in between the numbers?
If I put the .trim() or replace() function, the job will return error of NPE.
Could you help me on this matter?
Thanks a lot in advance.
Community Manager

Re: [resolved] Tmap from Sql to tfilidelimited

Hi
There are some null value in the row, try this expression on tMap:
row1.columnName==null?nullSmiley Sadrow1.columnName.trim()).replace(",","")
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: [resolved] Tmap from Sql to tfilidelimited

Hi janhess,
re-plug your tMap and test with row1.ADDRESS.replaceAll("\r\n"," ").replaceAll("\n"," ");

Probably best to do row1.ADDRESS.replaceAll("\r"," ").replaceAll("\n"," "). That way you catch any lone carriage returns.
I have similar problem and tried with your solution. It's almost work but there are two blank spaces everywhere. I've tried with replaceAll(" +",""), but no luck.
Could you help me on this?
Thanks in advance.
Use "" for the replace rather than " ".
Community Manager

Re: [resolved] Tmap from Sql to tfilidelimited

Rozie
I become also fuzzy what you really need to do now,;( can you please email me your job with an example file ? I will see what you have done.
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business