2 Replies Latest reply on Mar 24, 2010 4:54 PM by dandrake

    Import text fields with line-terminators -- how?

    dandrake

      Title

      Import text fields with line-terminators -- how?

      Post

      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?

       

        • 1. Re: Import text fields with line-terminators -- how?
          FentonJones

          Line breaks within fields look like returns, and are translated to returns when copy/pasted. But if exported, they are ASCII 11, VT (Vertical Tab). A tab-separated text file with them will import text separated by them into a field, and they will be field "returns."

          • 2. Re: Import text fields with line-terminators -- how?
            dandrake

            Thanks, I didn't know this feature, and it will be useful in the future. My problem now, though, seems to be on the other end: getting the data into FileManager in the first place. For this process, FoxPro has no such treatment of internal EOLs, and OpenOffice doesn't do tab-delimited output at all.

             

            Oddly enough, I seem to have tripped over a good solution for my needs.

             

            Read the dbf directly into OpenOffice spreadsheet; nothing special about this at all.

            If you like, format any column that has dates to use a good, readable format like 12/31/1999 if one can tolerate American concentions.

             

            And just put that out in .csv format and have faith.

             

            FileMaker will read it correctly, end-lines and all, and then one can clean up the table definition; e.g., changing the date fields from Text to Date.

             

            Seems to have been flawless. Pretty much.