65534 limit

Posted on 2011-10-11
Last Modified: 2012-05-12

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..

Question by:RVL
    LVL 41

    Accepted Solution

    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
      COPY NEXT 65000 TO ("DBFExport"+TRANSFORM(i)) TYPE XL5
      i = i + 1
    LVL 29

    Expert Comment

    by:Olaf Doschke
    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.
    LVL 41

    Expert Comment

    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    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://msd…
    In the modern office, employees tend to move around the workplace a lot more freely. Conferences, collaborative groups, flexible seating and working from home require a new level of mobility. Technology has not only changed the behavior and the expe…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now