Database to excel 200 000 records, timing out

[anutta78]

Hello,

I'm trying to create excel from database output.
Rowset has more than 200K rows, project is runing and at some point fails. Here is log:

3/27/17 9:18:26 PM INFO Executing project 'Oracle report for client'
3/27/17 9:18:26 PM INFO Project location: \test\Oracle report for client.xml
3/27/17 9:18:26 PM INFO Executing module 'Main'
3/27/17 9:18:29 PM INFO Executing task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:31 PM INFO Executing sub-task 'query'
3/27/17 9:18:31 PM INFO Executing statement - SELECT * FROM PWR_UNBLIND_DU_LIST_TABLE_VIEW
3/27/17 9:18:31 PM INFO Query completed successfully and the rowset 'data' was created
3/27/17 9:18:31 PM INFO Finished sub-task 'query'
3/27/17 9:18:31 PM INFO Finished task 'sql 1.0 (Connect to DB)'
3/27/17 9:18:35 PM INFO Executing task 'writeExcel 2.0 (Convert to Excel)'
3/27/17 9:18:35 PM WARN Overwriting the output file 'Global Production Processes\client\disp.xls'
3/27/17 9:20:30 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 2 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.
3/27/17 9:34:01 PM INFO Number of rows in the sheet exceeded the maximum limit, Sheet 3 rows. Creating a new sheet '65,536' because 'whenSheetFull' was set to 'rename sheet'.


Hangs ... and then fails with error:

Project 'Oracle report for client' is defined as not thread safe and an instance with job number '1000000623844' is already running.
Please wait for this job to finish and then try again.

Then I have to reboot a server. because GoAnywhere services hangs and not responding.

<writeExcel label="Convert to Excel" inputRowSetVariable="${data}" outputFile="\Global Production Processes\client\disp.xls"
whenFileExists="overwrite"
excelFormat="excel2007"
whenSheetFull="rename sheet" includeHeadings="true" outputFileVariable="excelFile" version="2.0" logLevel="debug">
<data trim="right" dateFormat="MM/dd/yyyy" timestampFormat="MM/dd/yyyy" />
<headings backgroundColor="Gold" />
</writeExcel>

[Support_Rick]

The biggest issue with creating an EXCEL file is that it is very memory intensive. If you only have 1GB or 2GB of JVM, you'll probably need to bump that up to around 6GB or 8GB to handle the memory needed to write 200k records to an EXCEL file.

Keep in mind, a CSV does the same thing... opens in EXCEL, but not as memory intensive. It's an option if you have something like that size.