importing text with carriage returns

[Coyne]

I have a text file that I need to import into a simple flat database. Fortunately due to the structure of the file, it's very easy to set up tsv for each of the fields. 

 

However, the last field will always have text with carriage returns. I do not want those carriage returns slipping to the next record.

 

I tried as a test to convert all carriage returns into the bar character "|" prior to import and then I was hoping that I could convert all "|" into carriage returns by doing a find/replace with ^p, but all that did was to substitute the "^p" with the "|" character, so that didn't work.

 

So, is there a way to do this? And please, I'm not strong with scripts and formulas so if you are going to suggest same, please write it out.

 

Thanks for any help,

 

Gary

[Lee Smith]

You can probably use the Custom Function Trim4 found Here instead of replacing the carrage returns with "|" 

 

HTH

Lee

[Coyne]

Huh?

 

I do not want to remove the carriage returns in the final text, I want to keep them but be able to import them into FM and not have new records created caused by the carriage returns in the body of the text of this last field during the importing process.

 

Currently the text is in a Word file and as such I can easily do global finds & replaces to remove the carriage returns prior to import (and avoid unwanted record creation). However, either I need a mechanism to return the unique character back into a carriage return after import or use some other mechanism to import the text into FM and avoid the extra record creation. 

 

For example, let's say that this is the text:

 

[Gary Coyne (tab)11-29-2012 (tab)Trying to import text (tab)Hi, my name is Gary Coyne

 

I'm trying to import text.

 

I also have a lot of carriage returns in the last field.

 

Is there a way to do this?]

 

 

Using standard tab delineated text, as text entered into FM, things will go along smoothly until the first carriage return and instead of 1 record, I'd end up with 7 records (including the carriage returns separating paragraphs in that last field).

 

Sorry if I wasn't explicit enough in my first attempt at explaining this. Is there a way to do this?

 

Thanks,

 

Gary

[comment]

I was hoping that I could convert all "|" into carriage returns by doing a find/replace with ^p, but all that did was to substitute the "^p" with the "|" character, so that didn't work.

 

Instead of Find/Replace, you should do Records > Replace Field Contents… > Replace with calculated result… =

Substitute ( YourLastField ; "|" ; ¶ )

 

This could be scripted as part of the import.

 

 

If you are able to manipulate the file prior to import, you might try converting the carriage returns into vertical tabs (ASCII 11) - Filemaker will automatically convert these to carriage returns upon import.

[Coyne]

Bwahhahaha! Of course, the replace field content. I didn't think to use that because I've always used that to do global replaces on the full contents of fields. I didn't realize that you can do focused replace as well. Outstanding!

 

Meanwhile, yes I can (and did) do manipulation of the text prior to import, but what's a vertical tab? I'm using Word on a Mac.

[comment]

what's a vertical tab?

 

 

It's a control character with a decimal code value of 11. I don't know if or how you can use it in Word - perhaps ^K might work?

[Coyne]

Alas no, I can't seem to find a way to make this work. Fortunately your code of before should do the trick just fine. Thanks ever so much, you've saved me weeks of work.

 

Garhy

[comment]

Well, it's best to use a text editor (such as BBEdit or its free sibling TextWrangler) for tasks like these, rather than a word processor. Of course, ultimately the best solution is to fix the problem at its source - i.e. when creating the original file.