5 Replies Latest reply: May 15, 2014 9:53 AM by Grant Perkins RSS

    Adding a CRLF in a formula of a Memo field

    Sunapee _

      I have a CSV file where one field in the CSV has embedded CRLF within the field quotes.  This was an issue with v7 because it would not import them as part of the field.


      In v8, there is a check box that covers embedded CRLFs in CVS Fields.  This works very well.  However, when I export the data to a abase table the CRLFs are all stripped out.


      I am trying to write a formula to force them back in and can't seem to get around the problem.  The formula I am using is:


      left(,81) + CHR(013) + CHR(010) + Substr(,83,81)


      Where the CHR(013) + CHR(010) is the CRLF.  Unfortunately, it still does not output the CRLF.


      Any suggestions??

        • Adding a CRLF in a formula of a Memo field
          Grant Perkins

          My understanding of this is that you can import CR/LF and then use it to select specific lines from a block of 'text' but exporting will always change the CR/LF type combinations to spaces.


          The implication is that you could export the lines separately or in a single block, but not as they originally appeared.


          That suggests to me that one approach might be to substitute another character or string for the CR/LF, run the export and then convert it back again. Using MSRP.exe for post processing rather than preprocessing would work I think. There are a few other programs that offer the same sort of functions. However, although probably OK for text file based exports (export to csv or fixed width field files) I doubt it would be an option for database exports. (Can databases deal with embedded cr/lf commands? Also I am not quite sure what you are exporting to according to your post.)


          This may not be a solution but it is my understanding of the way things have been written to work.


          Hope it is useful.



          • Adding a CRLF in a formula of a Memo field
            Sunapee _

            Thanks for the info Grant.  I am exporting to dBase memo fields, and yes they handle the CRLF very well.  Unfortunately, a limitation of dBase is that you can not use Memo fields in formulas to do things like search & replace.  However, I may be able to accomplish that via SQL statements & ODBC.


            It seems odd that Monarch would strip the CRLF when exporting, but then if it were a perfect world we would all be part timers.

            • Adding a CRLF in a formula of a Memo field
              Bruce _


              Perhaps the new function "TextLine" in version 8 can help. It splits memo fields into several lines. Here is a snippit from the help:




              Splits a multi-line string at the line breaks and returns the specified line.

              Where string is any string, presumably containing multiple lines, and n is a 1-based index indicating which line is to be returned.  Such a string will be the result of trapping a multiple line field using one of the Monarch Advanced field properties, End Field On options.

              A line break is defined as a LF (i.e., Chr(10)), or a CR/LF combination (i.e., Chr(13)+Chr(10)).  These are the most common forms in which line breaks are found in text values. 



              • Adding a CRLF in a formula of a Memo field
                Sunapee _

                Bruce...  WOW!  I did not know about that one, it will defiantly make things easier to pull each line of the memo field to a new field, output each line to desperate fields, then concatenate them back together once in dBase.  Still seems like a few too many hoops to preserve CRLFs in data that were already there.

                • Adding a CRLF in a formula of a Memo field
                  Grant Perkins

                  Thanks for the input on dBase - not something I have used for many years and then only sparingly.


                  As Brice has pointed out there is the option of looking at the new TEXTLINE function - which iw what my second paragraph was alluding to and, in my head at least, mentioned explicitely!


                  Missed the screen though. I blame it in the time of day (here in the UK) that I was posting!


                  In a similar connection it is worth mentioning that the result of a calculated field creation can now be a MEMO field, which was not allowed before.


                  My personal preference for the CRLF question would be to have a field level switch to allow or disallow with a model level switch to set a default parameter.


                  Absent that the character switch idea I suggested before seems like the cleanest sure option but no doubt there will be a better one.


                  Let's see what we get from the conference attendees ...