8 Replies Latest reply on Jul 4, 2021 9:28 AM by Arya Nymeria

    Data to parameter flat file by shifting to new line

    Arya Nymeria Active Member

      Hello, I am trying to achieve to write the [GLOBAL_SAHRED]  as first line and date to the second line in a flat file in a single mapping. I am using dummy oracle as source and trying to create the output port and hard coded the global shared manually in expression transformation. But I am struggling how I can shift to the next line after the global shared is written to the flat file. Any clues or suggestions?

       

      Sample output in the flat file.

       

      [global_shared]

      date=20210630

        • 1. Re: Data to parameter flat file by shifting to new line
          Syed Aziz Guru

          Hello Arya,

           

          To get CRLF as End Of Line in a UNIX FlatFile target, do the following:

          1. Add an Expression transformation before the target.
          2. Add a new output port by concatenating the CHR(13) and CHR(10) to the last column.

          This produces the CRLF at the end of line. However, UNIX adds a LF after every line. To remove the LF added, write a command task to remove these empty lines. You could use the unix2dos utility.

          Example

          Sample Expression transformation:

          Exp_Trans_1.jpg

          Command Task that could be used in the workflow:

           

          Command_task_1.jpg

          Source Input file:

           

          As observed in notepad:                                           As observed in with EOL characters:

          Input Source File_1.jpg

          Intermediate Output file generated from the mapping:

          Intermediate File_1.jpg

           

          Final output file after command task is run:

          Final Output File_1.jpg

          Best regards,

          Syed

          • 2. Re: Data to parameter flat file by shifting to new line
            Nico Heinze Guru

            More complicated than necessary. There's an easier way to achieve this.

            OK, there are two ways, one is a little easier (namely the one I'll explain below), the other one (using an EXP with two output ports and a Normalizer to translate these two ports into two records) is a little "cleaner" and more universal. In particular the approach with the NRM will work on both Unix/Linux and Windows servers without any change.

             

            For this explanation I'll assume that you work with PowerCenter on Windows, I'll explain the difference to Unix/Linux afterwards.

             

            Let's assume you have an EXP into which you feed the output from the dummy source qualifier.

            Let's further assume you want to have this text in the parameter file (I can't see any attachment in the original post) so that all workflows in the GLOBAL_SHARED folder can use this parameter $$START_DATE:

             

            [GLOBAL_SHARED]

            $$START_DATE=2021-06-30

             

            Within the EXP, simply set up one output port with the following expression:

            '[GLOBAL_SHARED]' || Chr( 13) || Chr( 10) || '$$START_DATE=2021-06-30'

             

            When you now send this output port to a flat file target with a string field of at least 42 characters, the text will appear in two lines.

             

            Now where's the difference between Unix/Linux and Windows?

            As indicated by Syed, Unix and Linux use only a Chr(10) to determine the end of a text line; Windows always uses Chr(13) || Chr( 10).

            So, in order to make the expression above work under Unix/Linux, change it like this:

            '[GLOBAL_SHARED]' || Chr( 10) || '$$START_DATE=2021-06-30'

             

            Again, creating two output ports in the EXP, one for the '[GLOBAL_SHARED]' line and one for the actual parameter, and translating these two ports into two records using a Normalizer is a cleaner approach. And a good way to learn using the Normalizer.

            Not to forget that using a Normalizer you don't have to worry about the end-of-line characters, this is done by the flat file target writer itself. No need for you to deal woth Chr(13) and/or Chr(10).

             

            Regards,

            Nico

            • 3. Re: Data to parameter flat file by shifting to new line
              Arya Nymeria Active Member

              Thank you very much Syed Aziz. Unfortunately we are in windows environment.

              • 4. Re: Data to parameter flat file by shifting to new line
                Arya Nymeria Active Member

                Thank you Nico. The date will not be static but I will be using a date from varchar column. The date in the varchar column will be populated as below.  Dates can be any date. Thank you for all of your help.

                 

                 

                [GLOBAL_SHARED]

                $$START_DATE=2021-06-30

                 

                [GLOBAL_SHARED]

                $$START_DATE=2021-05-31

                 

                [GLOBAL_SHARED]

                $$START_DATE=2021-06-03

                • 5. Re: Data to parameter flat file by shifting to new line
                  Nico Heinze Guru

                  The hard-coded value was just an example. Where you get this value from, that's nothing I can tell you, so just take what I've explained as a starting point and add this date value as you need it.

                   

                  Beware that the header line [GLOBAL_SHARED] indicates that only workflows in a folder named GLOBAL_SHARED will "see" the parameter(s) in this section. If you mean to have all parameters visible for all workflows, you would need to name the section as {GLOBAL] instead of [GLOBAL_SHARED].

                  More information can be found in the respective chapters of the Workflow Basics or Advanved Workflow guide, I can never tell which one holds which information. :-)

                   

                  In addition please don't be insulted if this was clear to you; not every reader of this forum may have the necessary experience, so I wanted to make sure that each newbie reading this post understands the difference. No offense intended.

                   

                  Regards,

                  Nico

                  • 6. Re: Data to parameter flat file by shifting to new line
                    Arya Nymeria Active Member

                    Thank you Nico. No problem. I actually went ahead and build a standard way of what I was doing earlier. I used a dummy oracle table as source and in the expression transformation created an output port with CONCAT and CHR39 and connected to the output flat file. Date was populated by a different mapping and appended to the flat file. May be an old way of design.

                    • 7. Re: Data to parameter flat file by shifting to new line
                      Nico Heinze Guru

                      That design is completely ok, those things are still heavily used by many customers.

                      I would only suggest (yes, I'm a stubborn old know-it-all ) one little improvement.

                      The function CONCAT() is slower to use than the operator || (two pipe characters), so I would write

                          a || b

                      instead of

                          CONCAT( a, b)

                      The operator is faster to execute, and in my personal opinion a longer term is easier to read with the || operators instead of e.g. four or five CONCAT() functions.

                       

                      Regards,

                      Nico

                      • 8. Re: Data to parameter flat file by shifting to new line
                        Arya Nymeria Active Member

                        Thank you about the CONCAT() and ||. Never knew CONCAT() is slower than the operator. Thank you for this. Learned new today.