[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

65534 limit

Posted on 2011-10-11
3
Medium Priority
?
703 Views
Last Modified: 2012-05-12
Hi

We are using VFP 9 our dbf file is about 100,000 records. We are trying to export to xl5, the problem is the 65534 limit. Is there a way to export the first 65534 then the next 65534 etc..

Thanks
0
Comment
Question by:RVL
  • 2
3 Comments
 
LVL 43

Accepted Solution

by:
pcelba earned 2000 total points
ID: 36950763
Try different format, e.g. CSV, it allows more than 65535 rows.

To export less number of rows repeatedly is also possible:

USE YourTable
i = 0
DO WHILE NOT EOF()
  COPY NEXT 65000 TO ("DBFExport"+TRANSFORM(i)) TYPE XL5
  SKIP
  i = i + 1
ENDDO
0
 
LVL 30

Expert Comment

by:Olaf Doschke
ID: 36953918
The limit comes from excels v5 format, not from foxpro, so you're only able to output chinks of 65534 rows to several xls files, as Pavel does in his code. I'd go for CSV. I think using XLS will output a xl8, but that might be wrong and even output lower excel version sheets. Import and APPEND both offer the xl8 option, but that doesn't help you here with output and I don't know if Excel 8 does not have the same limit anyway.

Bye, Olaf.
0
 
LVL 43

Expert Comment

by:pcelba
ID: 36957231
You may also insert data into Excel sheet via ODBC. I've created following sheet (named Book1.XLSX):
 Excel sheetand following commands can insert as many rows as you need:
 
? sqlconnect() && Select Excel Files from the dialog and the previously created XLSX file
*-- The result should be 1

*-- Select existing data
? SQLEXEC(1, "select * from [sheet1$]", 'cs1')
BROWSE  && Display data 

*-- Insert  new data
? SQLEXEC(1, "insert into [sheet1$] (aaa, bbb) values ('New text',3)")

*-- Check inserted records
? SQLEXEC(1, "select * from [sheet1$]", 'cs2')
BROWSE  && Display data

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Visual FoxPro (short VFP) is a programming language with it’s own IDE and database, ranking somewhat between Access and VB.NET + SQL Server (Express). Product Description: http://msdn.microsoft.com/en-us/vfoxpro/default.aspx (http://msd…
Most folks would know the basics of how Dropbox works, so that’s not the purpose of this article. Security is what it’s all about, so here I’ll share how I choose to secure my Dropbox Account and the Data it contains.
Screencast - Getting to Know the Pipeline
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question