Plateforme Level Extreme
Abonnement
Profil corporatif
Produits & Services
Support
Légal
English
Export from VFP to Excel
Message
De
18/04/2003 07:16:23
Cetin Basoz
Engineerica Inc.
Izmir, Turquie
 
 
À
17/04/2003 13:55:52
Van Son Nguyen
Dessau Soprin Inc.
Laval, Québec, Canada
Information générale
Forum:
Visual FoxPro
Catégorie:
Autre
Divers
Thread ID:
00778968
Message ID:
00779111
Vues:
34
This message has been marked as the solution to the initial question of the thread.
>Hello to all,
>
>Actually I have a problem with the export in VFP : When I try to export a dbf file from VFP to Excel, the number of record exported is stopped at 16383 records and that, for any size of the export file. from 100K to 5 mb.
>
>Thank for your help

VFP versions up to 8 export only 16384 rows, VFP8 65536 (1 header) records. 65536 is current Excel sheet row limit and might not be so in future.
Up to 65535 (one header) simple solution is :
copy to ... type fox2x && W/o header
or :
copy to .. type csv && With header - VFP6+
Both of these formats are natively recognizable by excel.

If you need more than 65535 you need to divide data into columns, sheets or workbooks. Exporting into multiple workbooks is the fastest. However you could try exporting to multiple sheets. You can transfer data using datatoclip(), _cliptext, copy ... delimited with tab etc as well.
DataToClip() has a drawback you need to limit exported rowcount to a max of 2-3K per transfer block (not VFP's fault, a glitch in Excel itself).
Below is a sample transferring into multiple sheets w/o using datatoclip or any other _cliptext approach (Note that most time is consumed during copying data from one workbook to another - that's what I mean multiple workbooks would be fastest. Wish Excel be fast as half of VFP in those operations) :
Clear All
* Create a test cursor
Create Cursor testcursor (Rcno i,cType c(10), nType i, dType d)
Rand(-1)
For ix = 1 To 200000 && Create 200000 recs cursor
  Insert Into testcursor Values ;
    (Recco()+1,Sys(2015), Int(Rand()*1000), Date()-Int(Rand()*100))
Endfor

Set Safety Off

Wait Window Nowait "Pls wait sending data to Excel..."
lnTotal = Reccount()
oExcel = Createobject("Excel.application")
With oExcel
  .Visible = .T.
  oMasterWorkBook = .workbooks.Add && Add a new workbook
  lnMaxRows = .ActiveWorkBook.ActiveSheet.Rows.Count && Get max row count
  lnNeededSheets = Ceiling( lnTotal / (lnMaxRows - 1) ) && 1 row header

  lnCurrentSheetCount = .sheets.Count
  If lnNeededSheets > lnCurrentSheetCount
    .sheets.Add(,.sheets(lnCurrentSheetCount),;
      lnNeededSheets - lnCurrentSheetCount) && Add new sheets after old ones
  Endif
Endwith
With oMasterWorkBook
  For ix = 1 To lnNeededSheets
    .sheets.Item(ix).Name = "Page "+Padl(ix,3,"0")
  Endfor

  lcExportName = Sys(5)+Curdir()+Sys(2015)+".dbf"
  For ix = 1 To lnNeededSheets
    lnStart = ( ix - 1 ) * (lnMaxRows-1) + 1

    Copy To (lcExportName) ;
      for Between(Recno(),lnStart,lnStart+lnMaxRows-2) ;
      type Fox2x

    oSourceWorkBook = oExcel.workbooks.Open(lcExportName)
    .WorkSheets(ix).Activate
    oSourceWorkBook.WorkSheets(1).UsedRange.Copy(;
      .WorkSheets(ix).Range('A1'))
    oSourceWorkBook.Close(.F.) && Close w/o save
    Erase (lcExportName)

  Endfor
  .WorkSheets(1).Activate
Endwith
Wait Clear
PS:FoxyClasses' dbf2Excel also use another transfer method viaADO which can complete the process even if the data had memo fields too.
Cetin
Çetin Basöz

The way to Go
Flutter - For mobile, web and desktop.
World's most advanced open source relational database.
.Net for foxheads - Blog (main)
FoxSharp - Blog (mirror)
Welcome to FoxyClasses

LinqPad - C#,VB,F#,SQL,eSQL ... scratchpad
Précédent
Suivant
Répondre
Fil
Voir

Click here to load this message in the networking platform