Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Output in EXL2K Ends Early in Large Report
Go
New
Search
Notify
Tools
Reply
  
[CLOSED] Output in EXL2K Ends Early in Large Report
 Login/Join
 
Member
posted
Greetings! As part of a contract with my department, we send a list of records (100K+ individuals) as an excel file to a third part company with basic information every quarter in order for them to send an email. This involves running a report that we've used for a long time wherein the output of it has only grown.

Lately however, each time I run this particular report (unless recordlimited) my output is significantly reduced from what it should be. This is verified in the console output. In addition, it appears as though the output quits midway through the columns at a record and no more rows are displayed past that. It looks roughly like this below, but about 200-ish records in.

ID_NUM FIRST_NAME LAST_NAME EMAIL
80000033 John Smith johnsmith@gmail.com
80000044 John Doe johndoe@gmail.com
80000055 John


I'm completely at a loss. I've tried a bit of searching here but I'm seeing others say 65,000 is a 'limit' for output in EXL2K, which this report is longer than that - but I need to have some sort of reliable way to handle these reports moving forward. I have seen this occur with a few other length reports, and frankly it seems to just happen lately for those long directory reports that I run occasionally.

Can anyone advise how I can work around this? This report has been and will continue to be necessary for us for the foreseeable future. Are there alternatives to EXL2K that I should use?

This message has been edited. Last edited by: FP Mod Chuck,


WebFOCUS 8104, Windows 10, Excel
 
Posts: 2 | Location: App State University | Registered: April 06, 2018Reply With QuoteReport This Post
Expert
posted Hide Post
Change EXL2K to XLSX and let us know the results.


   In FOCUS Since 1983 ~ from FOCUS to WebFOCUS.
   Most recent: 8204 Gen 48 in Test and Production.
 
Posts: 3045 | Location: Middle Tennessee [8204M Gen48 in Test&Prod] | Registered: February 23, 2005Reply With QuoteReport This Post
Master
posted Hide Post
65K rows per sheet has always been the limit for EXL2K. If you still need in EXL2K and have more the 65K rows, you have to use a technique that compute a SHEETNUMBER based on row count, your first BY phrase as to be BY SHEETNUMBER, then use BYTOC and generate multiple sheets. Much easier if you can convert to XLSX (1 million rows per sheet).


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 942 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Member
posted Hide Post
Thanks for the insight everyone. I'm still having some strange problems when outputting to XLSX instead of EXL2K however.

I keep getting the error 'Excel cannot open the file 'filename.xlsx' because the file format or file extension is not valid. Verify that the file has not been corrupted and that the file extension matches the format of the file.'

This seems to indicate to me that WebFocus is corrupting my file upon output. Oddly enough, this doesn't happen if I record limit the report, or use a smaller initial input. I sincerely have no idea what could be the issue, as there doesn't appear to be any issues with my code. This report runs just fine unless it's over 20k-ish records in the initial sample.

Very frustrating Confused


WebFOCUS 8104, Windows 10, Excel
 
Posts: 2 | Location: App State University | Registered: April 06, 2018Reply With QuoteReport This Post
Virtuoso
posted Hide Post
Mayhewjm

Make sure your Excel Server URL IBIF_excelservurl is blank in the client administration console under the Client Settings / General option


Thank you for using Focal Point!

Chuck Wolff - Focal Point Moderator
WebFOCUS 7x and 8x, Windows, Linux All output Formats
 
Posts: 1694 | Location: Customer Support | Registered: April 12, 2005Reply With QuoteReport This Post
Master
posted Hide Post
If you are using Report Caster, make sure you update the output format on the task.

This message has been edited. Last edited by: jgelona,


In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.
 
Posts: 942 | Location: Oklahoma City | Registered: October 27, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
For XLSX, encode HTML entities. It doesn't do that (necessary step) automatically, at least, not on our 8.1.03.

If you generate multiple Excel sheets with BYTOC, make sure you replace '&' characters (for example, with '&|amp;') in the field used for BYTOC. The same goes for '<' and '>', BTW.

If IBI ever decides to fix their escaping issues, that needs to be undone, or the escaping occurs twice...


WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010
: Member of User Group Benelux :
 
Posts: 1652 | Location: Enschede, Netherlands | Registered: August 12, 2010Reply With QuoteReport This Post
Member
posted Hide Post
There has been over the years multi reports of similar problems (which are not always easy to pinpoint or resolve)

Most Excel download configurations uses redirect with WebFOCUS : meaning the Excel file is created then is stored into WebFOCUS81/temp and a page is downloaded/executed in the browser to go back and call a Servlet that download the file in binary. In most cases the file stored into the temp directory is complete and the issue occurs on the download steps that returns and incomplete Excel file (eventually truncated in the middle of a record)

This might be linked to some Apache (or some other network component) timeout setting : https://techsupport.informatio...om/sps/51412550.html

You may want to open a case in InfoResponse Online, or look at what has change in your configuration.
 
Posts: 11 | Location: Information Builders France | Registered: May 22, 2003Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [CLOSED] Output in EXL2K Ends Early in Large Report

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.