Hitachi Vantara Pentaho Community Forums
Results 1 to 5 of 5

Thread: Loop over Files with mapping?

  1. #1
    Join Date
    Jan 2017
    Posts
    5

    Default Loop over Files with mapping?

    Good Morning,

    I am actually pretty new to Pentaho Data Integration and what I “achieved” until now is just a bit trial and error within PDI. I am also new in this Forum so please don’t be upset if I overlooked something or did any spelling mistakes as I am no native speaker. But I hope you guys can still understand and help me.

    Right now I am trying to generate a new ETL process. I work with excel files just to let you know.
    I got like 15 different files for everyday and I need those to be compared to a file with date -1.
    E.g. for today I compare the file xxx_10/01/2016 with the file of xxx_09/01/2016

    For that I use the Merge Rows step.

    My problem is that I don’t want to it to be done with 15 independent jobs that just use a different file as source.

    To solve this I tried to get it a bit dynamically so I build a Job that loops over the file names and executes a Job for every Input file.
    But my problem is that I don’t know how to do the mapping. How can I prevent the Job to compares file xxx_10/01/2016 with zzz_09/01/2016 v.v.?

    Because in my solution I just hand over the path to the folder with the files for today (all 15) and the path to the files for yesterday (all 15)

    Right now its just for those 15 Jobs but as it works fine it will be expended to about 300 Files that’s why I can`t do it with single Jobs. Would be awesome if someone got an idea to solve it

    Best wishes

  2. #2
    Join Date
    May 2016
    Posts
    282

    Default

    You can add two parameters to the transformation, one to pass the root of file name (xxx or zzz) and another to pass the date, so you can tell the read xls step the filename to read is $proot_$pdate. If xxx and zzz files have the same structure you can create your transformation as usual, but if the files have different structure, you have to learn how to use metadata injection to use the same transformation to read and compare files with different structure.
    Regards

  3. #3
    Join Date
    Jan 2017
    Posts
    5

    Default

    hey and thanks for the quick answer! but that results in another question

    Quote Originally Posted by Ana GH View Post
    You can add two parameters to the transformation, one to pass the root of file name (xxx or zzz) and another to pass the date, so you can tell the read xls step the filename to read is $proot_$pdate.
    thanks to the "get System Info" Step I was able to get the date for today and yesterday and put it into a variable
    but how do I have to set the "set variable" step so that it works like you described above?
    how can I split the Name xxx_10/01/2016 into $pname_$pdate? right now I just use the short_filename that I get by using the "get File Names" but that already includes the Date.

    Quote Originally Posted by Ana GH View Post
    but if the files have different structure, you have to learn how to use metadata injection to use the same transformation to read and compare files with different structure.
    as soon as I solved the actuall problem I am trying to get the hang of it but thanks for the advise! but of course if someone got a usefull hint for me I would appreciate it.
    best wishes

  4. #4
    Join Date
    May 2016
    Posts
    282

    Default

    Take a look to the samples directory in your PDI installation, it has a lot of sample transformations and jobs focusing on the use of one step or typical ETL operations. There's a folder inside with an example of the use of metadata injection.
    To get the grip on the use of parameters, apart of the book Pentaho Data Integration Beginner's Guide, I used this blog post: http://diethardsteiner.blogspot.com....variables.html, it's on a older version of Pentaho but it still works the same.

    Regards

  5. #5
    Join Date
    Jan 2017
    Posts
    5

    Default

    thanks that helped me a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.