topic Re: Need to add extra 2 column in excel in Talend Studio

Need to add extra 2 column in excel

Anonymous — Tue, 15 May 2018 12:09:03 GMT

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.

Re: Need to add extra 2 column in excel

manodwhb — Tue, 15 May 2018 12:27:07 GMT

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

Re: Need to add extra 2 column in excel

Anonymous — Tue, 15 May 2018 12:33:57 GMT

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

Re: Need to add extra 2 column in excel

manodwhb — Tue, 15 May 2018 12:35:57 GMT

@samisyed80,yes you can do the below way.

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

Re: Need to add extra 2 column in excel

Anonymous — Tue, 15 May 2018 12:44:45 GMT

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.

Re: Need to add extra 2 column in excel

manodwhb — Tue, 15 May 2018 12:49:03 GMT

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

Re: Need to add extra 2 column in excel

Anonymous — Tue, 15 May 2018 12:57:08 GMT

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

Re: Need to add extra 2 column in excel

manodwhb — Tue, 15 May 2018 13:04:44 GMT

@samisyed80,find below.

Re: Need to add extra 2 column in excel

Anonymous — Tue, 15 May 2018 13:12:04 GMT

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.

Graph10.jpg
Graph11.jpg
Graph12.jpg

Re: Need to add extra 2 column in excel

vboppudi — Tue, 15 May 2018 22:16:14 GMT

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,

Re: Need to add extra 2 column in excel

Anonymous — Wed, 16 May 2018 06:09:49 GMT

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

Re: Need to add extra 2 column in excel

Anonymous — Wed, 16 May 2018 10:00:23 GMT

I gave like this but error happened

java.lang.NullPointerException

Re: Need to add extra 2 column in excel

manodwhb — Wed, 16 May 2018 10:48:58 GMT

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