I've got a data set I need to get from FMP to mySQL. I have the
procedure all worked out except for one thing: How do I get FMP to
omit the extra line feeds when it exports? What I need amounts to a
Unix compatible text file. Often, only some of the records have the
extra LF, and I haven't found a text editor that lets me do a Find and
Replace for an LF by itself and just delete it.

Another example of the same issue is exporting the contents of a
container field as text. The path and file size data are formatted on
three different lines. How can I get rid of the line feeds in there
and make the data come out on one line, or even better, in separate
fields?

Is there a way to get rid of that extra line feed so data saved as
ACSII text can be Unix compatible?

--
FW

In article <91ms33paich80nhpq4mradoc264el97euq (AT) 4ax (DOT) com>, FastWolf
<wolfsofast (AT) gmail (DOT) com> wrote:

Firstly, are you sure the record data itself doesn't have these "extra
line feeds" in it. If they're in the FileMaker fields' data then it's
easy to replace them there using a Substitute function in a Calculation
field and then export the new field.


When I export data from the Mac version to a CSV file to be uploaded to
a website I get a file that the website doesn't like. In the end I
found the solution was to export the data from FileMaker to a "CSV",
then open that file in Excel and SAve As to a "CSV (Windows)" file and
then it worked when uploaded with no problem.

Excel does have a Save As to "CSV (DOS)" format as well that might help
you.


For Find / Replace functions on a Windows file itself, the best,
easiest (as well as cheapest - it's free) product I found in a recent
search was GSAR. http://gnuwin32.sourceforge.net/packages/gsar.htm
It is a small program controlled from the DOS prompt in Windows or via
a .BAT file. I'm not sure, but I think you can run one of those from
within FileMaker in a similar way to AppleScripts. You do need to learn
about the parameters / switches to give it though, as well as the hex
values for the the control codes like LF.

I'm not sure how you can find "LF by itself" to replace (I'm assuming
you mean as opposed to a CR and LF). Probably the easiest solution
would be to first convert CR LF to something else not in anywhere in
the text (eg. *-*), then convert the remaining LFs to nothing and
finally convert the *-* back to CR LF.


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

On Mon, 07 May 2007 12:52:35 +1200, Helpful Harry
<helpful_harry (AT) nom (DOT) de.plume.com> wrote:

At first I was going to say "yes I am sure" but on reflection I'm not.
There are several data entry people who create records in this DB and
it's just possible that they might be throwing in the extraneous LFs
without realizing it. Unfortunately, turnover being what it is and
also the fact that these people just aren't capable of being very
careful, I'm going to need another solution.

Right, that's the workaround I've been using. But I haven't figured
out how to reformat, for exampe, the text output of a container field.
That's something that I'd really like to work out: how to reformat
container data so I can export/import file references easily.

I will check this out. I'm pretty handy with scripting and the
command line interface so learning a few switches won't be a problem.

You're right, that's exactly what I meant. I guess I could define a
calculation field for export only that would do the substitution. Then
the records that DON'T have the problem would be unaffected, which is
important since the export-only field would affect all records.

Thanks for the advice, I appreciate it.

--
FW

On May 6, 3:44 pm, FastWolf <wolfsof... (AT) gmail (DOT) com> wrote:

If you mean, you have fields that are supposed to be just one line,
and some staff have entered a 2nd blank line (by pressing return
during data entry or something), then there are several possible
solutions:

in the simplest case simply replacing the field contents with:

leftwords(theField, 1)

strips leading and trailing whitespace, including tabs, linefeeds,
spaces, and returns the first "word" in the field. good for simple
'one word answer fields' like an age. check the help for what counts
as a 'word'.

leftwords(theField, wordcount(thefield))

is more robust, handling fields potentially containing multiple words.
("Mac Donald" or "1 800 222-1234")

what counts as a 'word' can be a little tricky though, especially if
you need to preserve isolated punctuation marks, and other non-
alphanumeric characters that might be standing alone, but for most
simple scenarios like a last name, ssn, phone, favorite color, age,
etc it works just fine.

If you need something to handle the more exotic corner cases, such
things can certainly be built.

-cheers,
Dave