Database to excel 200 000 records, timing out

If you have a new question you’d like our support staff to post a response to, please visit our customer community, GoAnywhere Insiders, to pose the question in our Discussion Boards. We have a thread “Ask Our Tech Experts” that our support team monitors on a regular basis, or you can start a new discussion where other GoAnywhere users and support staff can weigh in. Log in or create your new account at https://insiders.goanywhere.com.

If you need an immediate response, please create a support ticket or contact our support team by email at [email protected].
2 posts Page 1 of 1

anutta78

Posts: 5
Joined: Fri Sep 09, 2016 11:59 am

Post by anutta78 » Mon Mar 27, 2017 9:59 pm
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

Support Specialist
Posts: 592
Joined: Tue Jul 17, 2012 2:12 pm
Location: Phoenix, AZ

Post by Support_Rick » Fri Mar 31, 2017 8:29 pm
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.
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
2 posts Page 1 of 1