export from memo fields causes loss of hard returns

[Jay Talbott]

I have an app originally written in A5v3 or 4.5, now in v.9
Until this year, the memo fields were working ok, but recently, the client has been having big problems with the table. It would bloat to 2 gigs with very little provocation. I know that this is a known issue with dbf memo files, and I have been wanting to redo the entire layout to conform more to the Dr. Wayne method of creating and maintaining memos that work (http://www.learn alpha.com/MemosThat...osThatWork.htm)

In the mean time, I decided to export the records to an ascii file and import them into a new table. This worked so well, I almost broke my arm patting myself on the back... until the client called and asked why all of his memo fields had lost their hard returns in the memos. Now all of the text in the memo fields is all run together.

I am contemplating going to SQL for this table but whenever I try to do most anything I get the message "Failure trying to move pointer to the beginning of the file". I cannot export the records to a new table directly, as the memo field gives the above message..

I have tried to use Alpha's memo repair as well as purchasing a third party dbf repair tool.

Ultimately, I would like to either create a new dbf file, with the hard returns in the memo field, or create an SQL table and use the varchar field, but that too has to have the hard returns.

Any thoughts on how I can export the records and leave the the hard returns intact?

Thanks.
Jay

[Stan Mathews]

Re: export from memo fields causes loss of hard returns

Have you seen

.MEMO_WRITE_TO_FILE()

?

[Tom Cone Jr]

Re: export from memo fields causes loss of hard returns

Jay, a technique that's worked for me involves appending half the records to a new empty table. If the append fails, I try appending the other half. Thereafter, each time an append fails I split the source records in half and try again. A bit tedious but you can find the broken record that way... enter it by hand. Naturally you'll want to park "good records" in a safe backup table after each successful iteration.

[Jay Talbott]

Re: export from memo fields causes loss of hard returns

Stan,
No, I did not know about MEMO_WRITE_TO_FILE() but will look into it. How would I best use that with a table that has 12,000 records? I am not asking for you to write the code (although I know that you could, and would do a great job) but just general advice or at most, pseudo-code.
Thanks.

Tom,
Thanks for the thought: the "rule of diminishing halves" is what I call that method. I will try that too.
Thanks.

Jay

[Stan Mathews]

Re: export from memo fields causes loss of hard returns

Teh example in the help seems a bit outdated so rather than point you to that.....

Given a table "data" with a memo field "mem"

dim tbl as P
dim fptr as P
tbl = table.open("data")
tbl.fetch_first()
filename = a5.Get_Path() +chr(92)+ "memos.txt"
fptr = file.create(filename, FILE_RW_EXCLUSIVE)
fptr.close()
while .not. tbl.fetch_eof()
    fptr = file.open(filename, FILE_RW_EXCLUSIVE)
    'Next 2 lines position insertion point at end of file
    size = fptr.bytes_get()
    fptr.seek(size)
    fptr.write_line(ltrim(str(tbl.recno(), 5,0)))
    fptr.close()
    tbl.mem.memo_write_to_file(filename, MEMO_APPEND)
    tbl.fetch_next()
end while

produces what you see in the attached screenshot.

[Jay Talbott]

Re: export from memo fields causes loss of hard returns

Thanks, Stan. That's very helpful.
Jay

[Stan Mathews]

Re: export from memo fields causes loss of hard returns

Since you want to be able to process this output file back to a table, I think, we shuold try to make sure the record identifier lines are unique. Suggest changing

fptr.write_line(ltrim(str(tbl.recno(), 5,0)))

to

fptr.write_line("~~~~~~"+ltrim(str(tbl.recno(), 5,0))+"~~~~~~")

so we get output like

~~~~~~1~~~~~~
1st line
second line
third line
~~~~~~2~~~~~~

~~~~~~3~~~~~~

~~~~~~4~~~~~~

~~~~~~5~~~~~~

~~~~~~6~~~~~~

~~~~~~7~~~~~~

[Jay Talbott]

Re: export from memo fields causes loss of hard returns

Stan,
Thanks so much for your help. I tried this with the first 10 records, I added a hard return between records, and it worked great.
However, after 1700 records, I got an error:

An attempt was made to move the file pointer before the beginning of the file.

I'll keep trying and keep you posted.
Thanks again.
Jay