Eight Stars

Need to add extra 2 column in excel

Here's the existing flow in my job.

 

DB --> tMap --> OutputExcel

 

Once the data gets in to the excel (Column A to Column X will be filled), the formula in Column-Y and Column-Z will be calculated.

 

Now, I want this calculated data also move back to DB. In this case, the excel data which moves back to DB is from the Column-A to Column-Z. 

 

Please suggest the solution to achieve it.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Eight Stars

Re: Need to add extra 2 column in excel

I gave like this but error happened

 

java.lang.NullPointerException

 

 

 

Graph13.jpg

12 REPLIES
Twelve Stars

Re: Need to add extra 2 column in excel

@samisyed80,in tmap,you can populate in output shcema,with two extra coulmns.as shown below.Untitled.png

Manohar B
Eight Stars

Re: Need to add extra 2 column in excel

Thanks Manohar, actually the calculation is happening in excel. After calculation i need to grab those values and send it back to DB. In this case, how can i pull only the calculated value? Example:- if the result is 15 for the excel formula "=A+B" then i need to send the value 15 to DB. Please suggest

 

current flow is 

 

DB-->tMap-->OutputExcel

 

Is the below flow i should follow?

 

DB-->tMap-->OutputExcel-->tMap-->DB

Twelve Stars

Re: Need to add extra 2 column in excel

@samisyed80,yes you can do the below way.

 

DB-->tMap-->OutputExcel-->tMap-->DB

Manohar B
Eight Stars

Re: Need to add extra 2 column in excel

actually the calculation is happening in excel. After calculation i need to grab those values and send it back to DB. In this case, how can i pull only the calculated value? i don't want the calculation part to be inserted in talend, but i need only the calculated value.

Twelve Stars

Re: Need to add extra 2 column in excel

@samisyed80,you can check the caluculated value is greated than 0 you can populated to DB in tMap itself.

Manohar B
Eight Stars

Re: Need to add extra 2 column in excel

how to pull the calculated value to tMap? Apologies, i am new to talend.

Twelve Stars

Re: Need to add extra 2 column in excel

@samisyed80,find Untitled.pngbelow.

Manohar B
Eight Stars

Re: Need to add extra 2 column in excel

see the flow and formula. I need to grab the value from "Denominator and Numerator" column to DB. Output is not yet placed, but before output DB i have placed a tMap. See the screenshot. Is this fine? I still don't get how the calculated value will come here.

 

 

Nine Stars

Re: Need to add extra 2 column in excel

Hi Samisyed,

If i understand correctly, first you need to load records to excel file and excel will calculate to columns by using formula. Now you need to read all populated columns from excel and two calculated fields as well correct?

 

If yes, The first load excel file with a-x columns and then use target excel as source.Now in source include y,z columns as well. Use target as DB table and update new columns based on key column (if you have ) else insert all columns into target DB.

 

hope this helps!

 

Regards,

 

Veeru Boppudi
Eight Stars

Re: Need to add extra 2 column in excel

Yes Veeru, you understanding is right. This is what i want. Thanks for your suggestion. I will do it that way.

Eight Stars

Re: Need to add extra 2 column in excel

I gave like this but error happened

 

java.lang.NullPointerException

 

 

 

Graph13.jpg

Twelve Stars

Re: Need to add extra 2 column in excel

@samisyed80,may be you were getting null pointer exception for other coulmns not to these.can you check?

Manohar B