Copy to XLS is there a limit on # of records?

[Newtonm]

I am trying to copy a dbf of 64,000 records to an Excel spreadsheet. It only copies the first 16,000+ records. Is this a limit of Foxprow 2.6, Excel or the COPY TO function?
Is there a way round it?

Richard Myers
Newton Myers Consultants

 

[JRB-Bldr]

The limit is in Excel.

You might be better off pre-processing the data in Foxpro so as to reduce the number of records and then export (COPY TO...) the resultant records to Excel.

If this will not work for you, perhaps if you explain what you are trying to do, we can assist you with better suggestions.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[Newtonm]

Thanks for the reply. I have solved the problem by COPY TO..delimited and now all 68,000 records are copied, I am sending this to a third party who does not have FPW and wants to do some data checking. He is happy with the new .txt file.

I remember now that the "old" Excel only allows 16k rows (version2.n) where as newer versions allow up to 65k. En passant I wonder if VFP exports to a later version of Excel? Actualy I can't test it because I'm currently on "vacation" with my laptop which only has 2.6 on it. Just a thought.

Regards

Richard Myers
Newton Myers Consultants

 

[TheRambler]

>I wonder if VFP exports to a later version of Excel?

See EXPORT/ COPY TO Commands Don't Work with Entire Tables
It says it applies to VFP 3 and older versions, but I guess it was fixed after VFP 7.

 

[TamarGranor]

I don't remember exactly when the different versions got added, but VFP offers TYPE XL5, which handles dates much better than XLS. XL5 can handle up to 64K rows, though not all versions of Excel can read them.

Tamar

 

[severach]

I hope "he's" not checking your data in Excel. ~4,000 rows are going to be missing no matter how valid your output file might be. Why are we exporting at all? Excel directly imports DBF files and will datatype the columns correctly without any data damage. Excel will destroy mucho data when importing delimited CSV.

What I have done with idiots that insist on using Excel is to

COPY TO PART1 FOR RECNO()<=65536
COPY TO PART2 FOR RECNO()>65536

Then import them both into two Excel worksheets.

 

[JRB-Bldr]

a third party who does not have FPW and wants to do some data checking. He is happy with the new .txt file.

From what you say, your end result is not an Excel file at all. You seem to be using Excel to merely convert your DBF data to a TXT file format.

For safer route to do this and not miss records, etc., you might consider avoiding Excel all together.

By using Low Level utilities (FCREATE, FPUTS, FCLOSE, etc...) , you can directly create your own text file and not run into these limitations.

It is obviously more code work to create a low level write utility, but it works well.

Good Luck,


JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com