Memo fields to text files 1

[dzdncnfsd]

I need to export a table with a memo field to a delimited text file that contains the memo field contents. How do I do this? I am using VFP 5.0.
Thanks,
Gladys [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[foxdev]

If you need the memo field contents to be included with the other fields (assumedly so, since you mentioned it is a delimited file), AND you are happy with only the first 254 characters of the memo, you can try something like this:

[tt]select MyField1, MyField2, left(MyMemo,254) as MyMemo ;
from MyTable
copy to MyTextFile.TXT delimited[/tt]
[sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[dzdncnfsd]

Thanks a lot, Robert. I tried it on a small file and it works great!! Now - is there a way I can check to make sure I do not have a memo field longer than 254 characters in the original file?
Thanks again. [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[foxdev]

is there a way I can check to make sure I do not have a memo field longer than 254 characters in the original file?

[tt]select max(len(trim(MyMemoField))) as Biggest from MyTable
? Biggest[/tt] [sig]<p>Robert Bradley<br><a href=mailto: > </a><br><a href=

http://www.foxdev.com/>FoxDev

- Visual FoxPro Development</a><br> [/sig]

 

[dzdncnfsd]

Thanks again. That was perfect for one of my tables. I checked the memo field length for the other table and some are longer than 254 - I don't want to be difficult, but is there a way to get the entire memo?
Thanks. [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]

 

[jpugh]

Gladys,

Although a little more &quot;brute force&quot;, I think the following should work :

SELECT exporttable
SCAN
STRTOFILE(MyField1 + &quot;,&quot;,'MyFile.txt',.T.)
STRTOFILE(MyField2 + &quot;,&quot;,'MyFile.txt',.T.)
STRTOFILE(MyMemoField + CHR(13),'MyFile.txt',.T.)
ENDSCAN

If performance becomes an issue, you can do the same thing with low-level file I/O functions (FOPEN,FWRITE,etc), but it does require a little more code.

Hope this helps.

Jon Pugh
[sig][/sig]

 

[Ropje]

that would be like this:

**********************************************************************************************************************
FUNCTION MaakDLM &&dutch for: make delimited(dlm)
********************************************************************************************************************
lcFile = &quot;c:\temp\rop.txt&quot;
lnOutHandle = FCREATE(lcBestand)
SELECT (lcCursor)
SCAN
*-- read line
lcRegel = &quot;&quot;
lcRegel = lcRegel + LeesRegel(lcCursor)

*-- write line
SchrijfRegel(lnOutHandle ,lcRegel)
ENDSCAN
FCLOSE(lnOutHandle)

**********************************************************************************************************************
FUNCTION LeesRegel
*read line
LPARAMETERS tcCursor

&& veld is field in dutch, velden are fields..
LOCAL lcRegel,laVelden,liAantal,liCnt,lcType,luVeld,lcVeld,lcWaarde
DECLARE laVelden[1]

lcRegel = ''

SELECT (tcCursor)
FOR liCnt = 1 TO AFIELDS(laVelden,tcCursor)
luVeld = laVelden[liCnt,1]
lcType = laVelden[liCnt,2]
liWidth = laVelden[liCnt,3]
liDecimal = laVelden[liCnt,4]

DO CASE
CASE lcType = 'C'
lcVeld = ALLTRIM(&luVeld)
lcVeld = STRTRAN(lcVeld,'&quot;',&quot;'&quot
lcRegel = lcRegel + '&quot;'
lcRegel = lcRegel + lcVeld
lcRegel = lcRegel + '&quot;,'
CASE lcType = 'D'
lcRegel = lcRegel + '&quot;' + ALLTRIM(DTOC(&luVeld)) + '&quot;' + ','
CASE lcType = 'L'
lcRegel = lcRegel + '&quot;' + IIF(&luVeld=.F.,'False','True') + '&quot;' + ','
CASE lcType = 'M'
lcVeld = ALLTRIM(&luVeld)
lcVeld = STRTRAN(lcVeld,'&quot;',&quot;'&quot
lcRegel = lcRegel + '&quot;'
lcRegel = lcRegel + lcVeld
lcRegel = lcRegel + '&quot;,'
CASE lcType = 'N'
lcWaarde = ALLTRIM(STR(&luVeld,liWidth,liDecimal))
lcRegel = lcRegel + '&quot;' + lcWaarde + '&quot;' + ','
CASE lcType = 'I'
lcRegel = lcRegel + '&quot;' + ALLTRIM(STR(&luVeld)) + '&quot;' + ','
OTHERWISE
RETURN .F.
ENDCASE
ENDFOR
RETURN lcRegel
ENDFUNC

********************************************************************************************************************
PROCEDURE SchrijfRegel
*write line
LPARAMETERS lnUitHandle,lcRegel

LOCAL liRegels,lcMemoRegel,liCnt,liPos

liRegels = OCCURS(CHR(10),lcRegel)
IF liRegels > 0
*in this case we have a memo with chr(10), which was a problem for my app, maybe you don't care..
lcMemoRegel = lcRegel
FOR liCnt = 1 TO liRegels
liPos = AT(CHR(10),lcMemoRegel)
lcRegel = LEFT(lcMemoRegel,liPos-1)
*-- write part before LineFeed:
FPUTS(lnUitHandle,lcRegel)
*-- low-level LF:
FPUTS(lnUitHandle,'')
*-- cut away what we wrote...
IF LEN(lcMemoRegel) > liPos
lcMemoRegel = SUBSTR(lcMemoRegel,liPos+1)
ENDIF
ENDFOR
*-- and the last piece..
IF !EMPTY(lcMemoRegel)
lcRegel = STRTRAN(lcMemoRegel,CHR(10),'')
FPUTS(lnUitHandle,lcRegel)
ENDIF
ELSE
*-- no CHR(10) ...
FPUTS(lnUitHandle,lcRegel)
ENDIF
ENDPROC

I can't promise it works, because I took out a lot of confusing code. This is the essential part. Maybe it helps you, or somebody...

greetings, great forum, Ropje


[sig][/sig]

 

[Ropje]

by the way:

STRTOFILE(MyMemoField + CHR(13),'MyFile.txt',.T.) doesn't work with my VFP5.0 ....

greetings Ropje.

[sig][/sig]

 

[JDurbin]

What about MEMLINES(), MLINE() and _MLINE in a count loop? [sig]<p>John Durbin<br><a href=mailto: john@johndurbin.com> john@johndurbin.com</a><br><a href=

http://www.johndurbin.com>

</a><br>MCP Visual FoxPro<br>
<br>
ICQ #9466492<br>
ICQ VFP ActiveList #73897253[/sig]

 

[jonscott8]

Hi Gladys,

Concatenating memo fields to records in a delimited text file, IMO, is a bad technique. Because in my mind, memo fields are meant to be used in cases where the data is multi-line. IOW, it has carriage returns & line feeds.

If you export a memo field and it does have carriage returns in it, your delimited text file is going to be inaccurate.

If you want to see what I'm am referring to: create a table with one character field. Append one record and replace the field with 'My ' + chr(13) + chr(10) + 'test'. Do a COPY TO MyFile.Txt TYPE DELIMITED and then view MyFile.txt.

If I were exporting a table with a memo field to a delimited text file, I'd use something like this:

USE MySourceTable
COPY TO MyFile.Txt TYPE DELIMITED FOR ExportMemo()

Use MyDestinationTable
APPEND FROM MyFile.Txt TYPE DELIMITED
GO TOP
SCAN
APPEND MEMO MyMemoField FROM 'MEMO'+TRANS(RECNO())+'.TXT'
ENDSCAN

FUNCTION ExportMemo
COPY MEMO MyMemoField TO 'MEMO'+TRANS(RECNO())+'.TXT'
RETURN .T.
ENDFUNC [sig]<p>Jon Hawkins<br><a href=mailto: jonscott8@yahoo.com> jonscott8@yahoo.com</a><br><a href= > </a><br>Focus on the solution....Not the problem.[/sig]

 

[dgrewe]

Gladys,
Maybe if we reverse enginnered this we might be able to help you a little better.

?? What is the final disposition if the file you are trying to create. ??
Export for Import into another Application File System. ?? (is so what application?)
Fo Historical reasons. ??
For a Backup ??
To store on CD's ?? [sig]<p>David W. Grewe<br><a href=mailtoave@internationalbid.net>Dave@internationalbid.net</a><br>[/sig]

 

[dzdncnfsd]

Thanks everyone for all the help. I have tried some of your suggestions and they were successful. I am still new at this so some of the more complicated stuff is a little over my head. I will try more when I have time for trial and error.

David,
Yes, my company is switching from the FoxPro application to a mainframe application. I need to convert my files to delimited text files so the new company can import them into their system. The table I am having the biggest problem with contains over 7 million records. And yes Jon, some of the memo fields do have carriage returns and line feeds and most are over 254 characters in length which as you know makes this a toughie!

Thanks again everyone.
Gladys [sig]<p>Gladys Clemmer<br><a href=mailto:gladys.clemmer@fifsg.com>gladys.clemmer@fifsg.com</a><br><a href= > </a><br> [/sig]