5 Replies Latest reply: May 15, 2014 9:54 AM by Tom Whiteside RSS

    Hopefully Simple Question

    J _

      Hello all,

       

      I am new to the software, and I am trying to use it for some data conversions.  I have a file right now with only two fields in it delimited by a pipe (|).  The first is an ID field, and the second is just a text field, but it sometimes wraps on multiple lines.  I want to string it out across one line for each record.  I do not care if the id column is included in the field or not, as I can parse them apart later.  I have each record separated by at least one blank line. 

       

      Here's a sample of the data format:

       

      7 | AAAAABBBBBCCCCCC

       

      8 | AAAAABBBBBCCCCCC

      DDDDD

       

       

      9 | AAAAABBBBBCCCCC

      DDDDDEEEEEFFFFFGGG

      GGHHHHHIIIII

       

      10 | AAAAA

       

      11 | AAAAABBBBBCCCCC

      DDDDDEEEEE

       

      And here is the format I would like to get it in:

       

      7 | AAAAABBBBBCCCCCC

       

      8 | AAAAABBBBBCCCCCCDDDDD

       

       

      9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

       

      10 | AAAAA

       

      11 | AAAAABBBBBCCCCCDDDDDEEEEE

       

      Basically I just want to concatenate any additional lines in to the same field.  I have tried a couple different solutions and have not had any luck yet.  I am hoping there is a simple solution that I am missing.  Any help would be appreciated. 

       

      Thanks!

        • Hopefully Simple Question
          Grant Perkins

          Hi J,

           

          First question is - is this an ascii text file (perhaps from a unix/linux system?) and do you know if it contains hard coded line feed characters?

           

          If not it may simply be that the 'page format' for the report is wrapping the text in which case you might be able to simply save the report with a different format (a3 Landscape for example or anything rather wide setting - even user defined maybe?) and work around it that way.

           

          On the other hand ...

           

          If the line feeds are embedded in the file you could strip them out/change them to spaces or whatever might be useful using an editor or the MSRP utility.

           

          You could then read the file as a delimited database if you have an appropriate Pro version of Monarch available.

           

          Similarly you could change the | to another character and then use the new character as a floating trap if you have V6 or V7 Pro.

           

          "|" is a special case here. A great character to use for delimited file but just happens to clash with a specific trap definition character when used in a report file. Not a problem if you wanted to (and could) read the file as a delimited input database file.

           

          Or ... (since you may not have a Pro version)

           

          You could do as you suggest and pick all the lines for a record using a trap that requires a non blank line to start the record, make the field a single line the width of the entire report. Then set the "field options" to "End Field On" and pick one of the options that will stop selecting the field at a blank line.

           

          That should give you your single field extract ready for parsing and you can then easily split the ID section from the main field (or should be able to but there may be something in your data that complicates that process as well. On the other hnd life is sometimes reasonable to us sp we can but hope!)

           

          Which way to go is a little dependent on the answers you have for the questions and then deciding which option offers the most effective way forward over all.

           

          If this doesn't mean much to you let us know (and tell us which version you have) and we will come up with some more ideas.

           

          Al the best,

           

          Grant

           

           

          Originally posted by J:

          Hello all,

           

          I am new to the software, and I am trying to use it for some data conversions.  I have a file right now with only two fields in it delimited by a pipe (|).  The first is an ID field, and the second is just a text field, but it sometimes wraps on multiple lines.  I want to string it out across one line for each record.  I do not care if the id column is included in the field or not, as I can parse them apart later.  I have each record separated by at least one blank line. 

           

          Here's a sample of the data format:

           

          7 | AAAAABBBBBCCCCCC

           

          8 | AAAAABBBBBCCCCCC

          DDDDD

           

           

          9 | AAAAABBBBBCCCCC

          DDDDDEEEEEFFFFFGGG

          GGHHHHHIIIII

           

          10 | AAAAA

           

          11 | AAAAABBBBBCCCCC

          DDDDDEEEEE

           

          And here is the format I would like to get it in:

           

          7 | AAAAABBBBBCCCCCC

           

          8 | AAAAABBBBBCCCCCCDDDDD

           

           

          9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

           

          10 | AAAAA

           

          11 | AAAAABBBBBCCCCCDDDDDEEEEE

           

          Basically I just want to concatenate any additional lines in to the same field.  I have tried a couple different solutions and have not had any luck yet.  I am hoping there is a simple solution that I am missing.  Any help would be appreciated. 

           

          Thanks! [/b][/quote]

          • Hopefully Simple Question
            Grant Perkins

            J,

             

            I just checked back in the archives (unintentionally!) and remembered your earlier post. So I figure you have Version 7, but not sure if you selected the Standard or Pro version.

             

            Or maybe you don't have 7?

             

            Grant

            • Hopefully Simple Question
              J _

              Hi Grant,

               

              The text file is actually an export from some dos based system.  (I could specify a character other than the pipe as a delimiter if that would matter.)  I am not sure if it contains hard coded line feed characters or not. 

               

              I would like to be able to read it as a database file, as that is the end goal, but cannot do so because the text wraps and it does not understand it as a delimited file. 

               

              I have version 7 pro, which I just bought. 

               

              I did create a template specifying a non blank line as a beginning trap and a blank line as the "End field on" option, and it does select the data I want, but does not concatenate the field.  It turns out like this:

               

              7 | AAAAABBBBBCCCCCC

              8 | AAAAABBBBBCCCCCC

              DDDDD

              9 | AAAAABBBBBCCCCC

              DDDDDEEEEEFFFFFGGG

              GGHHHHHIIIII

              10 | AAAAA

              11 | AAAAABBBBBCCCCC

              DDDDDEEEEE

               

              instead of like this:

               

              7 | AAAAABBBBBCCCCCC

              8 | AAAAABBBBBCCCCCCDDDDD

              9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

              10 | AAAAA

              11 | AAAAABBBBBCCCCCDDDDDEEEEE

               

              Any suggestions?

              • Hopefully Simple Question
                Grant Perkins

                J,

                 

                If there is any chance you could send me a page of the file that would be great - I'm working in the dark a little at the moment and I really like to see the light in the file! It's just the way I work. Confidentiality guaranteed but if you need to edit the data that's fine as long as core output format is unchanged.

                 

                I don't have access to my V7 installation for  couple of days but that should not make a lot of difference at this point.

                 

                That said, is the ID field always numeric? If so simply trap on the first character(s) of a line being numeric (assuming that any subsequent lines in the record do not start with a numeric character or character combination) and the multi line template should work. (This, rather than non- blank, is what I maent to put in my previous post.)

                 

                However I do expect there could be some anomalies with this idea if your second field is likely to have any numeric data in it.

                 

                I would be quite happy to use the sample information you posted but fear it may have lost something important to your requirements as part of the posting. It does happen.

                 

                Private message with my email address will follow shortly.

                 

                Grant

                 

                 

                Originally posted by J:

                Hi Grant,

                 

                The text file is actually an export from some dos based system.  (I could specify a character other than the pipe as a delimiter if that would matter.)  I am not sure if it contains hard coded line feed characters or not. 

                 

                I would like to be able to read it as a database file, as that is the end goal, but cannot do so because the text wraps and it does not understand it as a delimited file. 

                 

                I have version 7 pro, which I just bought. 

                 

                I did create a template specifying a non blank line as a beginning trap and a blank line as the "End field on" option, and it does select the data I want, but does not concatenate the field.  It turns out like this:

                 

                7 | AAAAABBBBBCCCCCC

                8 | AAAAABBBBBCCCCCC

                DDDDD

                9 | AAAAABBBBBCCCCC

                DDDDDEEEEEFFFFFGGG

                GGHHHHHIIIII

                10 | AAAAA

                11 | AAAAABBBBBCCCCC

                DDDDDEEEEE

                 

                instead of like this:

                 

                7 | AAAAABBBBBCCCCCC

                8 | AAAAABBBBBCCCCCCDDDDD

                9 | AAAAABBBBBCCCCCDDDDDEEEEEFFFFFGGGGGHHHHHIIIII

                10 | AAAAA

                11 | AAAAABBBBBCCCCCDDDDDEEEEE

                 

                Any suggestions? /b[/quote]

                • Hopefully Simple Question
                  Tom Whiteside

                  J, if your specific problem is not being able to concatenate your word-wrapped text fields, take a look at Nick Osdale-Popa's excellent [url="http://mails.datawatch.com/cgi-bin/ultimatebb.cgi?ubb=get_topic;f=1;t=000099#000002"]posting[/url] on this same topic that solved my own problem with wrapped fields.

                   

                  I had set up a calculated field concatenation function that did not work properly until Nick showed that there were two invisible ASCII characters, the carriage return (Cr, ASCII Chr(13)), and the line feed (Lf, ASCII Chr(10)), that needed to be included in the formula.  After that, the calculated field function with RTrims and LSplits worked great!

                   

                  Take a look at my version of your problem and Nick's formulaic solution - - and good luck!

                   

                  [size="1"][ May 03, 2006, 03:59 PM: Message edited by: Todd Niemi ][/size]