8 Replies Latest reply on Apr 9, 2021 6:23 AM by Akilan Chandrasekaran

    Fixed width header, trailer and data

    Arya Nymeria Active Member

      I have a requirement where I have to create a custom header & trailers (hard code values) in a fixed width file and then the data. Any suggestions please?

       

      Planning to incorporate in one mapping. So In expression transformation hard code the header and trailer and load header first followed by data and trailer? But the header and data have different lengths. @

        • 1. Re: Fixed width header, trailer and data
          Syed Aziz Guru

          Hello Arya,

           

          Please see if the "Header Options attribute" suggested in KB 15150 "Using PowerCenter to Write Header Information to Flat Files" below is helpful:

           

          Support

           

          Best regards,

          Syed

          • 2. Re: Fixed width header, trailer and data
            Nico Heinze Guru

            That can be achieved easier.

             

            Just use three different target definitions for your target flat file. One target definition for the header, one target definition for the data lines, and one target definition for the footer line(s).

            Make sure that you set the correct Target Load Order in your mapping, meaning the header part of the mapping will be executed first, the data part second, and the footer part last.

            Don't forget to associate all three target definitions with the same target file name in the session.

            And finally make sure that for the data and the footer part of the mapping the check box Append If Exists is checked.

             

            This way the footer part of the mapping will be run first, and the file will be created (because the Append If Exists box is not checked for this target instance).

            Next the data part will be run, these records will be appended to the already existing file containing the header line(s).

            Finally the footer part of the mapping will write the footer line(s) to the already existing target file.

             

            Regards,

            Nico

            • 3. Re: Fixed width header, trailer and data
              Arya Nymeria Active Member

              Thank you Nico and I am in the same path but little differently.  I created a separate mapping for header and then detail and trailer in another mapping. Unfortunately I am not able to use target load plan as the source is same for both. Do you think I need to create separate mappings for all three?

              • 4. Re: Fixed width header, trailer and data
                Nico Heinze Guru

                No, there's an alternative. It just needs a little trick, but let me explain step by step.

                 

                The primary thing is to "sort" all data. This can be done by assigning two numbers to each line, namely first a "group number" (1 for the header lines, 2 for the data lines, and 3 for the footer lines) and second a "line number" (counted from 1 onwards within each "group").

                Now you can use two Joiner transformations utilising Sorted Input, both performing a Full Outer Join.

                The first JNR joins the header and the data lines by "group number" followed by "line number". As each of the two data streams has a different "group number", the JNR will never "join" header lines with footer lines but simply forward them one by one in the correct order.

                The second JNR joins the output of the first JNR with the footer lines, again by "group number" followed by "line number". Again, as both input streams use distinct group numbers, the JNR will never actually "join" two records but deliver them one by one in the correct order.

                Now you only have to write the output of the second JNR to the target file (and there's only one instance of that target definition in the mapping).

                 

                Your next thought probably will be, that can't work because the three groups have different formats.

                That's correct, no discussion.

                However, you can easily circumvent this problem using two more small tricks.

                Here's the first trick: Instead of writing each record to a flat file target definition with fixed-width fields, use an Expression transformation (to be exact, one EXP per stream, meaning one EXP for the header lines, one EXP for the data lines, and one EXP for the footer lines) which simply concatenates all data fields (padded to the correct length for each field using RPAD()) into one single string. Meaning that each EXP produces output consisting of three output ports: the "group number", the "line number", and the long string containing all fields padded to their needed final lengths.

                 

                The next trick is this: as far as I have understood your requirement, the header lines, data lines, and footer lines are of differing lengths. Meaning you cannot use the same flat file target definition for all three kinds of line.

                What you need here is a target definition with only one single long field, namely a string.

                Let's say the header lines are 450 characters long each, the data lines have a total length of 1528 characters, and the footer lines have a length of 328 characters each.

                That means you will need a flat file target definition with one single string of 1528 characters (the maximum of the three length values).

                And here comes the trick: you forward only the long string from the final JNR to the target, nothing else. Neither the "group number" nor the "line number". You only needed these two numbers to have all records "sorted" by the two Joiners, you don't need them in your target.

                 

                Is that clear so far? What have I left unclear?

                 

                Regards,

                Nico

                • 5. Re: Fixed width header, trailer and data
                  Arya Nymeria Active Member

                  I need to implement this. I will messg here once I am done. Sounds like a viable solution for sure. Thanks a lot.

                  • 6. Re: Fixed width header, trailer and data
                    Nico Heinze Guru

                    I'm pretty sure you will have questions when implementing this for the first time. Please, don't hesitate to post them here as soon as you get there.

                     

                    Cheers,

                    Nico

                    • 8. Re: Fixed width header, trailer and data
                      Akilan Chandrasekaran Active Member

                      If the body & footer can co-exist in a single mapping with the same target definition as per the above comments and header is creating a length issue, then header alone can be handled under session target properties - Header command (echo ...) can be used. Anyway, it is going to be hardcoded values.

                       

                      Also, if both header & footer are hardcoded values, Header & Footer commands under the session can be used and can keep data part in mapping.