EXPORT TO XLS LIMITATION 1

[rajeevnandanmishra]

Hi,

I am using export command to transfer data between my VFP tables and Excel files. I am using VFP6.0 & MSExcel 2000.

But it seems that export only copy 16383 rows from the VFP table. I have checked the limitation section on microsoft and it says in respect of VFP3.0/3.0b that the limitation for Export command to Excel is 16383 only.

I have not found any thing, if this problem persist in latest version of VFP & MSExcel or not.

Anyway, what i want now is, some solution (trick) by which i can export any number of rows (may be just limited by the excel row size).

Any help will be highly appreciated.


Rajeev

 

[Gandalf23]

We did it like this:

example table = customers.dbf

Customers.dbf -> customers.txt
Split customers.txt to Customers1.txt and customers2.txt
Read both into excel

 

[rajeevnandanmishra]

Thanks Gandalf23,

I appreciate this solution. But what happens that most of our users are not excel proficient. They can open an excel file and can do some calculations on it. But you know, when we try to open text file into excel you have to select the delimiter/ the data type of each column. Which is really a cumbersome and difficult thing for our users.

If the solution given by you is the last one, then i will surely try it.

Thanks again Gandalf23 for your imme. response.


Rajeev

 

[Mike Gagnon]

rajeevnandanmishra

Here is a work around to the limitations of Excel:

*  Program...........: Copy2xls.prg
*  Author............: Daniel Gramunt
*  Project...........: common
*  Created...........: 11.10.2000  17:25:06
*) Description.......: Replacement for the native COPY TO TYPE XL5 command.
*)                   : Excel 5 and Excel 95 have a limit of 16,383 rows per worksheet.
*)                   : The limit in Excel 97 and Excel 2000 is 65,536 rows.
*)                   : Since there is no TYPE XL8 command, VFP copies only the first 16,383 records.
*)                   : 
*)                   : This program works around this limitation and allows to copy as many
*)                   : records as the Excel version used on the user's machine supports.
*)                   : 
*)                   : The solution is very simple:
*)                   :  1. COPY TO TYPE CSV
*)                   :  2. Open CSV file and SaveAs(tcExcelFile) using Automation
*)                   : 
*)                   : Assumes that MS Excel (Excel 97 or higher) is installed on the 
*)                   : user's machine (well, it will also work with Excel 5.0 and 95, but of
*)                   : course the limit of 16,383 will apply).
*)                   : 
*)                   : Returns the number of exported records if successful, otherwise:
*)                   :   -1 = parameter missing or wrong type
*)                   :   -2 = no table open in current workarea
*)                   :   -3 = number of max. Excel rows exceeded
*)                   :   -4 = user didn't want to overwrite existing Excel file (SET SAFETY = ON)
*)                   :
*)                   : Performance note: The COPY TO command in VFP works very fast compared to
*)                   :                   anything that involves automation. However, since we use 
*)                   :                   automation only to open the exported file and to save it
*)                   :                   under a different format, there is almost no performance 
*)                   :                   penalty. 
*)                   : 
*  Calling Samples...: Copy2Xls("c:\temp\bidon.xls")
*  Parameter List....:      tcExcelFile - Path\Filename of the Excel file to be created.
*                    :          [tlDbf] - Specifies which TYPE to use with the COPY TO command:
*                    :                    .t. = TYPE FOX2X
*                    :                    .f. = TYPE CSV
*                    :                    Default = .f.
*                    :                    There are some differences between FOX2X and CSV. Depending on the
*                    :                    data to be copied, you can now specify which method to use.
*                    :                    See the remarks below for a description of the differences between
*                    :                    the two methods.
*                    : [tlNoFieldNames] - By default, the first row of the Excel sheet contains the 
*                    :                    fieldnames. If tlNoFieldNames is .t., the Excel sheet contains
*                    :                    only the data.
*                    : 
*  Major change list.: 26.10.2000: COPY TO FOX2X and SaveAs(< tcExcelFile >) instead of 
*                    :             &quot;assembling&quot; individual Excel files.
*                    :             Thanks to an idea from Çetin Basöz on the UT
*                    : 12.04.2000: COPY TO CSV instead of FOX2X.
*                    :             FOX2X has the following limitations:
*                    :             - problem with codepage 850 (e.g. character &quot;ã&quot;)
*                    :             - doesn't support long fieldnames (work around would be easy though)
*                    :             - doesn't support datetime
*                    :             CSV has none of the above problems, but has some other
*                    :             limitations:
*                    :             - logical fields are translated into F/T vs FALSE/TRUE.
*                    :               This isn't a problem, but to keep things consistent, we 
*                    :               don't use the native COPY TO TYPE XL5 anymore for tables
*                    :               with a record count below the limitation.
*                    :             - If a character field contains only digits and the value
*                    :               contains leading zeros, Excel translates this into a
*                    :               numeric value (e.g. &quot;00000100&quot; => 100). This could be a 
*                    :               problem, specially if the field is a PK and you later
*                    :               import the Excel file back into VFP.
*                    :             - [New 04.06.2001]
*                    :               If a character field contains double quotes and/or commas, 
*                    :               the result gets messed-up:
*                    : 
*                    :               - VFP ------------------------------------------  - Excel -------------------------------------------------------------------- - Remarks -----------------------------------
*                    :               cDesc1                   cDesc2                   cDesc1                                    cDesc2                  Next Field
*                    :               ------------------------ -----------------------  ----------------------------------------- ----------------------- ---------- ---------------------------------------------
*                    :               Rotating seal  1&quot;        Bibus:Deublin model 55   Rotating seal  1&quot;,Bibus:Deublin model 55&quot;                                    cDesc2 appended to cDesc1, all other fields
*                    :                                                                                                                                              are shifted to the left by one
*                    :               
*                    :               Bush 7/16&quot;               D=15/4,75 L=86,4         Bush 7/16&quot;,D=15/4                         75 L=86                 4&quot;         part of cDesc2 appended to cDesc1 (text until
*                    :                                                                                                                                              1st comma), the remaining text until the next 
*                    :                                                                                                                                              comma stays in cDesc2, the text after the 
*                    :                                                                                                                                              second comma is moved to field3, after that, 
*                    :                                                                                                                                              every field is shifted to the right by one
*                    :                                                                                                                                              
*                    : 04.06.2001  New parameters < tlDbf > and < tlNoFieldNames > added
*--------------------------------------------------------------------------------------------------
LPARAMETER tcExcelFile, tlDbf, tlNoFieldNames

#INCLUDE FoxPro.h

#DEFINE xlWorkbookNormal      -4143     && used by SaveAs() to save in current Excel version
#DEFINE ccErrorNoParameter    &quot;Parameter < tcExcelFile > : Parameter missing or wrong type (Expecting 'C')&quot;
#DEFINE ccErrorNoTableOpen    &quot;No table is open in the current workarea&quot;
#DEFINE ccErrorToManyRows     &quot;Number of records (&quot; + ;
                              ALLTRIM(TRANSFORM(lnRecords, &quot;999,999,999&quot;)) +;
                              &quot;) exceed max. number of Excel rows (&quot; -;
                              ALLTRIM(TRANSFORM(lnXlsMaxNumberOfRows, &quot;999,999,999&quot;))+;
                              &quot;)&quot;

*-- check parameter
IF VARTYPE(tcExcelFile) <> &quot;C&quot; OR EMPTY(tcExcelFile)
   ??CHR(7)
   WAIT WINDOW NOWAIT ccErrorNoParameter
   RETURN -1
ELSE
   tcExcelFile = ForceExt(tcExcelFile, &quot;XLS&quot;)
ENDIF

*-- make sure that we have a table/cursor in the selected workarea
IF EMPTY(ALIAS())
   ??CHR(7)
   WAIT WINDOW NOWAIT ccErrorNoTableOpen
   RETURN -2
ENDIF

LOCAL loXls, lnXlsMaxNumberOfRows, lnRecords, lnRetVal, lcTempDbfFile

loXls = CREATEOBJECT(&quot;excel.application&quot;)
*-- suppress Excel alerts and messages (similar to SET SAFETY OFF)
loXls.DisplayAlerts = .f.
*-- get number of max. rows from Excel. Before we can count the rows in a
*-- worksheet, we need to add a workbook.
loXls.workbooks.add()
lnXlsMaxNumberOfRows = loXls.ActiveWorkBook.ActiveSheet.Rows.Count - 1 && 1 header row

lnRecords = RECCOUNT()

*-- check if the number or records exceeds Excel's limit
IF lnRecords > lnXlsMaxNumberOfRows
   ??CHR(7)
   WAIT WINDOW NOWAIT ccErrorToManyRows
   *-- close Excel
   loXls.application.quit()
   RETURN -3
ENDIF

*-- respect SET SAFETY
IF SET(&quot;SAFETY&quot;) = &quot;ON&quot; AND FILE(tcExcelFile)
   IF MESSAGEBOX(tcExcelFile + &quot; already exists, overwrite it?&quot;,;
                 MB_YESNO + MB_ICONQUESTION + MB_DEFBUTTON2) = IDNO
      *-- user selected < No > so we bail out
      *-- close Excel
      loXls.application.quit()
      RETURN -4
   ENDIF
ENDIF

IF tlDbf
   lcTempDbfFile = AddBs(SYS(2023)) + SYS(3) + &quot;.DBF&quot;
   COPY TO (lcTempDbfFile) TYPE FOX2X AS 850
ELSE
   lcTempDbfFile = AddBs(SYS(2023)) + SYS(3) + &quot;.CSV&quot;
   COPY TO (lcTempDbfFile) TYPE CSV
ENDIF

lnRetVal = _TALLY

*-- open exported CSV file
loXls.Application.Workbooks.Open(lcTempDbfFile)

IF tlNoFieldNames
   loXls.ActiveSheet.Range(&quot;1:1&quot;).delete
ENDIF

*-- save as Excel file
loXls.ActiveSheet.saveAs(tcExcelFile, xlWorkbookNormal)

*-- delete CSV file
IF FILE(lcTempDbfFile)
   DELETE FILE (lcTempDbfFile)
ENDIF

*-- close Excel
loXls.application.quit()

RETURN lnRetVal

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first

 

[rajeevnandanmishra]

Thanks Mike,


Very good program.

But i am afraid, i can not use this.
As when we copy/open from csv to xls then the character fields with only digits are not behaving properly (you have also suggested this )

And in my system most of the table consists part_no and in more than 70% of part_no contains digit only (starting with zeros also).

I am also thinking for some more option (and you experts also think).

After 1/2 more thought i will be in a condition to give suggestion to my client about any rectification of this problem.

Thanks again Mike.

Raj

 

[ramani]

HI Rajeev
Look in this link

http://support.microsoft.com/support/kb/articles/q252/6/15.asp

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[rajeevnandanmishra]

Hello Ramani,

Thanks for your response.

But unfortunately that link is also using

COPY TO < > TYPE XL5

It is having a limitation of 16383 rows.

So, I want more

Rajeev

 

[rgbean]

Rajeev,
Quoting the VFP help file &quot;XL5 ... Creates a Microsoft Excel version 5.0 worksheet file. ...&quot;. Excel version 5.0 had a limit of 16,384 rows. I believe version 7.0 (Excel 95) was the first to allow 64k rows, and no version of VFP (including 7.0 SP1) supports this file type. So it's really an Excel limit - not FoxPro.

You can COPY TO ... TYPE FOX2X or TYPE DELIMITED - all 32-bit versions of Excel can import these. You could also use Automation of Excel to directly load a spreadsheet if you know each workstation has a copy of Excel installed.

Note: VFP 8.0 has raised this limit to 65,535, but it's still true that older versions of Excel will only use the first 16,384 rows.

Rick

 

[rajeevnandanmishra]

Rick,

What I understand from your comments is, that Excel 5.0 had the 16,384 rows limit and the later versions are having 64k rows or so. That means. If i use

COPY TO <> XLS && Please notice i have not used XL5

Then depending on the Excel version installed it will exported either 16k or 64k ?

Like i am having Excell2K on my machine. But when i export the data it only comes with 16k rows and not 64k (which it can accept).

So, If it is limitation, then it is surely VFP limitation.

Anyway, Thanks for your comments.


Raj

 

[rgbean]

Raj,
But the same help file states that for XLS, VFP &quot;Creates a Microsoft Excel version 2.0 worksheet file.&quot; This is an even older version!

Since VFP doesn't use (or even check for) the version of Excel on a system, what version it is doesn't really matter.

MS decided that since automation is a much easier (and more flexible) way of moving data from VFP to other Office products, this was the preferred method. Prior to the 8.0 announcement, MS had repeatedly suggested that because most developers were using automation or 3rd party conversion utilities, that the Fox team would spend thier time updating/adding more requested features. After all it, like any other programming team has limited time and resources and have to prioritize what's changed. The fact that they have made this change for 8.0, tends to indicate that they do listen IF enough people request it!

Rick

 

[dakotafox]

Here was my solution to the problem because we produce these type of reports on a monthly basis.

*---Unfortunately excel spreadsheets handle only up to about 65,000 records on
*---a single spreadsheet thus we need to create multiple worksheets for them
if y13Excel
count to nTotRecs for ALLT(myfield)=c13TArr
go top
*------Copying into Worksheets has limitations on # of Rows to Export to
*----so as to not push the threshold we choose to break it at 16000 lines
store 10000 to nMaxPerPg
store int(nTotRecs/nMaxPerPg)+1 to nTotPages
if nTotPages>1 &&We need to create multiple worksheets
select myfields;
from mytable;
ORDER BY myfields;
where ALLT(myfield)=allt(c13TArr);
INTO TABLE &c13TDbf
...........
*+++++++++++++++++++++++++++++++++++++++++++++++++++++++
select TempExp
set message to &quot;Creating Multiple Excel Files for MYFIELD: &quot;+c13TArr+&quot; with &quot;+allt(str(nTotRecs))+&quot; Records&quot;
mRecsLeft=nTotRecs
for x=1 to nTotPages
*---beginning of the looping for multiple pages(groups of 16000 records)
store justpath(c13XLOut)+&quot;\&quot;+juststem(c13XLOut)+&quot;_&quot;+allt(str(x))+&quot;.&quot;+justext(c13XLOut) to c13XLS
*---now select between the start of the recordset or balance left
if x=1
store 1 to mStart
else
*---this will move us thru the recordset, 16000 records at a time
store nMaxPerPg*(x-1)+1 to mStart
endif

goto mStart
**---origline removed and account added
if mRecsLeft < nMaxPerPg
*---this indicates there are less than 16,000 records to copy and will be the last page(group)
COPY TO &quot;&c13XLS&quot;;
FIELDS myfields;
NEXT mRecsLeft type XLS


else
*---this indicates there are more than 16,000 records to copy and need to break up balance left, again
COPY TO &quot;&c13XLS&quot;;
FIELDS myfields;
NEXT nMaxPerPg type XLS

endif
if mRecsLeft > nMaxPerPg
mRecsLeft=nTotRecs-16000
endif
endfor
*---we have finished this group and need to open the working table again
select mytable
else
endif
endif

 

[ramani]

HI Rajeev,
I have exported more than 60,000 records. PLease check and then decide
ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[rajeevnandanmishra]

Oh Dear Ramani,

Are you teasing me?

Let me know how ?

Rajeev

 

[ramani]

HI Rajeev,

Give your email or email me. I will send you sample code.
No teasing. It is real. I tested again just now.
Limit is Excel. It is about 66K rows.


ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com