3 Replies Latest reply: May 15, 2014 10:13 AM by rbiskup _ RSS

    capture multiple lines

    rbiskup _

      Good Morning

       

      I am trying to capture data from a text file into excel file format.

      I want to capture the detail information only.  Each page has a header and footer.  Each detail record wraps to second line (first line in sample data is bolded).  I need to capture all data between characters ^^.  The ^^ characters are always in the same position on the first land the second line.  Only the data in parenthesis is not.  I also want to capture the data in between the parenthesis into separate fields as underlined in below example.

      Second line can have null values in between ^^.

      Any suggestions how should I approach the project?

       

      Thanks.  Ellea

       

       

      xxxxxxxxxx^     000x^          00/00/0001^                        00000000^ 0000000x     ^0000000   ^    ^     ^000.00  ^[/B]

      000.00  ^   x00.0   ^x000.0  00xx^  ^  ^  ^                                                    ( xxxx[/U];0, 0000000[/U];1,[U]x00000[/U], xxxxx[/U], 00[/U] )

       

       

      xxxxxxxxxx^     000x^          00/00/0001^                        00000000^ 0000000x     ^0000000   ^    ^     ^000.00  ^[/B]

      000.00  ^x00.0  ^x000.0  00xx^  ^  ^  ^                                                                   ( xxxxxx;0, 0000000;1,x00000, xxxxx, 00 )

       

      ffffffffffffffffffff

        • capture multiple lines
          Data Kruncher

          Looking at your sample, I suspect that your best option is a bit of a "slice and dice" exercise.

           

          Welcome to the forum, and to the deep end of the pool. :eek:

           

          For your detail template, I built a two line template, and trapped on the NN/NN/NN. You indicated that that section doesn't move around on you, so I did not elect to use a floating trap.

           

          I painted a single field, simply named "A", and made it wide enough to capture every character in the line. Then on the Advance Field Properties, I selected the option to end the field on a line count of 2.

           

          Off to the Table window, and it's time to carve up our big A field. As I don't know what the fields represent, I named them F1, F2, F3, etc. for the fields between the ^ characters. I named the fields between the parentheses as P1, P2, P3, P4, P5.

           

          I set all of the F fields to Character, but you can make them Numeric as necessary by adding a Val() function. We'll look at that shortly.

           

          Without further ado, F1 gets this formula:

          LSplit(A;100,"^",1)[/CODE]

           

          Similarly, F2 gets:

          LSplit(A;100,"^",2)[/CODE]

           

          Here the 100 value is a bit arbitrary: I picked a value large enough to cover all of the required fields. Monarch doesn't care if you go too large here. The last value in these formulas is the important part. It tells Monarch which segment of the larger string to return.

           

          When building these formulas, it's easiest to duplicate the last field that you made, give it a name and then slightly revise the formula as necessary.

           

          Some fields, such as the date, will require another function (CtoD() in that case), and others will need to be enclosed by Val() functions to create Numeric fields.

           

          Time for the fields between the parentheses. I used the ( character in the A field to find the start, and then split the text again using the comma and semi-colon as necessary. P1 becomes:

          LSplit(LSplit(RSplit(A,2,"( ",1),5,",",1),2,";",1)[/CODE]

           

          P2 needed just a bit more housekeeping:

          Trim(LSplit(LSplit(RSplit(A,2,"( ",1),5,",",2),2,";",1))[/CODE]

           

          as did P3:

          Trim(LSplit(LSplit(RSplit(A,2,"( ",1),5,",",3),2,";",1))[/CODE]

           

          and P4:

          Trim(LSplit(LSplit(RSplit(A,2,"( ",1),5,",",4),2,";",1))[/CODE]

           

          P5 might be the ugliest to examine, but it's really just more of the same:

          Trim(Strip(LSplit(LSplit(RSplit(A,2,"( ",1),5,",",5),2,";",1),")"))[/CODE]

           

          The exception with P5 is that we remove the right ) character.

           

          There may be another way to model this sort of report, but without seeing the actual report, I'm sure that this approach will be able to handle whatever you have so long as the number of fields is consistent from record to record.

           

          Let us know if this works for you, and if you have further questions.

            • capture multiple lines
              Grant Perkins

              Ellea,

               

              You say this is an incomig text file. Straight text, no formatting?

               

              If so it may just be that the line length is to large for the 'display' width chosen when outputting the file and that a little work could be applied to unwrap into a single line. Have you looked into that possibility?

               

              That would give you a single line to play with - a little easier perhaps. Might Excel then be able to read it successfully and import directly? (Much as I love using Monarch if there is a more direct way of doing something it shoud not be ignored ....    )

               

              If all the fields in line one and in line 2 EXCEPT those in parentheses are always the same size and in the same place it shoudl be possible to simply map them individually in the template. This might be a little timesaving compared to Kruncher's solution for your specific needs as outlined. However if the report format might have variables at some point then best bite the bullet now and go with Krunchers more flexible proposal.

               

              The parenthesised (is that a word?) data looks like it will need to be sliced and diced as Kruncher indicates.

               

              HTH.

               

               

              Grant

                • capture multiple lines
                  rbiskup _

                  Hi Data Cruncher

                   

                  Your slicing and dicing worked beautifully.  I have only one problem.  The last record on each page extends to the next page (second row of the record).  Each page has a heder so I’m missing the data from the second row of the last record.  Didn’t have a chance to play and fix it.  Any quick suggestions?

                  Thank you again. 

                  Ellea