Need to add extra 2 column in excel

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.

 

 


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


All Replies
Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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.

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.
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.

Forteen Stars

Re: Need to add extra 2 column in excel

@samisyed80,find Untitled.pngbelow.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
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
Highlighted
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

Forteen 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
Don't forget to give kudos/accept the solution when a replay is helpful.

15TH OCTOBER, COUNTY HALL, LONDON

Join us at the Community Lounge.

Register Now

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch