11 Replies Latest reply: May 15, 2014 10:06 AM by Olly Bond RSS

    Mutiple Line Field adding line breaks

    Kristie _

      Hi,

      I have a multiple line field that is working well on the model and is loading into the SQL table but there are characters between each line that are making it not work so well when linking Access tables or excel queries:  It looks like two line breaks are added between lines in the field and so it is truncating the multiple lines in Access. (and adding a merged field in Excel).

       

      Any ideas on how to get around this?  thanks!!!

        • Mutiple Line Field adding line breaks
          Data Kruncher

          I'm not certain as to what's causing Excel merge cells, but Access might be seeing an ASCII value that it's not expecting for that data type and will truncate the field contents when it encounters that ASCII value.

           

          I suspect that it's either ASCII 10 or 13, and that brings up a bit of an interesting finding that maybe Gareth or Ken can shed some light on. This may have come up before here; I don't recall.

           

          I thought that a possible solution might lie in replacing all occurrences of ASCII 10 and 13 with a space, but when I built the appropriate calculated field Monarch didn't return the results that I expected.

           

          As a test, I used the formula Asc(Chr(1)) and tested each value from 1 to 20. Interestingly, the formula returned the test value in all cases, except for 10 and 13. In those cases, it returned zero. :confused:

           

          Sorry Kristie, this doesn't get you any further ahead at the moment.

            • Mutiple Line Field adding line breaks
              Olly Bond

              Hello Kristie,

               

              In Monarch, under Options, Input, there's an option to tell Monarch to "ignore unused print control characters (0-31)". Try that first.

               

              More diagnosis can be found using the utility from:[url]http://www.datawatch.com/_support/downloads_updates.php[/url] called "Examine".

               

              HTH,

               

              Olly

                • Mutiple Line Field adding line breaks
                  Kristie _

                  Thanks, I set that option and will see how it plays out with tomorrows data load.

                    • Mutiple Line Field adding line breaks
                      Kristie _

                      I set the option to ignore unused print control characters which didn't work.  I downloaded the "Examine" tool but honestly I cannot figure out how to work it.  Any ideas?

                      thanks

                        • Mutiple Line Field adding line breaks
                          Data Kruncher

                          You have to run Examine from the DOS command line. Before doing so, it might helpful to copy both the Examine.exe program and your report file to a c:\test folder, and rename the report to simply report.txt

                           

                          Now click your Windows Start button, select run and key "cmd" and hit Enter.

                           

                          Type:

                          c:

                          cd \test

                          examine report.txt[/CODE]

                           

                          hitting Enter for each line. The Examine program will ask you where to send the output. Just hit Enter to display it on the screen.

                           

                          That said, I'm not certain that this will reveal any useful insights for you. What does Examine report back to you?

                           

                          Kruncher

                            • Mutiple Line Field adding line breaks
                              Kristie _

                              Hi,

                              Yes this does not tell me much that I can understand:  

                               

                              Ascii  87 W  =    44572

                              Ascii  88 X  =     9579

                              Ascii  89 Y  =    83860

                              Ascii  90 Z  =    12585

                              Ascii  91 [  =        2

                              Ascii  93 ]  =        9

                              Ascii  95 _  =      881

                              Ascii  97 a  =      435

                              Ascii  98 b  =       15

                              Ascii  99 c  =       80

                              Ascii 100 d  =       19

                              Ascii 101 e  =      758

                              Ascii 102 f  =      341

                              Ascii 103 g  =       25

                              Ascii 104 h  =       47

                              Ascii 105 i  =       62

                              Ascii 106 j  =       11

                              Ascii 107 k  =       12

                              Ascii 108 l  =       63

                              Ascii 109 m  =      401

                              Ascii 110 n  =      417

                              Ascii 111 o  =      121

                              Ascii 112 p  =       14

                              Ascii 113 q  =        4

                              Ascii 114 r  =      710

                                • Mutiple Line Field adding line breaks
                                  Grant Perkins

                                  Hmm.

                                   

                                  The examine output is showing you all the characters (visible and invisible) in the file and giving a count.

                                   

                                  For completeness, which version of Monarch are you using?

                                   

                                   

                                   

                                  Grant

                                    • Mutiple Line Field adding line breaks
                                      Kristie _

                                      Sorry for the delay.  I am using version 10.5 of Monarch.

                                      thanks again for your insight.

                                        • Mutiple Line Field adding line breaks
                                          Grant Perkins

                                          Hi Kristie,

                                           

                                          The Examine output you posted looks like it is only part of the file - either that or you have very unusual file contents.

                                           

                                          If you let it run its output to a text file we should be able to see more. Somewhere it will tell us how many of each text format control character there are in the file. It provides a reference point for the scale of the problem, especially if we know how many records there are on the input and output sides.

                                           

                                          I read your initial post to mena that you are creating a table in Monarch and then exporting it to an SQL database. From there you are linking Access and Excel to the SQL data when producing queries. Is that correct? So what you are seeing is a problem with the SQL query within Access or Excel when you link to the previously extracted data. Is that correct or have I misunderstood?

                                           

                                           

                                          Grant.

                                            • Mutiple Line Field adding line breaks
                                              Kristie _

                                              Here is the entire examine output:

                                              D:\Monarch\Examine>EXAMINE 20DSP002.TXT

                                              Press 'Y' or 'y' &   =      222

                                              Ascii  63 ?  =      356

                                              Ascii  64 @  =      399

                                              Ascii  65 A  =   545222

                                              Ascii  66 B  =   112720

                                              Ascii  67 C  =   389602

                                              Ascii  68 D  =   320481

                                              Ascii  69 E  =   606215

                                              Ascii  70 F  =   120728

                                              Ascii  71 G  =    89079

                                              Ascii  72 H  =   233880

                                              Ascii  73 I  =   416640

                                              Ascii  74 J  =    32886

                                              Ascii  75 K  =   127938

                                              Ascii  76 L  =   286276

                                              Ascii  77 M  =   172203

                                              Ascii  78 N  =   354211

                                              Ascii  79 O  =   413244

                                              Ascii  80 P  =   196014

                                              Ascii  81 Q  =     8846

                                              Ascii  82 R  =   420091

                                              Ascii  83 S  =   386175

                                              Ascii  84 T  =   413659

                                              Ascii  85 U  =   129227

                                              Ascii  86 V  =   144189

                                              Ascii  87 W  =    44572

                                              Ascii  88 X  =     9579

                                              Ascii  89 Y  =    83860

                                              Ascii  90 Z  =    12585

                                              Ascii  91 [  =        2

                                              Ascii  93 ]  =        9

                                              Ascii  95 _  =      881

                                              Ascii  97 a  =      435

                                              Ascii  98 b  =       15

                                              Ascii  99 c  =       80

                                              Ascii 100 d  =       19

                                              Ascii 101 e  =      758

                                              Ascii 102 f  =      341

                                              Ascii 103 g  =       25

                                              Ascii 104 h  =       47

                                              Ascii 105 i  =       62

                                              Ascii 106 j  =       11

                                              Ascii 107 k  =       12

                                              Ascii 108 l  =       63

                                              Ascii 109 m  =      401

                                              Ascii 110 n  =      417

                                              Ascii 111 o  =      121

                                              Ascii 112 p  =       14

                                              Ascii 113 q  =        4

                                              Ascii 114 r  =      710

                                              Ascii 115 s  =      704

                                              Ascii 116 t  =      405

                                              Ascii 117 u  =       36

                                              Ascii 118 v  =       10

                                              Ascii 119 w  =       15

                                              Ascii 120 x  =        2

                                              Ascii 121 y  =      355

                                              Ascii 122 z  =        3

                                                      Error! Mismatched CR/LF combinations.

                                                      Use MSRP to match CR's to LF's.

                                               

                                                      This file does not contain any extended characters.

                                                      27460135 Total characters counted. /code

                                               

                                               

                                              I am putting the data in an SQL table and then using excel to query the table for only the records needed.  But the multiple lines are not showing even though they are there just with a break in just that field not the rest of the line.

                                               

                                              Thanks again!

                                                • Mutiple Line Field adding line breaks
                                                  Olly Bond

                                                  Hello Kristie,

                                                   

                                                  27 MB of text shouldn't be too big, so I don't think it's a file size issue.

                                                   

                                                  The Examine output is prompting you to download MSRP (Monarch String Replacement Program, I think) from the Datawatch downloads page again. You don't need to.

                                                   

                                                  Just run the Monarch Utility and select the "prepare files for Monarch..." button, then once you've selected the input and output filename, check the option replace all occurrences of a string, and I think I'd try two routes:

                                                   

                                                  a) get rid of LF (\10)

                                                   

                                                  b) replace \13 with \13\10, then replace \10\10 with \10 to trim the duplicate LFs.

                                                   

                                                  Experiment, and you should find a fix fairly easily...

                                                   

                                                  Best wishes,

                                                   

                                                  Olly