topic Re: Sort FTP files using dates in Talend Studio

Sort FTP files using dates

daez — Wed, 09 May 2018 14:57:05 GMT

Hello everyone,

Sorry for my english skills, my java skills & my Talend skills that is all pretty new to me & I'm learning everyday here.

I'm currently working on something that I have no idea how the best I should start on. I have an FTP access where they will be files which are named like "TEXT_yyyyMMdd_hhmm". I have to sort get the latest files (so sort by alphabetic I guess?), do some data transform (xml to csv, new column ... = tMap?) then load back on the FTP the files and delete only the oldest.
So if i have :

1. TEXT_20180506.xml
2. TEXT_20180504.xml
3. TEXT_20180508.xml
4. .....
I want to only get the TEXT_20180508.xml , put it in a Tmap & output it on the same FTP with .csv . And once its done, delete the oldest one (TEXT_20180506.xml).

What should be the best way ?

I can only think of tftpconnection > tftpget > tmap (+java code) > outputdelimited but I dont feel it.

Looking for advices; Thanks !

Re: Sort FTP files using dates

manodwhb — Thu, 10 May 2018 13:35:33 GMT

@daez, you need design in below way.

in tjava you need to set a pattern using below code

String pattern_suffixe = "*.xml";

globalMap.put("yyyyMMdd", TalendDate.getDate("yyyy_MM_dd"));

globalMap.put("pattern", "*TEXT_"+ TalendDate.getDate("yyyyMMdd") + pattern_suffixe);

tFTPget as a file mask you should use ((String)globalMap.get("pattern"))

tJava-->On Subjob Ok--->tFTPConnnection-->On Subjob Ok--->tFTPGet-->On Subjob Ok--->tFtpList--->On Subjob Ok---> tXMLinput-->main-->tMap-->tfileoutputdelimitted

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 09:01:45 GMT

Hello @manodwhb , thanks for your time !

I tried this yesterday and still struggling on so I thought i could ask some help, I did as you said. For the tJava, this is my code (I added this on red) :

String suffixe_fichiers = "*.xml";

globalMap.put("yyyyMMdd", TalendDate.getDate("yyyy_MM_dd"));

globalMap.put("TEXT_yyyyMMdd_hhmm", "*TEXT_"+ TalendDate.getDate("yyyyMMdd_hhmm") + suffixe_fichiers);

All is working fine but I dont get how it is suppose to get the newest file and delete the oldest, nothing here is comparing the date?

It only takes & convert the files that equals the current date / hour. IE : If i launch my job at 09:10 at 05/11/2018 it will work only if there is a file named : TEXT_20180511_0910.xml on my FTP.
Yet, i would like to get the oldest one. How should i do that?

Again, thanks for your time !

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 09:46:08 GMT

@daez,with logic which i provided to get the lates file.it will not delete the Old files form FTP server.

to delete friles from server you need to use tFTPDelete in the tPostjob.

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 10:16:42 GMT

Hi @manodwhb , thanks for your reply,

I step back considering your answer. But i still have an error :

[statistics] connected
C:\Users\François\Desktop\Test\*TEXT_20180511_1109*.xml (La syntaxe du nom de fichier, de répertoire ou de volume est incorrecte)
[statistics] disconnected

(translate : syntax of file name, repertory or volumetry is inccorect)

My tjava :

String suffixe_fichiers = "*.xml";
globalMap.put("yyyyMMdd", TalendDate.getDate("yyyy_MM_dd"));
globalMap.put("TEXT_yyyyMMdd_hhmm", "*TEXT_"+ TalendDate.getDate("yyyyMMdd_hhmm") + suffixe_fichiers);

My tFTPGet filemask :

((String)globalMap.get("TEXT_yyyyMMdd_hhmm"))

My tFTPFileList filemask:

((String)globalMap.get("TEXT_yyyyMMdd_hhmm"))

My tfileInputXML filename:

"C:/Users/François/Desktop/Test/"+((String)globalMap.get("TEXT_yyyyMMdd_hhmm"))

Currently on my FTP i have only 2 files to check if he get the latest; they are named :

TEXT_20180511_0907.xml
TEXT_20180511_0349.xml

Do you have any idea how can i fix this ?

Thanks a lot

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 10:19:27 GMT

@daez,which component is failling?

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 10:25:34 GMT

I think its the tFileInputXML, but considering all the code i gave in the last post, the tFTPGET doesnt put anything in my folder too. It only works if i rename the file on FTP with the good dates hour & minutes.

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 11:18:05 GMT

@daez,if i understand correctly,since you were using hhmm in filename,you should execute the job in same hhmm time other wise it will not pick by tFTPGet.

You need to get the current date files into local directory in tFilelist ,there you can specify the order by in tFileList ,after the filelist use Run if and in the Run if condition specify global paramter of tFileList ((Integer)globalMap.get("tFileList_3_NB_FILE")) ==1

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 11:29:35 GMT

@manodwhb , really appreciate your time !

I tried what you did, but I think we are missing something. Full job here & will try to explain after :

What I want the job to do :

There is 2 files on my FTP, named, files will always be like : "TEXT_yyyyMMdd_hhmm"
TEXT_20180511_0349.xml
TEXT_20180511_0907.xml

I want to get the latest one (so TEXT_20180511_0349.xml) and then put it in my tFileInputXML >Tmap ... But for the moment, the tFTPGet doesnt return any files since the tJava get a files with the current date.

But how can the tJava compare the filename BEFORE the tftpGET " give " their names ? Thanks a lot for all.

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 11:36:52 GMT

@daez,

in java put this.

globalMap.put("TEXT_yyyyMMdd_hhmm", "*TEXT_"+ TalendDate.getDate("yyyyMMdd") + suffixe_fichiers);

then you will get the 2 files in tFTPGet and in tFTPlist order by modified date and order direction is DESC

in tFileinputxml,use (String)globalMap.get("tFileList_1_CURRENT_FILEPATH") global variable as file name.

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 12:22:00 GMT

Thanks @manodwhb , Now i get 2 files with my tFTPGet. But I dont see any order/sort possibilites with my settings on tFTPFileList. So it doesnt order at all and my tFileInputXML return a "null" for : (String)globalMap.get("tFileList_1_CURRENT_FILEPATH") .

Here is a screenshot :

Thanks !

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 12:29:14 GMT

@daez,you should use tFilelist not tFTPFileList

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 12:40:29 GMT

@daez,please find the below after the first file has processed you need to abort the job since this way.

instead of tJava place,you should use your logic to convert xml to csv.

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 13:25:19 GMT

@manodwhb , Oh my bad I thought it was FTPFileList. Its now working all fine, thanks a lot !

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 13:26:48 GMT

@daez,please accept solution and also provide Kudos

Re: Sort FTP files using dates

daez — Fri, 11 May 2018 13:30:33 GMT

@manodwhb, Yes i was about to do that, i just didnt see your last post before i reply, why should I add a tdie?

I added a tdie after my tFileOutput with the link " on component OK " but I have no "iterate " row like yours, I dont understand what it does.

Re: Sort FTP files using dates

manodwhb — Fri, 11 May 2018 13:39:36 GMT

@daez,if you do not want to iterate use tdie.otherwise do not use.