I want to fetch files using liststfp based on date range.

[#1]

The scenario is like this:I have a control table in MySql from where I am getting a date(Last_file_load) from Last_file_load to current date I want to fetch all the files . The naming convention for the file is like ABC_20170102_01.csv (ABC_YYYYMMDD_Sequence_number.csv).So my desired output from ListSFTP should be ABC_20170102_01.csv,ABC_20170103_01.csv,ABC_20170104_01.csv.

[#2 · Accepted solution]

Hi @Aman Jain,

Can you check "State management: section of https://nifi.apache.org/docs/nifi-docs/components/org.apache.nifi.processors.standard.ListSFTP/" ? Is this what you are looking for?

[#3]

@Arti Wadhwani :Hi Arti , I want to fetch files from a remote server based on date range i.e the range which is specified in the file name .For example , file ABC_20170101_01.csv is successfully loaded on 01-01-2017 so there will be an entry in MySQL table with load date as 2017-01-01.So , when the flow runs next time on 01-04-2017,it will fetch last load date from my sql as 2017-01-01 and start fetching following files from remote server ABC_20170102_01.csv, ABC_20170103_01.csv, ABC_20170104_01.csv.

[#4]

@Aman Jain

The ListSFTP processor has a "File Filter" property that allow you to use a java regular expression to specify the filename pattern you want the processor to look for on the target SFTP server. It does not give you the capability to pull some value from MySQL to use here which is what it sounds like you want to do.

That being said.... keep in mind that the ListSFTP processor does actually fetch and data, it only produces a zero byte NiFi FlowFile for each File it lists. It is the responsibility of the FetchSFTP processor to actually retrieve the data content and add it to the NiFi FlowFile. Perhaps you can have NiFi always list all the files for the target SFTP server and filter out hose 0 byte FlowFiles you do not want before doing the FetchSFTP on each run?

Having one flow that retrieves your load date from mysql and writes it to a distributed Cache Service in NiFi. then use Then have another flow to list files and filter them based on the current value loaded in the Cache service. The filtered FlowFiles could then be sent to FetchSFTP processor while other are just dropped/auto-terminated.

Matt

[#5]

Agree with @MattWho .

You can use the nifi expression language to choose the certain files from your source.

Based on the date or file name you can filter out the files using FetchSFTP processor.

i can see in the latest version of nifi expression language is false in ListSFTP processor. to handle your case you have to do it in two steps ListSFTP ==> FetchSFTP (If you want to put dynamic date filters in your source directories).

e.g. you can mention the below in Remote file property of FetchSFTP

0000000001_${now():toNumber():minus(86400000):format('yyyyMMdd')}235959_filename

this will gives us output as 0000000001_20200112235959_filename.

Regards

Nitin