Apache Commons VFS - How to open whatever dir follow by specific dir?

Question

I think this is simple thing but some how I fail with it.

I have this :

ftp://user:password@host/Folder/Folder/Folder/../${Year}/${Month}/*.csv

What I'm trying is to open whatever folder name follow by specific folder name. I'm try to access file with the path like that but fail. The fail is in this part /../.

/../ this part should mean whatever folder, but somehow the path is going back to previous folder. 3 folders are need to open have a fix name so I can't change it.

e.g when I use this ftp://user:password@host/Folder/Folder/Folder/../${Year}/${Month}/*.csv path, it will open folder like this ftp://user:password@host/Folder/Folder/${Year}/${Month}/*.csv. It's going back 1 folder and follow by folder from variable then the file.

I use this /../ because after 3 folders I open there is about 30+ folder should I check follow by two folder like ${Year}/${Month} and 3 types regex file like A*.csv B*.csv C*.csv.

${Year}/${Month} this variable is use as path and I get from Set Variable step on previous transformation and yes it is job level transformation.

I use the path to access file on Pentaho Data Integration a.k.a Kettle using Text File Input Step.

I have thinking to set /../ into variable value, but the variable have at least one value in it and two max so I can't because I have 30+ values.

I have try using Copy row to result with Get row from result and do Execute every input row on the Transformation, but the result always fail, every execute it always first row, same value from first row of field(field contain 30+ foldername I need to), it not move to next row with different value.

I have try this path ftp://user:password@host/Folder/Folder/Folder/ with this regex [^\/]+\/${Year}\/${Month}\/A.* (fail)
Or
this path ftp://user:password@host/Folder/Folder/Folder/[^\/]+\/${Year}\/${Month}\/ with this regex A.* (fail)

So, any other suggestion?

N.B My grammar is pretty bad, so stackers allow to edit this question.

Answer 1

If replacing /../ by /\.\./ does not work, you may try something like :

Answer 2

I don't think the Get File Names step can do multiple searches in a single line like you want, you could whoever list all folders from a single point forward.

ftp://user:password@host/Folder ... to list all subfolders from this point on, assign Include subfolders, with a .* RegExp, and in the Filters Tab, Get [Only folders], This will list all the folders from the starting point, from here you can use Filter Steps for verification of which one to use.

Answer 3

Well, after few days working on this thing and it's still fail to use that kind of path with regex.

But I got another alternative answer for that. It's required more step than it supposed to. So, here it is (You guys can use this for reference) :

1. Use Table Input step and take all folder names from your database that u want to use on path. If u don't have it, make one.

2. Use Formula step. Connect Table Input with Formula and generate the value.

   e.g :

   field name : [fill yourself]

   value : "ftp://user:password@host/Folder/" & [folder from db].

   You can combine as much as u need using "ftp://user:password@host/[your folder]/[again]/.." with & [table value] "/" & [any value] "/" ...

   And you also can generate regex value using Calculator step.

3. Use Get File Name step or Text File Input step. Connect Formula to this. Double click this step, check the checkbox Accept filename from a field. Choose [path] field that you have create before and also choose [regex] field you create.

Well then it's done, you can do anything after 3rd step. This is only alternative way, I really appreciate if someone can teach me how to do this only using path and regex.

P.S My apology for the grammar. Still learning.