topic Re: How do i replace 2.000- to 2.000 in Talend Studio

How do i replace 2.000- to 2.000

gouravd_7 — Sat, 16 Nov 2024 07:18:25 GMT

Hi Team,

PLease help me to replace the numbers in table like 2.000- to -2.000 in talend job.

Please find the attached screenshot of the job.

Thanks,

Gourav Dubey

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 12:36:22 GMT

Hi,

My first query will be do you have any data quality issues in your source DB table? Ideally the data cleansing should always happen from source rather than from an interim layer.

Now, if the source is not ready to cleanse it and will continue to send data in wrong format like "2.000-" (I assume it is a string data type) then first thing you will have to do is to pass the data to a tmap/tjavarow.

The code I used is as shown below. If needed, you can convert the output string value to other data types once the cleansing is complete.

row1.input.contains("-")?"-"+row1.input.replaceAll("-", ""):row1.input

If the reply has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 12:51:44 GMT

Hi Nikhil,

Thanks for the quick response. Really appreciate it.

My Source column is string and target column is float so the above code is giving me error.

Please find the attached screenshot.

Thanks & Regards,

Gourav Dubey

1JPG.JPG

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 13:04:21 GMT

Hi Gourav,

Please use tconverttype after tmap to convert the value from String to Float once the cleansing is complete.

Below link will give the details about this component.

https://help.talend.com/reader/hm5FaPiiOP31nUYHph0JwQ/GDoeGgb~qKpyQ_84gB37WQ

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 13:11:07 GMT

Hi Nikhil,

tConvertype doesnt have output so we cant use it after Tmap and before tMap , have tried using it multiple times but it doesnt converts anything to my bad luck.

Please help me with some other solution.

Thanks,

Gourav Dubey

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 13:16:12 GMT

Hi Gourav,

I was able to do it. You will have to add the schema details for both input and output.

I didn't worry about the precision of my output but you can handle it also f you want.

I would suggest you to go through sample scenarios mentioned in Talend Help link where this component is being used. It will help you to get better familiarity of the component.

If the answer has helped you, could you please mark the topic as resolved? Kudos are also welcome 🙂

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 13:22:32 GMT

Hi Nikhil,

Please find the below log after following the above process

Starting job quickcheck at 18:48 14/11/2018.

[statistics] connecting to socket on port 3359
[statistics] connected
Exception in component tMSSqlInput_1 (quickcheck)
java.sql.SQLException: Value 2.000- cannot be converted to REAL.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:752)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getFloat(JtdsResultSet.java:683)
at sga_data_lake.quickcheck_0_1.quickcheck.tMSSqlInput_1Process(quickcheck.java:12746)
at sga_data_lake.quickcheck_0_1.quickcheck.runJobInTOS(quickcheck.java:16732)
at sga_data_lake.quickcheck_0_1.quickcheck.main(quickcheck.java:16390)
[FATAL]: sga_data_lake.quickcheck_0_1.quickcheck - tMSSqlInput_1 Value 2.000- cannot be converted to REAL.
java.sql.SQLException: Value 2.000- cannot be converted to REAL.
at net.sourceforge.jtds.jdbc.Support.convert(Support.java:752)
at net.sourceforge.jtds.jdbc.JtdsResultSet.getFloat(JtdsResultSet.java:683)
at sga_data_lake.quickcheck_0_1.quickcheck.tMSSqlInput_1Process(quickcheck.java:12746)
at sga_data_lake.quickcheck_0_1.quickcheck.runJobInTOS(quickcheck.java:16732)
at sga_data_lake.quickcheck_0_1.quickcheck.main(quickcheck.java:16390)
[statistics] disconnected
Job quickcheck ended at 18:49 14/11/2018. [exit code=1]

Also, please find the attached screenshot of the job after 6 rows getting same error again.

2.JPG

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 13:27:27 GMT

Hi,

It seems you are reading the data in tMSSQLInput with wrong data type. Could you please cross check the data type of source table and align your schema in Talend input component accordingly?

I believe you need to read that column as a string. Could you please cross checkthe DDL of that source table and the source table schema you have created in tMSSQLInput component?

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 13:37:37 GMT

Hi Nikhil,

In source its varchar (100) and in target its INT.

We cant change the target db from our side.

Thanks,

Gourav Dubey

Re: How do i replace 2.000- to 2.000

TRF — Wed, 14 Nov 2018 13:48:36 GMT

If the target is an int, use this one:

Integer.parseInt(
    (row10.input.contains("-") ? "-" + row10.input.replaceAll("-", "") : row10.input).replaceAll("\\..*$", "")
)

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 14:22:44 GMT

Hi Team,

I am still getting the same error

[statistics] connecting to socket on port 3802
[statistics] connected
Exception in component tMSSqlInput_1 (quickcheck)
java.sql.SQLException: Value 2.000- cannot be converted to REAL.

Thanks & Regards,

Gourav Dubey

Re: How do i replace 2.000- to 2.000

TRF — Wed, 14 Nov 2018 14:31:23 GMT

The expression mut be placed int o the tMap.

The job design must look like this:

tMSSqlInput --> tMap --> tMSSqlOutput

As the erro come from your tMSSqlInput_1 component, can you share its Settings?

Re: How do i replace 2.000- to 2.000

TRF — Wed, 14 Nov 2018 14:31:33 GMT

The expression mut be placed int o the tMap.

The job design must look like this:

tMSSqlInput --> tMap --> tMSSqlOutput

As the error come from your tMSSqlInput_1 component, can you share its Settings?

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 14:31:54 GMT

Hi Gourav,

Could you please share the screenshot of the tMSSQLInput component's schema? I am interested in the datatype you have added for this column in the schema.

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 14:52:34 GMT

Hi Team,

Attaching the job so that you can cross check the same.

Also. I did the mapping of all 113 columns again and some how the issue with input component resolved and am getting this error now

Type mismatch cannot convert from int to string.

Please find the attached zip file of the job and screenshot of the error where we are getting this issue

newjob.zip
3.JPG

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 14:58:51 GMT

Hi,

Could you please add column names in the select query and once they are added correctly, could you please try Guess schema?

I believe you have missed some column in schema which is of integer type in source table.

Warm Regards,

Nikhil Thampi

Re: How do i replace 2.000- to 2.000

TRF — Wed, 14 Nov 2018 14:59:48 GMT

You must CAST from Int to String into your query by yourself.

Example:

SELECT CAST(yourIntColumn AS VARCHAR) from yourTable

Edit: you must CAST the numeric fields as soon as you want them into a String field

Tip: to get the table schema without having to enter a complete SELECT, enter the query as "SELECT * FROM yourTable" then click the "Guess schema" to get the schema of the table and finally, click the "Guess query" to generate the corresponding SELECT statement (this suppose your SQL Server connection doesn't use context or global variables which are setted at runtime - in this case, use a dedicated component for design with the connection element hard coded and copy/past both resulting schema and query into the component included into your job)

Re: How do i replace 2.000- to 2.000

gouravd_7 — Wed, 14 Nov 2018 15:14:03 GMT

Hi Team,

As per the instructions , I have added the columns with cast statements in the query mentioned in above mail chain still facing the same issue .

Thanks & Regards,

Gourav Dubey

3.JPG

Re: How do i replace 2.000- to 2.000

TRF — Wed, 14 Nov 2018 15:21:30 GMT

Your tMap output is String (for the fields where you want to remove the final "-") so it doesn't make sense to try to convert to Integer.

It's your responsability to clean up your job before to say "it doesn't work".

Re: How do i replace 2.000- to 2.000

Anonymous — Wed, 14 Nov 2018 15:22:24 GMT

Hi Gourav,

You seems to be changing at wrong area. @TRF has advised you to convert the integer columns to String within select statement and then onlyasking you to allocate to a STring data type in the schema mentioned in the tMSSQLInput component.

If you have a integer data type in underlying database, you have two options.

a) convert them to string in SQL statement itself and pass it to string variables in the schema mentioned in tMSSQLInput component

b) allocate the column as integer datatype itself in both select statement and schema. If needed, convert to other data types later in your flow.

Warm Regards,

Nikhil Thampi