Memo Field

[naguaramipana001]

hi gurus



I am trying to learn Foxpro on my own so take it easy on me



here is where I am, I have created a select statement like the following

select field1,field2 from table into cursor mytest



then I wanted to create an excel file from this cursor



copy to filename type xls



well here is the problem



I get one colunm on my excel file that contains field1 and field2 is not there , then I checked field2 and is a memo field, HOw can I get the text inside the memo field to go into the cursor and therefore to the xls file as a separete colunm.



Thanks a bunch



VFP9

 

[JRB-Bldr]

You might want to try something like:

SELECT field1,;
   LEFT(field2,200) AS field2 ;
   FROM table ;
   INTO CURSOR mytest

SELECT MyTest
COPY TO ExclFile.xls XL5

That will extract the left 200 characters from field2 and put them into a character field within the result cursor.

Then the export to Excel will work.

Good Luck

 

[Mike Lewis]

Hi Naguaramipana001,

JrbBldr has given you the correct solution, although you should bear in mind LEFT(Field2, 200) will only give you the first 200 characters of the memo field. You can improve that slightly by changing it to LEFT(Field2, 254).

What you are doing here is converting the memo field to a character field, which can be exported easily to Excel. But character fields are limited to 254 characters, so you'll lose anything in the memo field after that point.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Nifrabar]

In addition to Mikes suggestion:
If your memofield contains more tha 254 characters you might put the '2nd' 254 characters by:
substr(field2,255,254) as field2a
-Bart

 

[Olaf Doschke]

The proposed solutions can be a lightweight solution if your memo field values are short.

Without any conversion o some kind you can't use the COPY TO command, so for a general solution you'd need to ole automate excel and set Cell values to the full memo content.

Memo fields have quite some disadvantages besides not being exported by COPY TO, they also can't be indexed, can't be used with DISTINCT or GROUP BY. I'd only use them for larger texts or in their binary flavour for storing files.
I also tend to use them for file paths, as those can be larger than 254 chars and with unicode every byte is worth only a nibble or less.

Bye, Olaf.

 

[naguaramipana001]

Thank you all a bunch, thanks

 

[Talbess]

I've used this construct many times and it works well (if the memo fields are short), BUT, memo fields use CR-LF (0D0A) at the end of each line. This displays as two black boxes in Excel cells. If you type into a cell using Alt-Enter for each new line, there are no little black boxes and the new lines display in the actual cell, so there is something different about how excel formats line feeds. I have tried stripping out the 0D or the 0A from the memo data but this doesn't work.

Does anybody know what has to be done to the memo text to make it display without the little black boxes for each 0D0A sequence?

 

[JRB-Bldr]

Remember that you can use the STRTRAN() command to convert CRLF's to some other characters such as spaces.

You can do that in your preliminary SQL Query where you extract the Memo field data.

Good Luck,
JRB-Bldr

 

[Talbess]

I did some more checking on this and Excel uses just LF whereas FoxPro uses CRLF so I now include something like:
strtran(memo_text,Chr(13),'') as memo_text
in the query to strip out the CR's.
Only trap is that Excel still displays boxes for the LF characters until I turn Word Wrap on for the column

 

[JRB-Bldr]

"Excel still displays boxes for the LF characters"

Well if it bothers you, you can always do
strtran(memo_text,Chr(13)+CHR(10),' ') as memo_text

Or
strtran(memo_text,Chr(10)+CHR(13),' ') as memo_text
whichever works for your situation.

Good Luck,
JRB-Bldr

 

[Mike Lewis]

Talbess,

Your point about CRLFs at the end of each line might be true if the user is typing directly into the memo editor, but that's not necessarily always the case. The text might have been entered into some other control, or written to the memo programmatically. It's possible that you might just have a CR or just a LF at the end of each line. You would need to take account of that when doing your STRTRAN().

Also, you mentioned using Alt-Enter at the end of the line. I think you meant Shift-Enter.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

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

 

[Talbess]

Hi Mike,
You're right (as usual) - there are lots of ways of getting text into memo fields and the separator could be CR, CRLF, LFCR or LF, and I have found instances of every one of these in data that I have on hand. So to do it properly the strtran has to look at three possibilities, just CR, just LF or the two together (order doesn't matter when stripping the CR). I'll try and come up with a succinct way to do it, but I think it will still take two passes.

Alt-Enter is used to get a linefeed when typing into a cell in Excel. It genarates a single LF character.

Regards
Greg (aka Talbess)