I am importing from a csv in which there are hard breaks within some
fields. I would like to strip them, and wonder if this can be done
automagically.

Yes, they could be removed automatically, depending on the format of the CSV
file.

For example:

"12","Edward","Smith","123 Main St.","CA","95123"
"24","John","Jones","358 Elm Rd.","MI","48321"
"31","Mary","Adams","ACME Corp.
1000 Acme Way","WA","98246"
"44","Robert","Johnson","123 Main St.","FL","45382"

In this example, there is a return character in the middle of the record for
Mary Adams.

Because this particular CSV file has quotation marks around each field
value, a "true" record always ends with <quote><return>.

You can therefore change the end-of-record markers to something like "%$%"
not likely to appear in the actual data, change all return characters to a
space, then restore the "%$%" to <quote><return>'s.

If this kind of file is needed to be imported only once, or occasionally,
the best option is to use a word processor to make the transformation. If it
has to be done regularly, then you can use FileMaker to make the change:

1. Create a new table, Imported
2. Create a new text field, ImportedText (global)
3. Import the entire CSV file into ImportedText
4. Clean up the embedded returns with a calculation field like:

ParsedText (calculation, global, text result) =

Substitute (
Substitute (
Substitute (
ImportedText; "\"¶"; "%$%")
; "¶"; " ")
; "%$%"; "\"¶")

3. Turn the calculation field into a table using a script like this:

Go to Layout ["NewTable" (NewTable)]
Set Variable [$CleanData; Value:Imported::ParsedText]
Set Variable [$i; Value:0]
Loop
Exit Loop If [$i=ValueCount ($CleanData)]
Set Variable [$i; Value:$i + 1]
New Record/Request
Set Field [NewTable::CleaRecord; GetValue($CleanData; $i)]
End Loop

4. Export NewTable as tabbed-text, then re-import it as CSV.

We have to go through the script rigamole because in exporting a single
field FileMaker would convert all returns in that field to ASCII 11 (in
order to avoid the problem you're posting about in the first place).

There are further optimizations possible. The entire process could be
scripted of course. And you could replace the returns within fields with a
"%NL%" and then in the script, replace the "%NL%" with the "backwards P"
paragraph symbol, to preserve the returns within fields.

Kind of complicated, but I hope that helps.

Bill


"Andrew L." <no-one (AT) nowhere (DOT) net> wrote