topic Re: [resolved] Writing to two sheets in the same excel file from one job in Talend Studio

[resolved] Writing to two sheets in the same excel file from one job

Anonymous — Thu, 25 Sep 2014 15:38:25 GMT

Hi all,
I have a problem writing to two different sheets in the same excel file from the same job, below is the scenario :
tFileInputExcel - > tMap -> tReplicate .. replicate1 .. -> tFileOuputExcel(sheet1) .. replicate2 .. -> tMap -> tFileOuputExcel(sheet2)
tFileOuputExcel is the same file.
If I change one of the tFileOutputExcel file name to a different file name, then both files are created with the respective sheet names(sheet1, sheet2), however they are in different files which does not suite my purpose.
Is this a bug in Talend ?

Re: [resolved] Writing to two sheets in the same excel file from one job

rbaldwin — Thu, 25 Sep 2014 15:56:45 GMT

Hello,
You can write to multiple sheets in the same spreadsheet, but they need to be done in different subjobs. From what you describe, you could take the tMap output and push it into a tHashOutput (instead of the tFileOutputExcel), then in another subjob have a tHashInput go to the second sheet in the spreadsheet.
Hope that helps.

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Thu, 25 Sep 2014 16:22:44 GMT

This is one of the reason I build my own Excel components to:
write multiple sheets in one job
read templates and write into
reuse formattings
write formulas
and a lot more...
Take a look at the tFileExcelSheetOutput component (in conjunction with tFileExcelWorkbookOpen and tFileExcelWorkbookSave).
These components are available at Talend Exchange (with documentation)
http://www.talendforge.org/exchange/index.php and search for tFileExcel

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Fri, 26 Sep 2014 10:25:37 GMT

Thank you for the responses. I would prefer to do it in the same job as it is already a subjob. I will try the tFileExcelSheetOutput combo first ! Let you folks know how it goes shortly.
Perfect !! Thanks folks, used the exchange fileexcel components and they work much better.

Re: [resolved] Writing to two sheets in the same excel file from one job

jsubashini — Mon, 18 Jan 2016 09:54:01 GMT

Hi Jan,
I am currently using your excel components to write multiple sheets to the same spreadsheet. I have subjobs each writing to one sheet but I am usig a tparallelize to run all the subjobs. At then end of the job, I dont see all the sheets. Its not consistent. Even though I'm saving the workbook and opening in every subjob it doesnt seem to save some sheets. do you have any suggestions?
Thanks
Shuba

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Mon, 18 Jan 2016 17:45:13 GMT

Hi, you cannot write into one excel workbook with multiple threads. My component does not recognise such situation yet but it impossible because you write actually into one huge XML file and the Apache POI API also does not allow multi threading - it is NOT thread save!
I cannot predict what will happen if you try this, but to get an inkonsistent document is for sure a worst case scenario.

Re: [resolved] Writing to two sheets in the same excel file from one job

jsubashini — Mon, 18 Jan 2016 20:11:09 GMT

Thanks for your reply. That's correct. I get inconsistent document every time. I guess have to end up adding the sheets sequentially and think of other ways to speedup.

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Mon, 18 Jan 2016 21:09:06 GMT

Sorry for not having a better solution. If you have to create multiple documents, you can run in parallel these processing but not within one document.

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Thu, 11 Feb 2016 17:18:27 GMT

Hi Jan,
I took your suggestion and I am using your custom components. tFileExcelWorkbookOpen, Save and tfileExcelSheetOutput. It was working great as long as I was creating .xls files. Yesterday we had a requirement to switch to .xlsx instead. and when I changed it in the jobs, it started giving me several errors.
1.
# A fatal error has been detected by the Java Runtime Environment:
# SIGBUS (0x7) at pc=0x00007f542f9b3732, pid=34686, tid=139998362167040
# JRE version: Java(TM) SE Runtime Environment (7.0_97-b02) (build 1.7.0_97-b02)
# Java VM: Java HotSpot(TM) 64-Bit Server VM (24.95-b01 mixed mode linux-amd64 compressed oops)
# Problematic frame:
# C newEntry+0x62
# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again
2. It started throwing error in my second job that the file written and saved in the first job was not found and I could not see the file in the server either.
I have attached some screenshots of my jobs. I have a set of jobs running one after another appending sheets to the same workbook and this works great with .xls. Not sure why .xlsx would have such issues.
Appreciate your help a lot!
Thanks
Shuba
screenshots.zip.zip

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Thu, 11 Feb 2016 18:05:09 GMT

Whow ... this error should not happen. The problem here is not your job or my component, the problem here is an error in the JVM.
It is a error in the JVM using the zip library. I can tell you for sure these components works in a huge number of projects and it is not a problems caused by the component, instead I would say it happens because of the typical memory expensive kind of jobs working with excel files.
Can you reproduce this error?
I suggest install another java runtime with a different version. Do you use the Oracle JDK or OpenJDK?

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Thu, 11 Feb 2016 19:34:13 GMT

Thanks so much for the quick response again. They just upgraded the java version from jdk1.8.0_74 to jdk1.7.0_97. We use OpenJDK. But I am having this problem in both the versions in 2 different servers.
Thanks
Shuba

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Thu, 11 Feb 2016 19:36:00 GMT

Can you just try to install Oracle JDK? - simply unpack and set it as JAVA_HOME for your job.

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Thu, 11 Feb 2016 19:52:50 GMT

Sorry my bad, just figured we're using Oracle JDK not OpenJDK.

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Thu, 11 Feb 2016 20:33:53 GMT

Jan, Do you think that the second issue is also related to the Java settings?
2. I have sequence of jobs. The first job creates the workbook, create a sheet and then saves it. The second job should open the same workbook and append another sheet. But with .xlsx, I am getting the error that the file is not found.
Thanks
shuba

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Thu, 11 Feb 2016 22:09:15 GMT

File not found means file not found.... It is sometimes a timing problem but my API checks the existence of the file before it tries to open it. This helps to find problems much better. Do you use network attached storage? Sometimes NAS file systems have a synchronizing delay.

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Thu, 11 Feb 2016 23:47:22 GMT

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Tue, 16 Feb 2016 08:17:59 GMT

Hi Jan,
Any idea why I'm getting this error? Appreciate your help on this..
Thanks
Shuba

Re: [resolved] Writing to two sheets in the same excel file from one job

Anonymous — Tue, 16 Feb 2016 17:21:56 GMT

Without knowing your job design, I have no idea. Could you please post a screenshot of your job design?

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Wed, 17 Feb 2016 00:11:40 GMT

screenshot… .zip
Please see my screenshots in the attached zip file.
Thanks!

Re: [resolved] Writing to two sheets in the same excel file from one job

sjaganmo — Wed, 17 Feb 2016 00:13:02 GMT

Screesnshots attached.