topic Re: Out of Memory Error exporting to Excel - 8GB RAM in Talend Studio

Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 09:33:15 GMT

Hi,
We are experiencing problems to get to export a table from SQL Server to Excel 2007. Our table has about 40k rows and 8 columns and when we try to export to excel, always crash with error "Out of memory Error ".
Our machine has 8GB RAM; if we do it through SSIS (Sql Server Integration Services) the process run successfully in a few seconds, why we can't get it with Talend?
We're using the following config for Run/Debug (modified on Preferences > Talend > Run/Debug):
-Xms1024M
-Xmx6144M
Any idea?
Thanks

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 11:07:46 GMT

check the schema of tmssqlinput and fFileinputexcel...try to mention size for string columns..
also try to export say only 10 rows initially and see whether that works..and then keep on increasing row size..see memory consumption while 10 rows job is running...
are you doing any transformation in the job..what all components you have in job.

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 11:50:26 GMT

Hi Garpit, thanks by your reply.
About the schema, the longest column is a String (100).
If i export less rows i can do it correctly.
Our job copies an excel template to other place, run a SQL procedure to generate the source table, and then, get the data rows and export to excel, without any transformation (i attach a photo)
Any idea?
Thanks

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 12:37:02 GMT

The problem is, you accumulate all data into the memory with complex structure.
You could try the user components tFileExcelWorkbookOpen (has a streaming option to limit the memory usage) + tFileExcelSheetOutput and tFileExcelWorkbookSave
With the streaming option allows you to write nearly unlimited (only limited by Excel it self) number of large rows.
The trade off of this mode is, you can only access the last 100 rows after writing them within the Talend job but if you want only writing and do not read out in the same job, this will work.

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 12:48:24 GMT

The problem is, you accumulate all data into the memory with complex structure.
You could try the user components tFileExcelWorkbookOpen (has a streaming option to limit the memory usage) + tFileExcelSheetOutput and tFileExcelWorkbookSave
With the streaming option allows you to write nearly unlimited (only limited by Excel it self) number of large rows.
The trade off of this mode is, you can only access the last 100 rows after writing them within the Talend job but if you want only writing and do not read out in the same job, this will work.

Thanks by your answer.
Could you say me where can i find that componentes? are they custom components?
Thanks!

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 31 Jan 2014 21:15:36 GMT

Yes these components are published in Talend Exchange: http://www.talendforge.org/exchange/index.php
Please search for tfileexcel and you will find the whole suite.
These components works a bit different as the build-in components.
At first you need tFileExcelWorkbookOpen to open a new or existing Excel file (capable to read a file an use it as template and write it with tFileExcelWorkbookSave under a new name).
In tFileExcelWorkbookOpen you can specify the memory saving mode but this mode works only for the newer xslx format, not for the old xls format.
The other components read/write into the workbook.

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Mon, 03 Feb 2014 15:22:31 GMT

I got it!! thanks you very much jlolling.
Cheers

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Wed, 12 Feb 2014 16:32:10 GMT

Hi jlolling
I've another question.
When i use those components running the job manually from Studio it works fine, however, when i try to run it through TALEND ADMINISTRATOR CONSOLE (I've scheduled the exection of the job) the custom excel's components don't run and the excel is not generated, why? do i need install custom components in any place more?
Thanks for your time
Cheers

EDIT: the job's execution traces only shows the beginning and end of the whole execution, and MSSQL components, but not the excel's traces.
It appears as these componentes not achieve to run:

TALEND STUDIO'S JOB TRACES

connecting to socket on port 4045
connected
2014-02-12 18:06:24|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||

2014-02-12 18:06:24|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|begin||
2014-02-12 18:06:25|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|end|success|1016

2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|47

2014-02-12 18:06:26|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|begin||
2014-02-12 18:06:29|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|end|success|3031
2014-02-12 18:06:29|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|begin||
2014-02-12 18:06:30|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|end|success|1468
2014-02-12 18:06:30|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|begin||
2014-02-12 18:06:38|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|end|success|8235
2014-02-12 18:06:39|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|begin||
2014-02-12 18:06:41|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|end|success|2453
2014-02-12 18:06:41|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|begin||
2014-02-12 18:06:43|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|end|success|1453

2014-02-12 18:06:52|jmrNVc|jmrNVc|jmrNVc|544|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|27828
disconnected
Job MyJob ended at 18:06 12/02/2014.

TALEND ADMINISTRATOR CENTER'S JOB TRACES

### Job STARTED at 2014/02/12 17:53:28 (jobId=20140212_174057_vYBXD, jobExecutionId=20140212175328_pVLLY) ###
2014-02-12 17:53:28|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||
2014-02-12 17:53:29|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-12 17:53:29|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|78
17:53:31|20140212175328_pVLLY|20140212175328_pVLLY|20140212175328_pVLLY|5676|MyProject|MyJobl|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|2640
### Job ENDED SUCCESSFULLY at 2014/02/12 17:53:31 (jobId=20140212_174057_vYBXD, jobExecutionId=20140212175328_pVLLY) ###

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Wed, 12 Feb 2014 20:49:47 GMT

Yes, you have to setup a user component path for the commandline in the TAC.
If you have configured it you will see the process of building a job as one step more "setUserComponentPath".
Which release do you run? Actually the commandline should complain about the missing components.
Best regards.
Jan Lolling

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Thu, 13 Feb 2014 09:11:40 GMT

Thanks jlolling by your suggestion,
I've just configured my User components' path in Talend Administrator > Configuration > Commandline ( Talend Help!)
And yes, now I can see the traces of these components' execution. However, they are not running ok because they don't insert any row into the excel files. Any idea of this behaviour??
The job's traces from Talend Administrator Center:

### Job STARTED at 2014/02/13 10:06:14 (jobId=20140213_095819_kzpvS, jobExecutionId=20140213100614_ghgca) ###
2014-02-13 10:06:14|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookOpen_1|end|success|0
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|begin||
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tMSSqlRow_1|end|success|78
2014-02-13 10:06:15|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_1|end|success|281
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_2|end|success|250
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|begin||
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelWorkbookSave_1|end|success|0
2014-02-13 10:06:16|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|begin||
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_3|end|success|203
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|begin||
2014-02-13 10:06:17|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default|tFileExcelSheetOutput_4|end|success|156
2014-02-13 10:06:21|20140213100614_ghgca|20140213100614_ghgca|20140213100614_ghgca|9468|MyProject|MyJob|_owoZQImUEeO5AbT5-_XKlg|0.6|Default||end|success|6719
### Job ENDED SUCCESSFULLY at 2014/02/13 10:06:21 (jobId=20140213_095819_kzpvS, jobExecutionId=20140213100614_ghgca) ###

Cheers

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Thu, 13 Feb 2014 21:11:24 GMT

Which Release do you use?
The commandline is an "ongoing" problem child.
Please ensure the path you have given to the commandline contains the directories of the components.
Here the way to reset the commandline and force it to run correctly.
Please stop your commandline (and take care the commandline is REALLY stopped by checking the process list of your OS.
Now delete EVERYTHING in the commandline-workspace.
Start the commandline again.
The next generation run checks out the complete repository and recreates important - and often corrupted - directories (.JETEmitters and .Java).
I would love it to have a Java standard build system like Maven or ANT ! The crap commandline is always a reason for trouble and works never reliable.

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Fri, 14 Feb 2014 13:23:14 GMT

Hi jlolling, we're using 5.2.1
In our commandline's workspace only appears a folder named ".JETEmitters"; should we delete it to check your suggestion? (i think that its a system folder of talend)
Cheers

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Sat, 15 Feb 2014 15:39:59 GMT

Yes, with everything, I mean also this folder!

Re: Out of Memory Error exporting to Excel - 8GB RAM

Anonymous — Wed, 19 Feb 2014 11:04:15 GMT

HI jlolling, i got it!!
Thanks you very much. The solution was delete all content of the commandline's workspace.
Thanks again
Cheers