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,

Change EXL2K to XLSX and let us know the results.

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).

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

Mayhewjm

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

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,

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...

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.