I am exporting data from filemaker to Excel. Each time I do it
though, there are little boxes or symbols at the end of each field.
I've discovered that it is because there are extra paragraph markers
in some of the fields, which Excel recognizes and then creates a
symbol for when I export. I have over 6000 records with many more
fields, and I dont want to go back and delete these extra paragraph
markers in each field. Is there an easy way to delete these either in
FM or in Excel once the data is exported?

thanks, Tanya

On 17 May 2007 14:45:07 -0700, Tanya Nahman <tnahman (AT) gmail (DOT) com> wrote:

This one can be a real headache. What the paragraph symbols represent
are line feeds. When I had the same problem, what I did was export my
data to CSV. Then I opened my CSV file in a text editor and
eliminated the line feeds. After that I imported the CSV into Excel
and saved it as XLS.

The text editor I use is called Editpad, and there's a freeware
version at http://tinyurl.com/wo65. All you need to do to fix the
problem is convert the CSV file from Windows text to Unix text.
Editpad does that for you in one easy menu step.

Hope this helps.

--
FW

In article <1179438307.836687.65870 (AT) u30g2000hsc (DOT) googlegroups.com>,
Tanya Nahman <tnahman (AT) gmail (DOT) com> wrote:

If they are just extra carriage returns at the end of the field, then
they are quite easy to get rid of before exporting.

There are a number of ways to do it, but probably the easiest if you
need to keep exporting data on a regular basis is to create a set of
Claculation field that remove these extra carriage returns, and then
export these fields instead of the original ones.
eg.
ExportField Calculation, Text Result, Unstored
= Substitute(OriginalField & "*", "{ret}*", "")

where {ret} is the carriage return symbol that appears on one of the
buttons in the Define Calculation window (the "backwards P").

Including the "*" in the first and second parts of the Substitute
function means that it will only change the very last carriage return
to a nothing (""), and will not affect any other carriage returns in
the data ... not that those export as such anyway.


If this is a one-off export of the data, then you can instead use the
Replace command from the Records menu:

- first make a BACKUP of the file - this procedure can NOT
be undone if something goes wrong!

- then click in the appropriate field

- choose Replace from the file menu

- use the Replace via Calculation option and enter the
same calculation from above,
ie.
Substitute(OriginalField & "*", "{ret}*", "")

- click OK on the Define Calculation window

- double-check that the Replace window is definitely
replacing the correct field and then click Replace.

- repeat with other fields as necessary


You should also TRY to train the users to not put the extra return on
the end in the first place, but that's often like trying to teach a
fish not to swim. \


Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Fri, 18 May 2007 16:25:42 +1200, Helpful Harry
<helpful_harry (AT) nom (DOT) de.plume.com> wrote:

Your solution is much more elegant than mine. However, when I first
started out with FileMaker it would have been beyond me to implement
it even with the detail you provide. That's why I came up with my
method, which still works.

Thanks for sharing your knowledge. That goes for all the regulars on
this ng. As a fledgling FMP developer I can't put too high a value on
this ng as a resource.

It'd be interesting to find out which solution works out best for the
OP.

--
FW

On May 17, 9:25 pm, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Don't forget about the return-to-leave-field feature. In layout mode,
right click the field and select Field/Control:Behavior (Ctrl+Alt+K)
and check Return Key next to under "Go to next object using".

Scott in SoCal wrote:
Or use field validation to make the entry invalid if the last character
is a return (using the right() function) or if any character is a return
character (using the patterncount() function).

Or use the field's auto-entry calc function to always replace itself
with itself after the user exits the field, but stripping out any return
characters with the substitute() function.

In article <134rh3uaoplhp58 (AT) corp (DOT) supernews.com>, Howard Schlossberg
<howard (AT) nospam (DOT) fmprosolutions.com> wrote:

Both of these are good ways in newer versions of FileMaker, but the
original person didn't say what version they were using - I always go
with the simplest solution that works no matter what version ...
besides, I don't have the newer versions so I don't know about those
new fangled ways. ;o)

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

On Sat, 19 May 2007 11:10:39 +1200, Helpful Harry
<helpful_harry (AT) nom (DOT) de.plume.com> wrote:

Hey, if something works, why mess with it?

--
FW

On May 18, 11:39 pm, FastWolf <wolfsof... (AT) gmail (DOT) com> wrote:
Wow, thanks for all of the feedback. I use FM at my work, so I will
need to wait till I go back in next week to try out these solutions.
I wouldn't call myself a novice user, but definitely not advanced. It
looks like the return-to-leave-field feature might be the easiest.
Oh, and my work is using FM Pro v. 7, I believe. And yes, getting
people to stop putting the extra paragraph markers at the end of each
field IS like asking a fish not to swim

Thanks again for the feedback. I'll let you know what works. thanks,
Tanya

On May 18, 12:25 am, Helpful Harry <helpful_ha... (AT) nom (DOT) de.plume.com>
wrote:
Hello Harry,

Ok, I'm doing a combination of all of these suggestions. I've made
the fields to "return-to-leave", but that doesn't solve the problem
for the existing extra paragraph markers. I like the replace via
calculation function. The only thing is that if the field is empty,
then it is replacing it with a "*". Is there anyway to get it to
ignore empty fields?

thanks so much, Tanya