Four Stars

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

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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Forteen Stars TRF
Forteen Stars

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

Sorry for the mistake, this one works:

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

 


TRF
12 REPLIES
Forteen Stars TRF
Forteen Stars

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

Have a look to tSampleRow

TRF
Four Stars

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

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??

Four Stars

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

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

Four Stars

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

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

Forteen Stars TRF
Forteen Stars

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

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

 


TRF
Four Stars

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

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.

Four Stars

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

Hi,

Could you please let me know the solution asap.

 

Thanks in advance

Forteen Stars TRF
Forteen Stars

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

Sorry for the mistake, this one works:

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

 


TRF
Four Stars

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

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.

Forteen Stars TRF
Forteen Stars

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

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.

TRF
Four Stars

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

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 ???

Forteen Stars TRF
Forteen Stars

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

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

TRF