topic Re: Problems to handle dirty data in Talend Studio

Problems to handle dirty data

lcupito — Fri, 15 Nov 2024 22:16:58 GMT

Hello,

i'm trying to handle a dirty data that i have in a csv file. I have an integer column but some values are "n.a."

i've tried to handle this error in a tmap but i couldn't do that.

Re: Problems to handle dirty data

Anonymous — Fri, 09 Dec 2022 19:20:14 GMT

Can you send us more information on this? We will need to see an example of the data that you are using (an example, not private data) and a couple of screenshots of your job, so that we can see what you are doing at the moment.

Re: Problems to handle dirty data

jlolling — Sun, 11 Dec 2022 22:23:28 GMT

Yes, this is sometimes a mess. In your case I suggest to use as column type of the csv source column String as type.

You can use the component tConvertType and on its output set the actual integer columns as Integer typed.

Take care the tConvertType is not set to Die on Error and you can also lead the mistaken values to a reject flow.

Another solution is using a routine.

I have created a lot of them and you can use the source code as you like. E.g. in your use case you can use the NumberUtil and here the method getFailSaveInt(...) in the tMap to convert the String fail-save into a Number.

Take a look in this project and here in the source folder src/routines: https://github.com/jlolling/talend_routines

To install a routine create a new routine with the same name you see here and after successfully create (with a dummy method) replace the whole content with the content of the source code in this project.

Re: Problems to handle dirty data

lcupito — Mon, 12 Dec 2022 09:10:30 GMT

Hi,

this is the jobthe tmap structure

dirty data i have

the errors

Re: Problems to handle dirty data

jlolling — Mon, 12 Dec 2022 10:04:04 GMT

Ok, I would put right after the tFileInputDelimited a tConvertType component and setup the problematic column as String typed and the outgoing column of the tConvertType as integer column.

Re: Problems to handle dirty data

jlolling — Mon, 12 Dec 2022 10:09:16 GMT

Or you install the routines I have described and use them in the expression of the output.

Re: Problems to handle dirty data

Anonymous — Mon, 12 Dec 2022 13:08:46 GMT

@Luca Cupitò it looks like @Jan Lolling got here first. I'd do exactly the same as he suggested. He deserves a best answer for this 😉

Re: Problems to handle dirty data

RJLC — Wed, 14 Dec 2022 22:43:38 GMT

Maybe you can use tReplace between tFileInputDelimited and tMap and replace “n/a” value by a valid value. Sample as 0.

the integer field must be defined as a string in the tFileInputDelimited, for uses it into the tReplace component, and convert it after to integer when do you need it.

best regards