topic Re: Saving Excel data to one sheet with multiple tabs in Talend Studio

Saving Excel data to one sheet with multiple tabs

Anonymous — Thu, 30 Aug 2018 20:47:14 GMT

Talend beginner here. I have a job where I need to read data from multiple tabs in an excel file (All tabs have a different schema). Then, I need to filter that data based on certain requirements and export that data to an excel sheet with multiple tabs.

Here is an example of what I was thinking:

I was going to setup up 5 sub-jobs, each with its own metadata based on the corresponding tabs, then save that data to the same excel file with 5 different tab names for the sheets. Then use tRunJob to run all the sub-jobs at once. The big issue I am having is that I am unable to get the data to save to each tab, only the data from the last job is saving.

Not sure if I am on the right track, or completely off base, but any help would be appreciated.

Re: Saving Excel data to one sheet with multiple tabs

Jesperrekuh — Thu, 30 Aug 2018 21:43:04 GMT

Honestly why on earth from excel to the same excel?
Build a macro or a vba script in excel which is more capable and way better in doing this kind of stuff then Talend if you want everything in the same xls as your input... dont even try doing it in Talend... honestly dont. You are not able to open your output and switch between worksheets in talend. It opens and closes, you are only able to append.

1 job with 5 subjobs, as in your example would be the way to go.
Per worksheet (containing a dataset, some rows and columns) you filter and write the output to a specific target sheet. While repeating make sure you append to the file and with every step make sure the output excel is closed (your os will do it for you) for this in appending jobs I always build a 1sec delay between task 1 and the next.

From a user perspective I love excel... from a data-engineer perspective... I despise it.

Re: Saving Excel data to one sheet with multiple tabs

Anonymous — Thu, 30 Aug 2018 22:43:51 GMT

Hi Dijke,

Thanks for the quick response and suggestions. Glad to see I was on the right track with the sub jobs at least. I did want to clarify the job, in case that changes things. There is an excel file that gets placed on a share drive daily; the job would read that file, filter data on certain tabs then create a new excel file with the filtered data.

Our end goal is to have this run automatically so the user won't have to do it, which is why we were looking at Talend. If it's not possible then we can look at other options.

Appreciate the help! Thanks again!

Re: Saving Excel data to one sheet with multiple tabs

Jesperrekuh — Thu, 30 Aug 2018 22:57:31 GMT

Considered using a database and an ODBC connection where they retrieve/download the data by simply refreshing it in a template xls? And within the database / tables you maintain your views ... near future if they want to use PowerBI or another tool its way easier to switch, less impact regarding RFC's

In different companies and different data / bi teams I've worked, this type of solution/workflow is planning for failure and high maintenance. Suggest not to implement it in this way. In 2007 we already had projects to reduce data handling in excel and excel files, even eliminate ms access (forms)..
If somebody is saying 'self service' bi ... this isn't the way.

Just some additional thoughts! Good luck.

Re: Saving Excel data to one sheet with multiple tabs

Anonymous — Sat, 01 Sep 2018 09:31:01 GMT

The big issue I am having is that I am unable to get the data to save to each tab, only the data from the last job is saving.

There is a checkbox in the excelOutput component, where you can select whether you want to create a new file or append

to an existing. You prohably left this checkbox unchecked. So in every subjob a new empty excel file will be created.

That explains why there is just the data from your last run/subjob.

For the first subjob you should leave this checkbox unchecked -> excel with first tab is created.

For runs 2-n select it -> excel will stay as it is and new tabs with data will be appended.within each subjob

regards

excel1.JPG
excel2.JPG

Re: Saving Excel data to one sheet with multiple tabs

Anonymous — Tue, 04 Sep 2018 19:58:13 GMT

Thanks odisys, I was able to get the data to write to separate tabs, appreciate it!