Database to excel 200 000 records, timing out

Post any question you may have in regards to GoAnywhere MFT and let our talented support staff and other users assist you.
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