5 Replies Latest reply on Jul 21, 2019 8:54 AM by Nico Heinze

    indirect file loading based on source

    New Member

      Hi All,

      i need to load multiple files having different name and structure into target db. Problem here is i have to use indirect file loading.

      eg

      i have

      customer_123.csv

      customer_234.csv

      into one table

       

      producer_123

      producer_234

      into another table

       

      currently all files are available in same directory. Can i accommodate such requirement using 2 session having pre session command task creating file list and using this list as indirect file name for each session.

       

       

      please suggest

       

      regards

      mohan

        • 1. Re: indirect file loading based on source
          Pavel Henrykhsen New Member

          Hi Mohan,

           

          If your Integration Server is not Windows hosted, you can use:

          Input Type: Command

          Command Type: Command Generating File List

          Command: ls -1 <your folder>/*.*

           

          The issue with Windows-based server is that PowerCenter replaces / characters with \ and command that you have to use dir /1 *.* converted into dir \1 *.* and fails.

           

          Best Regards,

          Pavel

          • 2. Re: indirect file loading based on source
            Ramesh Kalluri Guru

            Hi Mohan,

             

            If both customer_123.csv and customer_234.csv are having similar structure, then you can copy these 2 file names into another file (for ex, customer.csv) and take this customer.csv as source. and use indirect file method in session properties. Follow the same approach for producer_123.csv and producer_234.csv files.

             

            If the source files are not in similar format, then you need to convert both the files into similar format using shell script/batch script and then follow the above approach to load the files.

             

            For example, below is the structure of customer_123.csv file:

              

            CUST_IDCUST_NAMEPH_NO
            1A123
            2B214
            3C100

             

            below is the structure of customer_234.csv file:

              

            CUST_IDCUST_NAMEPH_NODOB
            1A1238-Aug
            2B2148-Jun
            3C1008-Apr

             

            In this example, convert customer_123.csv file format to customer_234.csv so that both the files are having similar structure.

            Once both the files are in similar structure, you can use indirect file method to process all source files at a time.

             

            Regards,

            Ramesh.

            • 3. Re: indirect file loading based on source
              New Member

              Hi,

               

              Powercenter hosted on a windows machine, currently we do as follows using a command task:

              dir <File Location>\*$$Filename*.$$Extension* /B /O:N > <File Location>\LIST_$$Filename.dat

               

              make 'customer' and 'producer' as parameters. and also the extension if needed.

               

              works fine.

               

              file list is sourced in subsequent session after the command task.

               

              ensure to always delete if exist a file list so that there is no confusion in subsequent loading.

               

              Hope this helps.

               

              Regards,

              Suhail

              • 4. Re: indirect file loading based on source
                Francois Sammour Active Member

                hello Suhail,

                 

                can you please supply us with the and example of how to right the:

                "dir <File Location>\*$$Filename*.$$Extension* /B /O:N > <File Location>\LIST_$$Filename.dat"

                in the command task.

                 

                since I'm facing the same problem, where i have a file that is uploaded monthly into a folder, the uploaded folder has the same suffix but different prefix.

                 

                as i understood that the command you suggested functioned properly with the same actions.

                 

                waiting for your replay as soon as possible.

                thank you,

                 

                Regards,

                 

                Francois

                 

                • 5. Re: indirect file loading based on source
                  Nico Heinze Guru

                  Hold on, please let's get one step back for an issue which has not yet been clarified.

                   

                  @Mohan, you mentioned that the source files have different file structures.

                  Now the big question is whether they shall all be loaded into the same target structure or if each separate source file structure needs to be loaded into its own target DB structure.

                  Could you please answer this first? The response how to achieve this greatly depends on this question.

                   

                  Regards,

                  Nico