AnsweredAssumed Answered

Import text fields with line-terminators -- how?

Question asked by dandrake on Mar 24, 2010
Latest reply on Mar 24, 2010 by dandrake


Import text fields with line-terminators -- how?


When I'm typing into a text field in a form, I can hit Return and get a line separation (End-line, EOL) there, all normal.


When I want to import data from files, what do I do to get the text fields, complete with their EOLs, in FileMaker fields?


My main data source is an old FoxPro database, with Memo fields that contain nice normal ASCII data, with line breaks at reasonable places. Importing this into a FM table is problematic in the first place: the method in the KnowledgeBase, which hacks tab-delimited files from Memo fields via a FoxPro command and then imports them, simply can't work right, at least from Fox version 2.6, because it writes the output from each long Memo field in multiple lines (with EOL on each, of course, as CR-LF) and that's not proper tabbed input; also, it can't be fudged to reconstruct the original text unambiguously.


So, I make a spreadsheet out of the table, importing into OpenOffice (clearly, Genuine Micrsoft Excel would do it also) directly from the dbf. I could now import this into FM from the .xls file, except that FM crashes when a field contains EOL characters.


So, I edit the spreadsheet first, replacing all the EOL sequences with something silly like ":!:"  . Easy to do with regular-expression searching.  This even allows use of a .csv file as an intermediary to read into FileMaker, which is nice because the data fields get handled well.


So here I am with a new FileMaker table. I'd like to get my EOLs back! Find & Replace would be pretty easy, but I see nothing in the documents about putting an EOL in the replacement string. The GUI doesn't think that's nice.


I can write a script to do it, or so it appears. Or I could export the whole table in XLM, and fix the EOLs back again with a Perl program, and hope FileMaker will read that XML file without balking. There are also postprocessing operations for some of my uses, but not if I want to use those text fields in a pretty report in PDF.


Is there some better way?