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

 

[Olaf Doschke]

Yes, I think I posted about 2) already here at tek-tips. Anyway it's the observation of the log, that the variable reaching that limit causes the C5. The C5 is NOT happening within the Xml_Cell() method, then that would go into the vfp9err.log, but it happens in this line. What else is happening in this line but memory allocation for the result string?

Even if xml=xml+cell would not error at all, copying forth and back 16MB hundredthousands of times (assume 1000 cells per page = 300000 cells total) also takes time, even within RAM. If you want to write that XML out to the xlsx file later anyway, you can do so in 8KB or somewhat larger chunks, but you don't prepare the full 16MB and more in memory.

.NET has the StringBuilder class, which can allocate a longer memory block and adding to that string really appends to once allocated memory. See

http://www.dotnetperls.com/stringbuilder

VFP has no such string variable type.

And despite what behaviour you prove you see in single lines of code, Dan's report results are proof enough for me you better not play around with going beyond the 16MB limit, when you don't want to risc c5 fatal errors.

Bye, Olaf.

 

[dantheinfoman]

Hmmm. So I did a log file and TestA.log was identical to TestB.log according to winmerge. TestC.log didn't get very far (the 3rd time running the 'save as XLS') because it crashed as usual.

I think what you're saying is that I need to either:

1. Write this info to a text file or log (which takes forever) and then use FileToStr() to take that long string and create the excel stuff from there.
2. Use multiple variables to handle the excess once the len(lcXMLTable) > 16,777,184 and at that point to use a secondary variable, such as lcXMLTable2 from then on. Then later to combine them (probably by putting them in a txt or log file as well) into one variable somehow and then creating xls from there.
OR
3. I believe Olaf hinted at a way to incrementally build the excel file instead of in one swift move.

I suppose whichever one works and takes the least amount of time to perform would be ideal.

Thoughts? Please let me know if I misunderstood in the above statements.

Thanks!!

Dan

 

[Olaf Doschke]

1. No, you write the XLS file, that you write anyway, just not in one go but in chunks of 8kb or 32kb, it won't take longer.
2. Yes, that would be a solution not interfereing with current other code, but using one more variable would only solve up to 32MB XML. And you'd need to find the place the one XML string was written to a file and change that to two writes.

In both cases you need to know where and how the XML is written were in the end. But it's pretty clear this is written to a file which finally is compressed into the xlsx.

To see what I am talking about take it from the other way around: In Excel create a sheet with some cells filled (one page is enough), then save that as xlsx. Now change the file extension to .xslx.zip and unzip that file. You'll find some xml file containing your data. It's that XML, which Foxypreviewer generates and writes out to a file to later become the xlsx.

The zip root has folders _rels, docProps and xl, the xl folder contains a workbook.xml and a folder worksheets, where you find sheet1.xml

Bye, Olaf.

 

[vgulielmus]

I agree that very long strings make Vfp slower or even to crash.

Downloaded Foxypreviewer and made two small tests.
The first one generates an XLS file (Microsoft Excel 97-2003 worksheet, the first bytes are D0 CF 11 E0 A1 B1 1A E1)

DO "D:\Kit\FoxyPreviewer v299z30\FoxyPreviewer.app"
CREATE CURSOR cc (ii I AUTOINC,cc C(10) DEFAULT SYS(2015), dd D DEFAULT DATE())
FOR lni = 1 TO 100
	APPEND BLANK
NEXT
CREATE REPORT cc FROM cc
REPORT FORM cc PREVIEW

The second one gives two errors and finally said that are two many rows to be converted into XLS (100000 > 65536, maximum for Excel 2003).
File extension is XLS, but the content is Excel 2003 XML (ASCII)

DO "D:\Kit\FoxyPreviewer v299z30\FoxyPreviewer.app"
CREATE CURSOR cc (ii I AUTOINC,cc C(10) DEFAULT SYS(2015), dd D DEFAULT DATE())
FOR lni = 1 TO 100000
	APPEND BLANK
NEXT
CREATE REPORT cc FROM cc
REPORT FORM cc PREVIEW

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

The exellistener.xml_cell() method does create XML <cell> tags.

In the exellistener.AfterReport() event with the line =FPUTS(lnHandle, lcXMLTable), this is simply writing the XML, if you choose to split the lcXMLTable you can make this two FPUTS and would be safer up to 32MB XML table data.

You can't write this first, though, as the first thing written via FPUTS is another lcXML variable, which is generated after the loop. It's having some header and style info. If this part could be done before the SCAN..ENDSCAN part that lcXML part of the file could already be written and the FPUTS of lcXMLTable could be done in chunks within the SCAN..ENDSCAN instead of afterwards, as said. That would prevent this variable getting too big and in turn would most probably also solve any other memore pressure the loop geenrates.

I'm unsure, if you can simply change the order of the code, there might be something going into the lcXML header, which comes out of the loop, so the easier thing to do is split up lcXMLTable and later have several FPUTS for all the partial lcXMLTable strings.

As said already, the foxypreviewer developers surely had no such large reports in mind when writing code not creating the xml file in smaller portions.

Bye, Olaf.

 

[Olaf Doschke]

>File extension is XLS, but the content is Excel 2003 XML (ASCII)
The AfterReport event converts the XML file it writes out to XLS via the ToPureXLSUsingExcel method it has. This uses Excel. If Excel isn't available it tries via the ToPureXLSUsingOO method, which uses OpenOffice.
If non is existing the file is kept as XML with XLS extension via RENAME (lcTempFile) TO (This.cWorkbookFile). That's why you see what you see.

So it may not be the OfficeXML xml but Excel2003 XML, anywayy it's XML, not binary files.
Anway that conversion step also takes away RAM space and so this only goes smooth enough for one or two such large reports. There is no way to enforce a full blown memory sweep and garbage collection so the problem boild down to too large reports. Insisting it should work for as many and as long reports as you like, just because it goes with the first run is insisting on memory management putting back the runtime into the perfect state it was before creating the first XLS. There also is Excel.Application involved and the memory consumption of that process is beyond your control. In the end there is no full solution to that, as there are some imperfections in memory management.

The last resort is forcing flush of memory settings:

lcMemory = Sys(3050,1)
Sys(3050,1,1)
Sys(3050,1,Val(lcMemory))

That should not be done each iteration, just after the report ran, but it still would only suggest and recommend the vfp runtime to clean up memory usage. And it won't effect other processes. You simply have a report size problem.

Bye, Olaf.

 

[Olaf Doschke]

Why are you actually creating 300 page reports via FRX and foxypreviewer, to create XLS files?
The direct way to export data to cs or xls would be more to the point with such large data amounts.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

So does the first line

lcMemory = Sys(3050,1)

go at the beginning of the code and the other 2 lines

Sys(3050,1,1)
Sys(3050,1,Val(lcMemory))

go at the end, or does it all go together at the end?

I will try both ways. .

To answer your question, some reports even for a biweekly payroll report could be large if you have a gazillion employees, I'm trying to make sure it works in harsh conditions.

Thanks!

Dan

 

[Olaf Doschke]

These lines are all going after the report ran.

Bye, Olaf.

 

[Olaf Doschke]

>some reports even for a biweekly payroll report could be large if you have a gazillion employees
That's not answering my question. Do you really need a report to generate the output?
You can more easiy export data into xls or csv, because it seems to me the real need is not the report itself, but the data in it as input data for other systems.
I do weekly exports to SAP for payments and this simply goes through an SDF format I write out via STRTOFILE, could aso be done via COPY TO some.txt TYPE SDF, but that was halfways inherited.
It's not hard to put together a record with PADL() and PADR() and TEXTMERGE also helps and then output line by line or a whole cursor, which also gives you full control, if you prepare all data in char fields only. This only has the limit of no field having a width beyond 254 chars.

If the report is about data export you don't have a report problem, but a data export/import problem, and you don't treat that via a report, even if you finally need the excel file you can produce that easier without limitations of VFP by writing a csv file, the csv file extension also is read in by excel and you can then SaveAs whatever you need. You may extract the part of the afterreport event putting together the XML and do it yourself, but csv is the much shorter format, produced faster.

And you can't convince me, that a pretty formatted report with eg a payslip per employee printed out in a report, later scanned in for the payment system or even going into mail is what is better than transferring data in a pure format for data exchange.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

Ultimately, I'm just trying to keep users happy, I suppose. I got it working with the sys(3050) tool you showed me. Sorry I haven't had any time yet to report the exciting news. I will do more testing with this tomorrow to make sure nothing else is adversely affected by using sys(3050) since you did say it was a 'last resort' (gulp). But it looks as if in Task Manager, the EXE goes from formerly being about 250 - 300MB+ to about 15 - 25MB. Wow!

If that had not worked, I would have added an additional Export to XLS (Basic) option that does just that, without the fancy cell data.

I can't thank you enough. Is there a way to buy you a beer or soda or something?! If not I will have to keep learning and pay it forward. But, really, I'd love to buy you a drink good sir!

Thanks

Dan

 

[Olaf Doschke]

Thanks, Dan.

Well, the disadvantage of that "last resort" is it flushes out all the cashed data accellerating any query etc. so the next few queries that cache will be built up again. You can't selectively just purge the memory used up by all the lcXMLTable string operations. But this memory allocation reset will never release any current variables or cursors or buffers, so it has no direct negative effect, just indirectly influencing optimzation by flushing out all the cache.

Keep an eye on my posts. I'm taking the route from employee to freelancer and in that you could really help with a recommendation or testimonial on my site I currently prepare, if not even with collaboration. I'll change my signature as far and fair as it's usual to have a reference to my business.

If your way ever leads you to Germany, Hamburg - the nicest city in the world - feel free to contact me. Even if you're surely more fond of your home town, the german beer has its own reputation. If you're more into wheat beer, that's southern germany bavaria. Northern beer rather is pilsner, but you also get Oktoberfest beer on tap here and bottled beer of any type.

Bye, Olaf.

 

[Olaf Doschke]

>Ultimately, I'm just trying to keep users happy, I suppose.

On that aspect I could also tell many stories. In the end it's their choice, but sometimes their wishes are - well - let's call it conservative. Especially if you later find out there would be a much easier interface, ie to get data transferred.

Bye, Olaf.

 

[dantheinfoman]

I wish you very well on this endeavor. I have no doubt you'll be amazingly successful, Olaf. You've helped so many people and I know we'd all recommend you any day. My one regret is that I didn't see your name as one of the speakers for this year's Southwest Fox in October at the annual FoxPro conference in Arizona. I wonder, are you attending? If so, I'll definitely buy you a few rounds! It may not be Hamburg quality, but. . .

Also, just reminiscing from my childhood random factoids, isn't Hamburg where the 'Hamburger' came from?

Have a great morning or afternoon or whatever it is there! I'm going to bed soon (its 2am).

Yours,

Dan

 

[dantheinfoman]

Ha ha! Conservative. I like it.

Yes, have to keep the users happy even if they don't know what they want nor why.

Yours,

Dan

 

[Olaf Doschke]

In regard of the Hamburger: Yes, that dish originally came from here, but was rather something else than what America made it. So the best burgers in town here rather are in American Diner type restaurants, not any original Hamburgean Hamburger.

I know where you can get a "Rundstück warm", which rather is a classical meal made from leftovers of roast meat with sauce in a bun. So it was something you got at bakeries and butchers at monday as leftover of sunday meals and that tradition dropped even before my birth in the first half of the last century. That's at least one story about the Hamburger, there are several. The Rundstück doesn't have the appeal of an american burger, though. Served as a dish you get it with so much sauce it drains the bun, no food to go.

That story is kind of the the same for the pizza, if you think about it. Also gives "Junk Food" another meaning.

There are other meals Hamburg more proudly claims, some of which are in dispute with other cities, like the Currywurst (roasted sausage with curry sauce).

Something more typical is not for everyone just judging from the looks - Labskaus - but indeed it's just mashed potatoes mixed with corned beef, then add red beets and you get the idea how it looks. To complete that dish you add a sunny side up egg plus one or two matie and a few pickled cucumbers. Since most ingredients are canned/pickled or can be stored long (potatoes), that also was seaman food.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf,

Any thoughts on this: When I debug, it errors out on this line

And while stepping through it seems fine. The variable lcMemory = "262144" and i would have thought that using the VAL function like you did would make this work.

An thoughts?

Thanks

Dan

 

[dantheinfoman]

Actually, it only errors out on the first go-around, when the memory is high, like:

Subsequent times have no error as shown in the last post.

Hmmm. . .

Thanks

Dan

 

[Olaf Doschke]

Well,

Sys(3050,1,Val(lcMemory)) with lcMemory being "262144" means SYS(3050,1,262144).
That runs without problem for me on it's own.

It indicates your memory is rather low or your initial setting for this.
I'd always initialize this to some higher value ranging in several MB.

So in main.prg do something along the lines of SYS(3050,1,0x2000000)
0x2000000 is 32MB. Don't fear VFP to reserve that memory right away. Even if that's quite small for todays systems.

Bye, Olaf.

 

[dantheinfoman]

Hi Olaf, it's fine when 262144 is what's being put in there, but it errors out initially when it sees Sys(3050,1,2943090688). Thereafter it is fine.

Thanks

Dan