topic Re: Clean accented character and white space in column in Talend Studio

Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 11:27:14 GMT

I have a workflow as follows. In the column 'summary', i want to remove

1. question mark(?)
2. white space from the text
3. replace accented alphabets with the english equivalent. For example é into e.

Input

?? at Shenzhen Xingjiexun Electronics Co.Ltd
Designer at FabUnion | ????????
Jinanhaolu Ñ manager

Output

at Shenzhen Xingjiexun Electronics Co.Ltd
Designer at FabUnion |
Jinanhaolu N manager

For the accented alphabet, above is just a sample as it can be anything and i do not have a finite list to produce for an example.

Thanks in advance!!

Re: Clean accented character and white space in column

vboppudi — Tue, 02 May 2017 12:33:44 GMT

Hi,

Please provide some sample data and expected output.

Regards,

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 12:35:08 GMT

Hi,

Here is an example of howto:

1st, load the commons-lang3-3.4.jar file and import org.apache.commons.lang3.StringUtils.

For that, in tLibraryLoad Basic settings select "commons-lang3-3.4.jar", then in Advanced setting enter import "org.apache.commons.lang3.StringUtils;" in the import field.

In tJavaRow, enter the following (maybe something similar in tMap depending on your use case):

output_row.line = StringUtils.stripAccents(input_row.line);

tFixedFlowInput is here to generate data for the flow ("aaaéééàààçççbbbb" for my example), and the result is:

aaaeeeaaacccbbbb

Hope this helps,

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 12:39:56 GMT

Sorry, I forgot "?" and space.

Just replace:

output_row.line = StringUtils.stripAccents(input_row.line);

with:

output_row.line = StringUtils.stripAccents(input_row.line).replaceAll("[? ]", "");

That's all.

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 13:48:41 GMT

How should i connect tLibraryLoad and tJavaRow in my workflow?

should it be as follows? Please suggest if i should arrange this palettes in different way.

tMap -> tLibraryLoad -> tJavaRow -> tFileOutputDelimited

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 14:03:05 GMT

well, if you just want to remove starting white spaces (not all) just use:

output_row.line = StringUtils.stripAccents(input_row.line).replaceAll("[?]", "").replaceAll("^ ", "");

maybe exists a shorter form, but it works:

Regards,

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 14:10:16 GMT

Usually, place the tLibraryLoad at the bebenning of the job.
In my example, because there is nothing else inthe job, it's the 1st component and the following tFixedFlowInput is connected with a trigger onSubjobOk (or onComponentOk).

Don't forget to indicate the topic as solved (if it's) - also Kudos are welcome

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 14:45:16 GMT

I downloaded the jar file from http://book2s.com/java/jar/c/commons-lang3/download-commons-lang3-3.4.jar.html and tried working with the suggested solution and made tLibrary as first component. Below is how tLibraryLoad is configured

Basic SettingsAdvanced settings

And this is how tJavaRow is configured. I added the column name 'summary' after output_row and input_row in the code as follows

However, i am getting error

Execution failed : Job compile errors 
At least job "Test2_Copy" has a compile errors, please fix and export again.
Error Line: 49
Detail Message: Syntax error on token ""org.apache.commons.lang3.StringUtils;"", delete this token
There may be some other errors caused by JVM compatibility. Make sure your JVM setup is similar to the studio.

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 15:05:24 GMT

you must load the library first: tLibraryLoad - onSubjob OK -> tFileList

also verifiy Advanced setting of tLibraryLoad. It must contain import org.apache.commons.lang3.StringUtils; in the Import field.

Edit: OK, forget, just remove both " in the Import field (that's Java code, not just a string)

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 15:24:14 GMT

I inserted import org.apache.commons.lang3.StringUtils; in the advanced settings field and it ran without any error, however the output is not what i need. It simply replace accented Ñ with a question mark ?

Shenzhen WenTong electronic co.Ltd Ñ power adapter

is converted into

Shenzhen WenTong electronic co.Ltd ? power adapter

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 15:40:18 GMT

What's the encoding of the tFileInputDelimited?

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 15:42:32 GMT

UTF-8

@TRF wrote:
What's the encoding of the tFileInputDelimited?

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 15:54:46 GMT

But is your file encoded as utf8?
I just tested on my side and it works fine.

Re: Clean accented character and white space in column

vboppudi — Tue, 02 May 2017 16:06:39 GMT

Hi,

The following steps might helps you.

Step1: Change file read encoding

Step2: Create new routines stripAccents with below script.

package routines;
import java.text.Normalizer;
public class stripAccents {

public static String stripAccents(String s)
{
s = Normalizer.normalize(s, Normalizer.Form.NFD);
s = s.replaceAll("[\\p{InCombiningDiacriticalMarks}]", "");
return s;
}
}

create job src--> tMap--> tLogRow

COL as input in Source and row1.COL as in put in tMap. COL as output in tMap.

output COL --> stripAccents.stripAccents(row1.COL).replaceAll("[?]", "").replaceAll("^ ", "")

Input Data:

?? at Shenzhen Xingjiexun Electronics Co.Ltd
Designer at FabUnion | ????????
Jinanhaolu Ñ manager
aaaéééàààçççbbbb
Shenzhen WenTong electronic co.Ltd Ñ power adapter

Output Data:

Hope this helps!

Regards,

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 16:17:37 GMT

@TRF can you post screenshot? @vboppudi file is in UTF-8 format and if i change the format in input, file is not read properly, I faced this issue and it took me a week to understand the reason and after i switched to UTF-8, data was read properly.

Re: Clean accented character and white space in column

vboppudi — Tue, 02 May 2017 16:20:58 GMT

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 16:30:19 GMT

Here is the job with the tFileInputDelimited:

The Advanced settings tab of the tFileInputDelimited:

The input file with the Encoding menu (from Notepad++):

Finally, the result:

@Enthusiast, let us know the encoding system for your file.

Regards,

Re: Clean accented character and white space in column

Anonymous — Tue, 02 May 2017 17:00:46 GMT

Its appearing as ANSI when i open it in Notepad++

Re: Clean accented character and white space in column

TRF — Tue, 02 May 2017 18:27:39 GMT

So just select ISO-8859-15 as the encoding system in the Advanced settings tab.

It works (I've tried).