Not clear on exporting to XLSX format frm VFP9 2

[keepingbusy]

With reference to thread184-1605291 I have tried out some of the code posted without success:

COPY TO (THIS.cWorkFile) XLS
loExcel= CREATEOBJECT("EXCEL.APPLICATION")
loExcel.Workbooks.OPEN(THIS.cWorkFile)

First off, I get an error:

THIS can only be used within a method

so I'm guessing this is used within a form.

Having removed that and replace the THIS.cWorkFile with a variable (myfile) I still get an error:

OLE IDispatch exception code 0 from Microsoft Office Excel: 'myfile.xlsx' could not be found . Check the spelling of the file name, and verify that the file location is correct.

STORE "myfile" TO cWorkFile
COPY TO (cWorkFile) XLS
loExcel= CREATEOBJECT("EXCEL.APPLICATION")
loExcel.Workbooks.OPEN(cWorkFile)

My goal here is to export about 180000 records from a table into an XLSX file. We didn't have a problem in previous versions of Excel but as you know, they were limited to around 65000 records, hence the reason we upgraded.

We are using VFP9, Excel 2007, Windows 7

Any advice / guidance would appreciated.

Thank you


Lee

 

[Olaf Doschke]

&& ClipFormat - Fields delimited with tabs

I don't write such comments, you're partly right, the 3 falls from the sky. But what did not fall from the sky? "curTest" didn't I defined a cursor curTest. Take the rest for granted.

You can always refer to the help on lines you don't understand.

A good way to comment that would be

#DEFINE ClipformatTabDelimited 3

and then write
_vfp.DataToClip("curTest",,ClipformatTabDelimited)

But then the code is just sample code, and I urged you to lookup DataToClip yourself not only once. The sample code misses many things, not just comments. But how would that end up commenting every little thing somebody could misunderstand or not know. On what level should I go there, Lee?

The whole trick of the next line (oSheet.Paste(oSheet.Cells(1,1))) is that the Tabs in the clipboard spread the data into the cells, even though the paste itself is into cell A1 only. It's like having an excel 2d area in the clipboard and pasting it into some cell.

Bye, Olaf.

 

[keepingbusy]

Ok, found my mistake:

SELECT * FROM MYTABLE INTO [b]ARRAY[/b] curTest

whereas it should have been:

SELECT * FROM MYTABLE INTO [b]Cursor[/b] curTest

Here is my current code:

CREATE Cursor curTest (A C(21), B C(20), C C(18), D C(16), E C(15), F C(16), G C(40), H C(40), ;
  I C(20), J C(10), K C(254), L C(26), M C(12))

SELECT * FROM MYTABLE INTO [b]Cursor[/b] curTest

oExcel=CreateObject("Excel.Application")
oExcel.Workbooks.Add()
oSheet = oExcel.ActiveSheet
oSheet.Columns("B:B").NumberFormat = "@"
_vfp.DataToClip("curTest",,3)
oSheet.Paste(oSheet.Cells(1,1))
oExcel.Visible =.t.

It takes about 15 minutes to load the Excel spreadhseet (Using Windows 7, 2.20ghz processor, 4gb RAM, VFP9) but at least the leading zeros are now present (which is what I would have expected from the suggestions you guys gave and in particular Olaf)

Thanks again and I realise that I am probably coming close to outstaying my welcome on this forum with too many questions.

Lee

 

[Mike Lewis]

Lee,

Just a word about _VFP.DataToClip() ...

On the only occasions that I've used it, I found that it consistently crashed when there were more than a few thousand rows. I had to split the data into subsets, and transfer them in a loop.

But that was a long time ago. I don't remember which version of VFP or Excel I was using, but it was certainly old ones. It's likely to have improved since then.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Mike Lewis]

I am probably coming close to outstaying my welcome on this forum with too many questions

Just the opposite. It's a pleasure to try answer sensible questions that are clearly expressed. When I can't answer them myself (as was the case here), it's interesting to read other people's replies.

Unlike those "guess the question" threads that we were talking about elsewhere this morning.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[keepingbusy]

JRB

Since the code that Olaf shows above is not lengthy, I'd be willing to guess that you could take the time to test this yourself quite easily and then let us know yourself.

Just checked the Excel file (which is what I should have done before my previous post) and it only contains 65536 records (The maximum everyone is aware of).

Decided to abandon this project and will look for another avenue.

Thanks again

 

[Olaf Doschke]

Excel has a help topic "XLimits" stating:

Excel 2.x: 16'384 Rows x 256 Columns
Excel 3.0: 16'384 Rows x 256 Columns
Excel 4.0: 16'384 Rows x 256 Columns
Excel 5.0: 16'384 Rows x 256 Columns
Excel 7.0/95: 16'384 Rows x 256 Columns
Excel 97: 65'536 Rows x 256 Columns
Excel 98 (Mac): 65'536 Rows x 256 Columns
Excel 2000: 65'536 Rows x 256 Columns
Excel 2001 (Mac): 65'536 Rows x 256 Columns
Excel 2002: 65'536 Rows x 256 Columns
Excel X (Mac): 65'536 Rows x 256 Columns
Excel 2003: 65'536 Rows x 256 Columns
Excel 2004 (Mac): 65'536 Rows x 256 Columns
Pocket Excel 1.0: (Keine Angabe)
Pocket Excel 2.0: 16'384 Rows x 256 Columns (16'384 = 65'536 / 4)
Excel 2007: 1'048'576 Rows x 16'384 Columns
- Anzahl Rows: 1'048'576 Rows = 65'536 * 16
- Anzahl Columns: 16'384 Columns = 256 * 64


So Excel 2007 indeed allows >65536 rows, eve over a million.

Are you sure you are automating Excel 2007? I just added a for loop creating 70000 records and datatoclip plus paste does paste all these 70000 rows.

If you have several Excel versions installed, check out oExcel.Version, it should be 12. Or use oExcel = CreateObject("Excel.Application.12") to explicitly create an Excel 2007 automation application object.

Besides that you have another avenue, I point it out one more time: Access Excel as a database object via oledb provider, this also has the advantage tabs in your data won't hurt and you don't need special preparation of cell formats, oldb driver will make excel show the values as the types they are. Use Microsoft.ACE.OLEDB.12.0

Bye, Olaf.

 

[keepingbusy]

My apologies for not posting back sooner.

Olaf mentioned:

check out oExcel.Version, it should be 12. Or use oExcel = CreateObject("Excel.Application.12")

This is something I should have remembered but in any case having tried to create the desired file it is consistantly crashing our computer and I tend to agree with Mike (Lewis) who said:

Just a word about _VFP.DataToClip() ... On the only occasions that I've used it, I found that it consistently crashed when there were more than a few thousand rows. I had to split the data into subsets, and transfer them in a loop.

Now one sollution I found from research was to place a character such as ' in front of the field concerned containing the SKU/UPC numbers for example:

REPLACE ALL MYFIELD WITH "'"+ALLTRIM(MYFIELD)

By using this method I can revert back to creating the desired file, opening it and the end user can now use an Excel Formula to remove the leading ' from the desired feild in the spreadsheet.

Perhaps not the 100% outcome that you guys would expect or use but an acceptable one as far as I am concerned.

And finally, Mike mentioned:

Just the opposite. It's a pleasure to try answer sensible questions that are clearly expressed.

Thank you Mike, very reassuring....

COPY TO myfile.txt DELIMITED WITH "" with CHARACTER tab
DECLARE INTEGER ShellExecute IN shell32.dll ; 
  INTEGER hndWin, ;
  STRING cAction, ;
  STRING cFileName, ;
  STRING cParams, ;
  STRING cDir, ;
  INTEGER nShowWin 
cFileName = "excel"
cAction = "open"
ShellExecute(0,cAction,cFileName,"myfile.txt","",1)
CLEAR DLLS ALIAS 'SHELLEXECUTE'

Thanks again all for your posts and coding snippets.

Lee

 

[mm0000]

Another way to export to Excel 2007.It is very fast when working with large amounts of data.

CREATE cursor curTest (A C(21), B C(20), C C(18), D C(16), E C(15), F C(16), G C(40), H C(40), ;
  I C(20), J C(10), K C(254), L C(26), M C(12)) 
  
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')  
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')
INSERT into curTest VALUES ('00001','BBBBB','CCCCC','DDDDD','EEEEE','FFFFF','GGGGG','HHHHH','IIIII','JJJJJ','KKKKK','LLLLL','MMMMM')

LOCATE

lcTextMerge=('d:\temp\'+'temp.html') &&Change 'd:\temp\' to a valid directory

SET CONSOLE OFF
SET TEXTMERGE ON TO [&lcTextMerge] NOSHOW

DO xlsset

scan WHILE NOT eof()
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(CHR(160)+a))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(b))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(c))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(d))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(e))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(f))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(g))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(h))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(i))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(j))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(k))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(l))>><\td>  
   \<td class=xlChrblack>
    \\<<RTRIM(TRANSFORM(m))>><\td>  
\</tr>
ENDSCAN

\</table></body></html>

SET TEXTMERGE OFF
SET TEXTMERGE TO
*SET CONSOLE ON

oExcel = Createobject([Excel.Application])


With oExcel
  .referencestyle = 1
  .WorkBooks.Open(lcTextMerge)
  .ActiveWindow.DisplayGridlines = .t.
  *.visible=.t.
ENDWITH

oSheet = oExcel.ActiveSheet

osheet.saveas('d:\temp\temp.xlsb',50)  && Excel 2007 and greater - for earlier versions use temp.xls and 43
oExcel.visible = .t.


PROCEDURE xlsset
\<HTML>
\<HEAD>
\<META http-EQUIV='Content-Type' CONTENT='text/html; charset=windows-1252'>
\<META NAME='Generator' CONTENT='VFP'>
\<TITLE>a</TITLE>
\</HEAD>
\<BODY>
\<TABLE CELLSPACING=0 BORDER=0 CELLPADDING=3 WIDTH=264>
\<TR><TD WIDTH='100%' VALIGN='TOP'>
\    <style>
\<!--table

\.xlChrblack
\ {mso-style-parent:style0;
\ font-family:Arial, sans-serif;
\ mso-font-charset:0;
\ font-size:8.0pt;
\ color:black;
\ text-align:left;
\ white-space:normal;}

\\<BODY><TABLE>
\<tr>

 

[keepingbusy]

mm0000

Thank you for posting the code.

We have now resolved the issue by creating a straight forward TXT / TAB files and saving them in a specific folder.

The user then starts up Excel 2007 > Clicks on Open > Navigates to the relevant folder > Selects the TAB file and Excel prompts for the user to open the file in the desired format. Part of this process includes selecting the column containing the SKU/UPC numbers with leading zeros and changing the format to General.

This achieves the end result required with no loss of leading zeros.

Sometimes it's better to keep things simple than over complicate them. I suppose in this scenario the icing on the cake would have been to create the TAB file and have Excel open it with the correct formatting.

No worries, now moved on but thanks again guys for your help and inspiration.

Lee

 

[mikeopolo]

Some bush research on different methods gave me the following results, using mm0000's cursor data with 000's of rows added>

_VFPDatatoClip - <150,000 rows, then crashed with 'insufficient memory'

Arrays: <75,000 rows, then crashed with 'storage error'

VFP2XL (method from Cetin Basoz): <150,000 rows, then crashed with Querytable error

HTML: Achieved 500,000 rows, but note that the associated html file is 212MB


Regards
Mike

 

[Olaf Doschke]

Nice work, Mike.

But you can also always overcome these limits by working in chunks of smaller numbers of records. I doubt the errors on arrays will come with VFP9, as array limitations are dropped with that version, before array were limited to 65000 elements, still smaller arrays are handled faster moving in smaller chunks of eg each 1000 records than doing all at once.

Your research shows lower level ways like using \ and \\ are superior over more comfortable ones. That's completely to be expected. Still development can't for maintainability, time and budget reasons be broken down into low level programming. Otherwise we would always get the fastest performance of the final programs by programming assembler languages.

For creation of xlsx, like the first intent of the thread topic was, I'd opt for the Oledb or odbc option anyway. And going lower level I would make myself comfortable with the XML specification of excel xlsx files (

http://de.wikipedia.org/wiki/Office_Open_XML)

and then generate that XML directly. That would be even more to the point of it, than going through text or html.

Bye, Olaf.

 

[Olaf Doschke]

sorry, I posted a german version of

http://en.wikipedia.org/wiki/Office_Open_XML,

but you get the point anyway.

Bye, Olaf.