7 Replies Latest reply: May 15, 2014 10:03 AM by Bill Cebrynski RSS

    Multiple Detail lines in a single data row.

    Bill Cebrynski

      Hi - I was wonder if anyone can help me with this problem/challenge I am having.

       

      I have a txt file with a long lines - 1465 bytes.

      The line is structured so that the first 25 bytes of each line is header info.

      The next 1440 bytes is made up of 8 - 180 byte data segments - where each segment is structured the same.

       

      Therefore, I have, if you will, 8 detail lines on each line of input data.

       

      Is there anyway within Monarch to break out the 8 data segments into unique data rows in Monarch's table structure??

       

      I am running Monarch Pro V10.

       

      Thanks - Bill

        • Multiple Detail lines in a single data row.
          Data Kruncher

          Hi Bill, and welcome to the forum.

           

          You can handle this task with Monarch, but it'll require two models. The first will convert the single line data into multi-row data and export a new text file. The second model will extract data from the newly exported file which has a Monarch-friendly layout.

           

          To create the first model, you want a create a single 1465 character field, so it'll be a Memo field. Name it "A".

           

          Next, build a calculated Memo field to get the multiple row format you need. Since you know the fixed structure it's easy to model.

           

          Left(A,25)Chr(13)

          Substr(A,26,180)Chr(13)

          Substr(A,206,180)Chr(13)

          Substr(A,386,180)Chr(13)

          Substr(A,566,180)Chr(13)

          Substr(A,746,180)Chr(13)

          Substr(A,926,180)Chr(13)

          Substr(A,1106,180)Chr(13)

          Substr(A,1286,180)+Chr(13)[/code]The extra blank line at the end makes building the second model easier.

           

          Hide the A field, and in your export options menu, uncheck the option box to include field names as the first row of output.

           

          Now export the table as a fixed-length text file, and build a straightforward model for that file.

           

          HTH,

          Kruncher