topic Re: how to load only records within a specific range from a csv file into a oracle table in Talend Studio

how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 16 Nov 2024 08:01:22 GMT

Hi Guys,

I have a requirement where there is a csv file as input for example consider its having 1000 records

with the below schema

emp_name,emp_sal,emp_city

xyz,1000,hyderabad

abc,2000,delhi

mnp,3000,bangalore

I have to load the data into a oracle table but only specific records within a range
(ex : 200 to 350,500 to 875,etc) I need to give the range at run time using context variables.

how can I achieve that?
does tFileRowCount supports that or is there any solution??

quick reply would be appreciated

Re: how to load only records within a specific range from a csv file into a oracle table

TRF — Sat, 30 Jun 2018 06:47:14 GMT

Have a look to tSampleRow

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 07:01:56 GMT

Hi,

Thanks for the reply.

I had a look at tSampleRow component but in this we have to hard code the values which i don't want to.

But my requirement is I need to specify the range dynamically when the job runs.

Today I have a req to load records between 300 to 400 .

tomorrow the req can be to load between 600 to 900,etc like this.

So how can I achieve this??

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 07:12:56 GMT

Hi,

Please find attached screenshot of my job. I tried using context variables prompting them at run time for the range values. still its not giving the output

samplerow.PNG

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 07:45:58 GMT

Hi,

PFA screenshot.

Please if any one knows let me know where I'm doing wrong?
I tried to display the values of context variables and they are getting displayed on the console but none of the rows are moving to tlogrow from tsamplerow

Re: how to load only records within a specific range from a csv file into a oracle table

TRF — Sat, 30 Jun 2018 09:54:27 GMT

context.From+".."+context.To

may the better is to have a single context variable like context.Interval so you can define the desired interval as you want such as:

100..200,500..1000,1500,2000

Should work

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 15:10:47 GMT

Hi,

I tried as you told like keeping the expression

context.from+".."+context.to in tSampleRow and I got the above error.

PFA screenshot for the error.
both the context variables are of type Integer.

Please let me know for any details.

3.PNG

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 15:21:59 GMT

Hi,

Could you please let me know the solution asap.

Thanks in advance

Re: how to load only records within a specific range from a csv file into a oracle table

TRF — Sat, 30 Jun 2018 17:53:48 GMT

Sorry for the mistake, this one works:

new String (context.From + ".." + context.To)

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 18:03:03 GMT

Hi,

Sorry It didn't work.

PFA screenshot.

tJavaRow code : context.interval = new String(context.from+".."+context.to);

tSampleRow code : context.interval

All the 3 variables are of type String

But still it didn't resolve my issue.

4.PNG

Re: how to load only records within a specific range from a csv file into a oracle table

TRF — Sat, 30 Jun 2018 18:12:10 GMT

You don't need a tJavaRow. Just put the piece of code as the Range value for the tSampleRow component.
It works, I just tried it.

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Sat, 30 Jun 2018 18:29:27 GMT

Hi,

Thanks for your patience. it worked.

But my question is why it didn't work if we do in other way like

context.interval = context.from+".."+context.to; ----> in tJavaRow

and use the

context.interval -----> in tSampleRow ???

Re: how to load only records within a specific range from a csv file into a oracle table

TRF — Sat, 30 Jun 2018 19:09:35 GMT

The variable used as Range must be set before the subjob including the tSampleRow starts.

So, if you have a tJava before this subjob, it works too:

tJava--(onComponentOK)-->tFileInputDelimited-->tSampleRow-->tLogRow

Re: how to load only records within a specific range from a csv file into a oracle table

Anonymous — Wed, 12 Dec 2018 08:21:48 GMT

Hi,

If it is on number range suppose source field of type double divided by lookup field of type double and the result should be in between the range 0.1 to 5.0 and should not be in the range 5.1 to 8.9 how to do this in the talend expression filter while mapping in tmap?