Sort FTP files using dates

Six Stars

Sort FTP files using dates

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 !

 

 

Tags (2)

Accepted Solutions
Thirteen Stars

Re: Sort FTP files using dates

@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.

Manohar B
Tags (1)

All Replies
Thirteen Stars

Re: Sort FTP files using dates

@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

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

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 ! 

Tags (1)
Thirteen Stars

Re: Sort FTP files using dates

@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.

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

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

 

Tags (1)
Thirteen Stars

Re: Sort FTP files using dates

@daez,which component is failling?

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

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.

Thirteen Stars

Re: Sort FTP files using dates

@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

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

@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 : Sans titre.png

 

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.

 

Tags (1)
Thirteen Stars

Re: Sort FTP files using dates

@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.

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

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 : 

Sans titre.png

Thanks !

Tags (1)
Thirteen Stars

Re: Sort FTP files using dates

@daez,you should use tFilelist not tFTPFileList

Manohar B
Thirteen Stars

Re: Sort FTP files using dates

@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.

 

Untitled.png

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

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

 

 

 

Tags (1)
Thirteen Stars

Re: Sort FTP files using dates

@daez,please accept solution and also provide Kudos

Manohar B
Tags (1)
Six Stars

Re: Sort FTP files using dates

@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.


Thirteen Stars

Re: Sort FTP files using dates

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

Manohar B
Tags (1)