Export the Locals Window data to Excel?

[dantheinfoman]

Hi All,

I'm trying to figure out what's crashing FoxyPreviewer's "Save as XLS" feature. Whenever a report is large (300+ pages) and 2 reports are saved as XLS consecutively, my program crashes. I've tested this for hours, it's after 2 consecutive "Save as XLS"s. The first one doesn't error out.

Anyway, I think maybe a variable or something may retain a value or something after the first Save as XLS, so I wanted to compare the Locals window from the first time with the second consecutive save as XLS to see if anything in the Locals window is different the second time around.

I'd like to export Locals Window data into Excel so I can compare the before and after Locals Window variables to find the issue.

Thanks for any comments or advice!!!

Dan

 

[Mike Lewis]

Rather than export the locals to XLS, you can save them within VFP. A couple of easy options come to mind.

First is the DISPLAY MEMORY TO FILE command. This will write the variables to a text file, which you can them append into a DBF (using APPEND FROM .. TYPE SDF). This will give you the variable name, its scope, its data type, its width, and most importantly, its value.

The second option is to use SAVE TO. This saves the information to a MEM file. This is a binary format, but it is possible - with a bit of effort - to extract the same information as above.

In both cases, you are not just saving local variables. Publics and privates are also included - just like in the Locals window of the debugger.

Edit: Actually, LIST MEMORY TO FILE is better than DISPLAY .... because the latter pauses after each chunk of text, and demands that you press a key to continue. The LIST version does the whole thing without pausing.
Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

I'm not 100% sure, but I believe FoxyPreviewer's "Save as XLS" first create an Excel 2003 XML file, and this XML file is converted into XLS via MsOffice or OpenOffice, in this order. If none of these two is installed, then remains as XML.

Maybe this helps you to find the cause.

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[dantheinfoman]

This is really cool, thanks guys!! I compared all variables in the working and non-working scenario, and there are only a few things that are different the second time around. I will have to play with it to understand what these are for , but it's still crashing the second time I save as XLS.

Maybe I should try to 'RELEASE' any changed variables at the end of the "Save to XLS" and see if that makes it behave?

Any one else having difficulties with Foxypreviewer using "Save as XLS" twice consecutively, particularly with big reports (300+ pages)??

Thanks!

Dan

 

[newtofoxpro]

I tried big reports (300+Pages) I did not find problem. Pls try

REPORT FORM (myFRX) NODIALOG NOCONSOLE OBJECT TYPE 13 TO FILE (myXLS)
oExcel = CreateObject("Excel.Application")
oExcel.DisplayAlerts = .F.
oWorkbook = oExcel.Application.Workbooks.Open(myXLS)
oWorkbook.saveas(myXLS,56)
oExcel.quit()
oExcel = .Null.

 

[dantheinfoman]

Hi Newtofoxpro,

The Foxypreviewer tool allows everything from an FRX to be converted exactly the same into excel, rather than all fields being on column A in one string. That's why this is preferable.

Thanks!

Dan

 

[Mike Lewis]

I have to say that I think it unlikely that comparing the values of local variables will help you solve this problem. I'm not an expert on FoxyPreviewer, but I feel sure it is well-behaved enough not to be sensitive to the values of variables outside its scope, other than those explicitly passes as parameters or stored in its own properties. But I might be wrong.

Have you tried waiting a few seconds (or minutes?) between the two reports? I've no reason to think that that will be the solution, but it is always worth trying.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[newtofoxpro]

Already posted by vgulielmus that Foxypreviewer job is frx2xml. And my code turn xml to xls. If you have not solved this please try on another pc having m$ office.

 

[Olaf Doschke]

I once digged a bit into Foxypreviewers Excel file creation and it indeed puzzles together Office XML without the Ms Office Suite or MS Excel needed. The resulting XML file is zipped (like an xlsx is) and given the xlsx extension. Large report create large xml files and that might break some limitation of string handling at some point. For example there is an about 16MB limitation for string variables. The largest SPACE i can execute is Space(16777184), though 16MB are a few bytes more. The help talks about exactly that figure: 16,777,184. I looked it up, it's a bit below 16*1024*104.

Not all functions work correct on long strings. If the XML generated is too long you might end up instable. 300 page reports are really a problem in length of the XML to put together and process, so I'd say you simply have a size problem with such reports. The resulting xlsx may be far smaller, as xlsx is a compressed format.

Bye, Olaf.

 

[dantheinfoman]

Hi Mike,

I tried waiting 5 minutes and it still crashed. It always crashes at the same line (if I hit the cancel button really fast it reveals this error

Here's the line that FoxyPreviewer breaks on the second time around:

It seems the second time through, this line of code has an error for some reason, even though the report is unchanged. That's why I thought maybe something needs to be released or refreshed.

I would love to find a way to capture the erroneous variables Contents, UNCONTENTS and lnStyleNumber right when it crashes, but I can't seem to catch it. When I tried ON ERROR DO danError, and had a custom method in the excellistener, it still just crashed and didn't tell me anything.

 

[Olaf Doschke]

C5 errors are not catched by ON ERROR, that's simply because they are fatal errors.
But if you know the error is on that line exact, what hinders you to set a breakpoint or SET STEP ON the line before (lnStylenumber = ...) and then inspect variables BEFORE the error occurs.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

I don't know which iteration in the SCAN FOR NOT DELETED that the error occurs on and it loops 263 thousand times. It seems to be erratic, crashing at 62% sometimes and sometimes later on. I tried a

wait window myCounter nowait
myCounter = myCounter +1

but it was inconsistent, crashing at different line numbers.
This is for the same report, same dates, same data. Everything's the same, except the first time I run it (535 pages), it works perfectly, the second time it crashes. Something is different the second time. . .

Thanks!

Dan

 

[Olaf Doschke]

As the offending line does only add to a string variable, it could be a memory issue.
Try to monitor LEN(lcXMLTable) and Sys(1016). Instead of wait window, why not write into a log file, simply do STRTOFILE().

Bye, Olaf.

 

[Olaf Doschke]

Initially do STRTOFILE('',"some.log",0)
Then within the loop STRTOFILE(TRANSFORM(LEN(lcXMLTable))+":"+SYS(1016)+CHR(13)+CHR(10),"some.log",1)

Bye, Olaf.

 

[Olaf Doschke]

Would you share the results? I suspect the length of this string variable lcXMLTable will grow very large. If you do largevar=largevar+somestring what is done in memory is allocating enough memory for the result string and copying over, VFP does NOT expand the already allocated memory for largevar and just appends the new part at the end.

It can't.

Because right after the already allocated memory other variables, other code or even a different process might have that memory allocated.

It's a bad idea to put together the officeXML for such large reports in a string and only write it out, when the full document is in the variable. VFP garbage collection can't free memory of the old lcXMLTable memory address as long as you are not finishing with the loop, because the runtime only does garbage collection in its idle loop (when at READ EVENTS).

You may try doing SYS(1104) right before and after that line. The better idea would be modifying foxypreviewer to flush out partially created xml to the resulting file, if it goes beyond a threshold of eg 1MB size and then restart with an empty string, that would lower memory usage and contention of copying string parts forth and back. I don't think foxypreviewer was tested against 300 page long reports.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

It ran the first time but took a long time,

the first line of the LOG file is
159:17133296

the last line was
16658690:50455836

I will have to try this with the second time around after a scheduled meeting.

Olaf, I just read about SYS(1104), should I put this before and after the procedure that this offending line of code is being called from ( excellistener.AfterReport ) or immediately before and after this offending line of code? If I do the latter, won't is just clear the variables and/or slow FoxPro way way down?

I will test all these very soon after my meeting.

Thanks!!!

Dan

 

[Olaf Doschke]

16658690 is just about 100KB from the maximum allowed string length.

As the C5 happens somewhere within the loop doing the garbage collection just once before and after the report is not helping.
You might do it each 10th or 100th iteration. But it will not throw away currently allocated memory, only free memory will be defragmented. It won't run long especially when called often.

If your problem is the string length limit, then memory garbage collection won't help, though. In short your report is too long. It doesn't explain why it sometimes works, but if you do several experiments and your string legth always goes up near the 16MB limit you know it's that limit biting you.

What could help is something along the lines of defining LOCAL lcXMLTable2
And in the loop put in something like

IF LEN(lcXMLTable)>16000000
   lcXMLTable2 = lcXMLTable
   lcXMLTable = ""
ENDIF

But somewhere at the end lcXmlTable has to be appended to lcXMLTable2, ie lcXMLTable = lcXMLTable2 + lcXMLTable (as lcXMLTable2 is the first ~16MB of the whole string), and that again would break the 16MB limit. So all in all that might still not help.

The simplest thing helping would be splitting the report into smaller parts, then later maybe appending the Excel files to each other.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

You're going to love this one. So, I tried it a couple ways:

*ds test per Olaf
SYS(1104)
This.AfterReport()  && this is the procedure that contains the offending line of code
SYS(1104)

AND it worked 2 times in a row!!! But on the 3rd time, now it crashes on the same line.

So then I tried doing it every 10 iterations within the loop:

*ds 8/15/2015 the next line has been giving errors, investigating
		dancounter=dancounter+1
		IF dancounter % 10 = 0 then
			SYS(1104)
		ENDIF
		
		lcXmlTable	  = lcXmlTable + This.Xml_Cell(Contents, UNCONTENTS, lnStyleNumber) && 2011-12-29 Passing the Unicode as well
		
		IF dancounter % 10 = 0 then
			SYS(1104)
		endif

Any idea why it's clearing system memory the first time, but then when I want to run the report a third time, it explodes?

Thanks! This is definitely progress!!

Dan

 

[Olaf Doschke]

It can't prevent the string to get too long with garbage collection, the string remains as long as it is.
Modify the offending line into two lines:

[pre]lcNewCell = This.Xml_Cell(Contents, UNCONTENTS, lnStyleNumber)
* Log LEN(lcXMLTable)+LEN(lcNewCell) here
lcXMLTable = lcXMLTable + lcNewCell[/pre]

See whether that next total length goes beyond the string length limit. You can't go beyond that.
Files you write can get longer than 16 MB without problem, though, so the idea is to write out lcXMLTable were it's written to later anyway. Do that right away within the loop and reset lcXMLTable before it gets long.

Bye, Olaf.

 

[vgulielmus]

The 16M string limit can be broken in two ways, from what I know.

1) The assignment command where x is on the both sides of "=", x is the leftmost term of the right side and the rest of the string expression is within the 16,777,184 limit. I mean :
x = x + expression && where len(expression) <= 16777184 (if x already exceeded 16M)

x = REPLICATE("x", 16777184)
x = REPLICATE("x", 16777184) +  "x" && Error
? REPLICATE("x", 16777184) +  "x" && Error
y = x + 'x' && Error
? x + 'x' && Error

x = REPLICATE("x", 16777184)
x = x + x && ok
x = x + x && Error because the second term (the last x) has more than 16,777,184 characters
x = x + 'x' && ok
x = 'x' + x && Error because the second term (the last x) has more than 16,777,184 characters
x = x + REPLICATE("x", 16777184) && ok
x = x + REPLICATE("x", 16777184) + 'x' && Error because the the rest of the string expression (REPLICATE("x", 16777184) + 'x') has more than 16,777,184 characters
x = x + REPLICATE("x", 6777184) + REPLICATE("x", 10000000) && ok
x = x + REPLICATE("x", 6777184) + REPLICATE("x", 10000000) + 'x' && Error because the the rest of the string expression (REPLICATE("x", 6777184) + REPLICATE("x", 10000000) + 'x') has more than 16,777,184 characters

2) FileToStr() also can produce memory variables longer than 16M



Respectfully,
Vilhelm-Ion Praisach
Resita, Romania