VFP6 Export data with memo field into text file

[traceytr]

Hi. I've never used VFP before, but I'm tasked with exporting all data from a table (including memo fields) into a text file. I found a great article at microsoft.com called "How to export memo fields with other field types to a text file with Visual FoxPro." It contains program code, which when dropped into a new VFP program and run, prompts you for the table name of the data you want to export, and then prompts you for the location and name for your new text file. (It's a great little program.)

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q241424

It works magnificently, however when I reach record number 1,734,080 of 3,089,360, the program stops running. At that point I have to end VFP with the Task Manager. Upon examination of the text file, it always stops at the same point on the same record.

* I can paste a lot more data into the text file, so I don't think it's a buffer issue.

* I've looked at the record in the VFP table and don't see anything strange (although I might not notice.)

* I tried to delete the record and "Pack" the table in VFP only to find I can no longer open that copy of the table. The error message says the table does not match the index. (I'm probably not doing this correctly.)

* I'd like to try creating three small tables instead, however I haven't quite figured out how to do that in VFP. Still working on that. There's a date field named "Date" in the table that I'd like to use as query criteria for this, however the syntax is not what I'm used to in SQL Server or Access, and I haven't figured it out yet.

* I thought maybe a datatype used in the program may not be large enough to accomidate 3 million (+) records. I've looked at the program, but as I said I've never used VFP, and I don't see a problem if it's in the program.

In the meantime, the powers-that-be need this for a vendor ASAP... (so what else is new, right?)

Does anyone have any idea why this program stops at record number 1,734,080 of 3,089,360? Any advice at all is greatly appreciated.

Tracey

 

[dgrewe]

Check the file size of the output file.
Are you Exceeding the Windows limitations of a 2 gig file?

David W. Grewe (Dave)

 

[traceytr]

I just ran it again, and it stopped in the same exact spot. At this point the size of the text file is 140,960KB, so it doesn't look like it's hitting the Windows limitations of 2 gig...

 

[traceytr]

I take it back... It does not end in exactly the same place in the record every time.

 

[Mike Lewis]

Tracey,

I can't see what's causing this particular problem. But the following points might help.

To split the table into three smaller tables, try this:

USE BigTable
lnThird = RECCOUNT()/ 3 && no. of records in one third
COPY NEXT lnThird TO SmallTable_1
GO lnThird
SKIP
COPY NEXT lnThird TO SmallTable_2
GO lnThird * 2
SKIP
COPY REST TO SmallTable_3

(I haven't tested the above, but I think it will work.)

To deal with your index problem, try removing the indexes. To do so, open the original table in the Table Designer (for example, with MODIFY STRUCTURE). On the Indexes tab, delete the indexes.

If you need the output file to be in a particular order, go back to the Table Designer to recreate the index. But only create the one index that you need, not the others. (That said, it's unlikely that the presence of the indexes will be the cause of the original problem.)

Re the Date syntax, I suggest you try the above first. If you have a particular problem with the syntax for your query, post a new question. You get better results in this forum if you keep each question as specific as possible.

Hope this helps.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[Koen Piller]

traceytr,
I am curious to know the contents of the particular reccord where the export is stopping, or maybe the contents of the next record when the app is stopping to export. Try to cath these contents of all fields, maybe the debugger can give you a clue. Personaly I dont think we are dealing here with the 2GG limit, since you are exporting to a textfile and you are using VFP6, dont recall the limits of VFP6.
Regards,

Jockey2

BTW: Why are you using an outdated version 6, VFP already has an vesion 9 SP1