12 Replies Latest reply: May 15, 2014 10:09 AM by drobert _ RSS

    Trying desperately to preserve spaces and/or new lines in multi-line field

    Schorschi _

      Hi there,


      I'm having a heck of a time trying to get Monarch 10.5 to do what I want it to do.


      I have a report containing a multi-line field of variable length that needs to get exported to an MS Access database.


      I would like to preserve the formatting in the multi-line field as much as possible, however, I've noticed that Monarch appears to not preserve white space and line breaks. By 'not preserve white space' I mean that stretches of repeated blank characters get condensed into one.


      I could live with the condensing of white space, but need to find a way to preserve the line breaks.


      Here are two example records of the multi-line field from the report. (I am omitting other fields/columns of the report and have X-ed out most of the data.)


          XXX-XXXX   XXXXXXXX XXXXXXX XX XX X11-15-03  5.000% XX11 XX               |

          XXXX-XXXX  0                                                              |

          XXXXX-XXX  XX: 1 XXX: 1 XX:  1 XX: 1 XX: 1 XXX: 1 XXX: 0 XX: 0 XX: 0      |

          XX-XXX#    0112                                                           |

          XXXX-XXX   0149                                                           |

          XXX-XXX#   0901     = XXXX XX XXX XXXX                                    |

          XXXXXXX#   0901     = XXXX XX XXX XXXX                                    |

          XXXXXXXX#  0000     = XXXXXXXX XXXXX XXXXX                                |

          XXX-XXX    40090149 = XXXX XX XXXXX XXXX XXXXXX XX/5 XX                   |

          XXX-XX-XX  XX                       |  XXX-XXXX  XXXXXXX05.000XX11XX¢     |

          XXXXXXX    0                        |  XXX-XXXXX 0                        |

          XXXX-XXXX  01/30/04                 |  X-X-XXX   X                        |

          XXXXXXX#   005713                   |  XXX-XXXXX 0                        |

          XXX-XXXXX       $1                  |  XXX-XXXX  7                        |

          XXXX-XXXX  01/13/11                 |  XXXX-XXXX   /  /                   |




      NEW: XXX-XXXX   2                                                              |

      OLD:            0                                                              |

      NEW: XXX-XXXXX  05/15                                                          |

      OLD:            000{                                                           |

      NEW: XXX-XXXX   XXXXXXX05.000XX11XX¢                                           |

      OLD:            XXXXXXX05.000XX29XX¢                                           |

      NEW: XXX-XXXX   11/15/11                                                       |

      OLD:            11/15/29                                                       |

      NEW: XXX-XXXX   XXXXXXXX XXXXXXX XX XX X11-15-03  5.000% XX11 XX               |

      OLD:            XXXXXXXX XXXXXXX XX XX X11/15/03  5.000% XX29 XX               |

      NEW: XXX-XXXX   11/15/2011                                                     |

      OLD:            11/15/2029                                                     |

      NEW: XXXXXXXXX  X                                                              |

      OLD:                                                                           |



      I thought of making use of the pipe symbol '|' at the end of each line by replacing it with a unique string via a calculated field, which would then be picked up by Access after the export to be replaced by the DOS newline character combination CR LF (=ASCII codes 13 and 10).


      However, as you can see from my first example record, the pipe symbol can also appear as an infield column delimiter, which, of course, I don't want to convert to a newline.


      So far I have not been able to find a way to replace only either[/U] the in-line or[/U] the end-of-line pipe symbols in Monarch. Whatever string conversion I define in the REPLACE function, it always picks up both or none.


      So far, I have tried

      Replace(," | "," I ")

      Replace(,"|  ","I  ")


      in an attempt to replace the in-line pipe symbol by the letter 'I', but, the first Replace picks up both the in-line and end-of-line pipe symbol, and the second Replace picks up none.


      It seems this is happening because Monarch trims inline blank space and removes newlines before[/U] feeding the field contents into the formula of a calculated field.


      The first Replace from above searching for a pipe symbol padded by single spaces should only pick up the inline pipe symbol, since the end-of-line pipe-symbol is delimited by a blank space before and a newline after it.


      The second Replace searching for a pipe symbol with two trailing spaces is not being found at all (it is again meant to pick up the in-line pipe symbol), because Monarch reduces the two spaces after the in-line pipe symbol to one before handing the field contents over to the REPLACE function.


      I have the Trim leading and trailing spaces from Character and Memo fields[/B] option unchecked. Also, I'm aware of the Textline function, but the multi-line field can have up to 60 lines. Do I really want to have a calculated field with a formula with 60 instances of the Textline function? Can I even put that much code inside a calculated field defintion?


      Can anyone think of anything else I can do to differentiate between the in-line and end-of-line pipe symbols, so I can replace either one of them with a unique string that will then be handled accordingly on the Access side?



        • Trying desperately to preserve spaces and/or new lines in multi-line field
          Grant Perkins



          I think the following thread from last year may contain information that is useful to you.




          It may be a useful starting point. However I'm not sure if any of the content provides a solution for you specific problem, so do feel free to come back for further suggestions once you have assessed it.





            • Trying desperately to preserve spaces and/or new lines in multi-line field

              You could do this the long way. Which means using the substr function to cut sections you want from the detail line replace the | symbols you want replacing with I.such as below


              Substr(Transaction_Desc,1,100)"I"Substr(Transaction_Desc),102,100)[/code] etc as needed.


              Yes it might take a little work to do but then the hard part is over and you can preserve your end of line | markers.

                • Trying desperately to preserve spaces and/or new lines in multi-line field
                  Schorschi _

                  @Grant: Thanks for pointing out the other thread. It pretty much confirms my observations, but doesn't provide a solution or workaround. So, unfortunately, it doesn't help.


                  @Warlok: Unfortunately, Monarch removes any and all duplications of the white space character and reduces it down to one before[/U] submitting it to the formula of a calculated field; though this conversion is only done in a field with more than one line of actual content (cf. discussion thread Grant mentioned above).


                  So, any double space combinations simply won't exist when applying whatever search & replace logic in a calculated field. Therefore, I have no way of distinguishing between the one or the other pipe symbol, because they will both be enclosed by a single space on each side when it comes to applying the Replace function to the string.



                    • Trying desperately to preserve spaces and/or new lines in multi-line field

                      I'd reread what you had asked before and decided to reedit my answer. Would my edited response be able to handle the issue? You'd have to check and make sure the character in the first position you were replacing is a | character using an if statement and I know it would be quite some work to do, but I'm sure it could be done.

                        • Trying desperately to preserve spaces and/or new lines in multi-line field
                          Schorschi _



                          The field can be up to 60 lines long. I'm not sure it would be practical to create a calculated field with 60 instances of whatever formula logic would be required to handle each line.


                          I'll think about it some more, though. Will let you know if I figure something out that's worthwhile.



                          • Trying desperately to preserve spaces and/or new lines in multi-line field
                            Grant Perkins



                            The TEXTLINE approach may look long winded but then once created the computer does all the work and I don't think the line edits would take too long using cut and paste and tweaking the line number required. I think it would all fit into the formula space in V10 but if not you could use intermediate fields and build a 'pyramid'. I don't know if it would get you solution for you but it may be less work than you currently think.


                            If the pipe character solution would work for you other than the mid field vertical bar .... does that vert bar always appear in the same place across a line? If so you could do a check for that character at that position (Probably using the Substr function on the line) and change it on a line by line basis.


                            If it sounds like such an approach might work for you using your original idea let us know and we can have a play to see what comes up.




                              • Trying desperately to preserve spaces and/or new lines in multi-line field
                                Data Kruncher

                                While it's admittedly a less than optimal solution, without other options available it sounds like the solution will work.


                                Calculated field expressions can handle up to 32,768 characters so I'd imagine that it should meet your needs. Do yourself a favor and add some hard returns to make it more readable onscreen.

                                  • Trying desperately to preserve spaces and/or new lines in multi-line field
                                    Schorschi _

                                    Ok, so I created the following formula in a calculated field:


                                    If(IsEmpty(Textline(,1)),"",Textline(,1) + "CrLf") +

                                    If(IsEmpty(Textline(,2)),"",Textline(,2) + "CrLf") +


                                    If(IsEmpty(Textline(,60)),"",Textline(,60) + "CrLf")



                                    Every fiber in my programmer's body is revolting against such a construct...


                                    Before, I narrowed the field by one character on the right in order to exclude the end-of-line pipe symbol.


                                    That seems to work. Somewhat.


                                    Problems remaining are:

                                    Trying to add a newline directly by writing CHR(13) + CHR(10) instead of "CrLf" didn't seem to produce newlines at all, once the data was exported into an Access table.

                                    I was trying to avoid empty newlines with the IF-statement, but it seems the IsEmpty function does not properly recognize the output from the Textline function. I am getting 60 CrLf's in the output whether there are 2 or 60 lines in the field.



                                    Any ideas how to solve these?



                                      • Trying desperately to preserve spaces and/or new lines in multi-line field
                                        Grant Perkins



                                        ISEMPTY() is Boolean so needs to = something.  =1 if you are looking for an empty field.




                                          • Trying desperately to preserve spaces and/or new lines in multi-line field
                                            Data Kruncher

                                            Not trying to make light of your dilemma Georg, but you do know how to find them.


                                            I completely sympathize with the programmer struggle as it runs contrary to what makes sense and what should or ought to be done. Alas, it's the only option.


                                            Regarding the Chr(10) and Chr(13) problems, see [URL="http://www.monarchforums.com/showthread.php?3143-Mutiple-Line-Field-adding-line-breaks"]my first reply in this thread[/URL]. I recall having discussed this with Gareth, but at the moment don't see that discussion appearing in the forum.


                                            I recall others having the same problem when exporting to Access.


                                            As to avoiding the extraneous output, AFAIK the best option is to make your formula a bit smarter, which unfortunately adds to the requirement naturally as you've discovered.


                                            Does this slight change work any better?

                                            If(Len(Textline(,60))=0,"",Textline(,60) + "CrLf")[/CODE]


                                            Hopefully I balanced the ( and ) OK there, otherwise you get the idea.

                                            • Trying desperately to preserve spaces and/or new lines in multi-line field
                                              drobert _

                                              Hi Georg,

                                              I had come across a problem like this and found that IsBlank works (albeit may not be what you want) while IsEmpty did not.:confused: The difference between the two functions is that IsEmpty returns False (or 0) when the field contains only blanks while IsBlank returns True (or 1). I went back to a multi-line model and tried what you were doing and I had the same result as you.

                                              If I had:

                                              [SIZE=2]If( IsEmpty( TextLine( Variable[/I], 2 )), "", TextLine( Variable[/I], 2 ) + "CrLf" )[/SIZE][/CODE]

                                              would always return "CrLf" but the following returns nothing if the line contains nothing (other then the possibility of all blanks):

                                              [SIZE=2]If( IsBlank( TextLine( Variable[/I], 2 )), "", TextLine( Variable[/I], 2 ) + "CrLf" )[/SIZE][/CODE]


                                              BTW, the function does indeed work by simply using the format IsEmpty( x, # ) (or any other boolean function) in the If function since it is interpreted that a non-zero return means TRUE and a zero value means FALSE (similar to some other languages like Basic, xBase and Clipper:eek:, to mention a few). You can easily test that by creating a calculated field with the following formula:

                                              If( 1, "TRUE", "FALSE" )[/CODE]


                                              If( 100000, "TRUE", "FALSE" )[/CODE]

                                              or even

                                              If( -1, "TRUE", "FALSE" )[/CODE]

                                              would return TRUE



                                              If( 0, "TRUE", "FALSE" )[/CODE]

                                              would return FALSE.