how to get data max of date and max of date -1 from folder using tfilelist

[RAJ6]

Hi Talend Folks,

• I have files like XYZ_17Jun2017.xlsx, XYZ_18Jun2017.xlsx, XYZ_19Jun2017.xlsx, XYZ_20Jun2017.xlsx.

for example:

• I need to get max of date(XYZ_20Jun2017.xlsx) file from folder using tfilelist_1 component and max of date - 1 (XYZ_19Jun2017.xlsx) for tfilelist_2 component.
• I need to create job below like screenshots.

• It is a routine process
• Next day automatically i need to get max of date(XYZ_21Jun2017.xlsx) file from folder using tfilelist_1 component and max of date - 1 (XYZ_20Jun2017.xlsx) for tfilelist_2 component.

[Anonymous]

Firstly, whenever possible you should use ISO 8601 format dates (YYYY-MM-DD) especially for filenames, as they can be sorted alphabetically which will also be chronologically, making things a lot easier in most situations. With the current format of your filenames, it would be ridiculously messy finding the most recent one(s) based purely on the name.

However, in this case we can presumably use the file modified date to get the two most recent files, as follows:

The key is to have the files in the tFileList sorted by modified date, and descending; then it's just a case of recording the filenames for first two files.

You may want to use "abs_path" instead of "basename" which I've used in my example, and obviously you should remove the System.out.println lines, which are just there to demonstrate that it's working.

[RAJ6]

Thank you for your reply

when I ran your query. i am getting error message. 

Error Line: 880
Detail Message: The method containskey(String) is undefined for the type Map<String,Object>

if (row6.basename !=null)
{
if(!globalMap.containskey("XYZ_latest"))
{
globalMap.put("XYZ_latest", row6.basename);
System.out.println("Latest:"+row6.basename);
}
else if(!globalMap.containskey("XYZ_previous"))
{
globalMap.put("XYZ_previous", row6.basename);
System.out.println("Previous:"+row6.basename);
}
}

[Anonymous]

It's actually Java code, and Java is a case sensitive language. The method is "containsKey" (with a capital "K").

I really should have included the code in my post, rather than just a screen grab, so you could copy it.

When in a code editor, or any text field which accepts expressions, you can press Ctrl + Space to bring up auto-complete suggestions, which also provide some basic documentation for functions like this.

[RAJ6]

• Thank you.coding is working fine
• I got the output date values like 20-7-2017 (max date) and 19-7-2017 (max date of -1)
• Based on that values i need to get input data form sources folder

[Anonymous · Accepted solution]

If you change "row1.basename" to "row1.abs_path" in my example to keep things simple, then you can load the Excel files using two tFileInputExcel components, with the "File name/Stream" set to:

((String)globalMap.get("filename_latest"))  or ((String)globalMap.get("filename_previous"))

These components would be in another sub-job, which would be connected to the first (which gets the most recent two filenames) using an "On Subjob Ok" trigger.

In your original example, you'd not need the two tFileList components, and the tFileInputExcel components would use the above as filenames, but otherwise it should just be the same.

[RAJ6]

As you said i have tried that flow but i can't join tFileinputExcel_5 to tmap_4

As you said , can you please make screenshot for me

[Anonymous]

If you just remove the OnSubjobOk going to tFileInputExcel_5, then you'll be able to join it to tMap_4.

[RAJ6]

Hi ciw1973,

• I have tried what you said again, but i didn't get proper output.
• I have enclose my screenshots and error message.
• Please give me the solution.

Error Message:

Starting job routine at 11:45 19/10/2017.

[statistics] connecting to socket on port 3854
[statistics] connected
Latest: PED_20Jun2017.xlsx
Previous: PED_19Jun2017.xlsx
Exception in component tFileInputExcel_6 (routine)
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file input stream from file: 'PED_20Jun2017.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:155)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:142)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:117)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:257)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:90)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at local_project.routine_0_1.routine.tFileInputExcel_6Process(routine.java:9799)
at local_project.routine_0_1.routine.tFileList_3Process(routine.java:16760)
at local_project.routine_0_1.routine.runJobInTOS(routine.java:17043)
at local_project.routine_0_1.routine.main(routine.java:16863)
Caused by: java.io.FileNotFoundException: PED_20Jun2017.xlsx (The system cannot find the file specified)
[FATAL]: local_project.routine_0_1.routine - tFileInputExcel_6 Can't open the specified file input stream from file: 'PED_20Jun2017.xlsx'
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file input stream from file: 'PED_20Jun2017.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:155)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:142)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:117)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:257)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:90)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at local_project.routine_0_1.routine.tFileInputExcel_6Process(routine.java:9799)
at local_project.routine_0_1.routine.tFileList_3Process(routine.java:16760)
at local_project.routine_0_1.routine.runJobInTOS(routine.java:17043)
at java.io.FileInputStream.open0(Native Method)
at java.io.FileInputStream.open(Unknown Source)
at java.io.FileInputStream.<init>(Unknown Source)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:152)
... 10 more
at local_project.routine_0_1.routine.main(routine.java:16863)
Caused by: java.io.FileNotFoundException: PED_20Jun2017.xlsx (The system cannot find the file specified)
at java.io.FileInputStream.open0(Native Method)
at java.io.FileInputStream.open(Unknown Source)
at java.io.FileInputStream.<init>(Unknown Source)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:152)
... 10 more
[statistics] disconnected
Job routine ended at 11:45 19/10/2017. [exit code=1]

[RAJ6]

Hi ciw1973,

I have tried what you said again but i didn't get solution

screenshots 2:

Error Message:

Starting job routine at 15:14 19/10/2017.

[statistics] connecting to socket on port 3642
[statistics] connected
Latest: PED_20Jun2017.xlsx
Previous: PED_19Jun2017.xlsx
Exception in component tFileInputExcel_5 (routine)
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file input stream from file: 'PED_19Jun2017.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:155)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:142)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:117)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:257)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:90)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at local_project.routine_0_1.routine.tFileInputExcel_5Process(routine.java:9331)
at local_project.routine_0_1.routine.tFileInputExcel_6Process(routine.java:33027)
at local_project.routine_0_1.routine.tFileList_3Process(routine.java:40952)
at local_project.routine_0_1.routine.runJobInTOS(routine.java:41235)
at local_project.routine_0_1.routine.main(routine.java:41055)
[FATAL]: local_project.routine_0_1.routine - tFileInputExcel_5 Can't open the specified file input stream from file: 'PED_19Jun2017.xlsx'
org.apache.poi.openxml4j.exceptions.InvalidOperationException: Can't open the specified file input stream from file: 'PED_19Jun2017.xlsx'
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:155)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:142)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:117)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:257)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:187)
at org.apache.poi.POIXMLDocument.openPackage(POIXMLDocument.java:90)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at local_project.routine_0_1.routine.tFileInputExcel_5Process(routine.java:9331)
at local_project.routine_0_1.routine.tFileInputExcel_6Process(routine.java:33027)
at local_project.routine_0_1.routine.tFileList_3Process(routine.java:40952)
Caused by: java.io.FileNotFoundException: PED_19Jun2017.xlsx (The system cannot find the file specified)
at java.io.FileInputStream.open0(Native Method)
at java.io.FileInputStream.open(Unknown Source)
at java.io.FileInputStream.<init>(Unknown Source)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:152)
... 11 more
at local_project.routine_0_1.routine.runJobInTOS(routine.java:41235)
at local_project.routine_0_1.routine.main(routine.java:41055)
Caused by: java.io.FileNotFoundException: PED_19Jun2017.xlsx (The system cannot find the file specified)
at java.io.FileInputStream.open0(Native Method)
at java.io.FileInputStream.open(Unknown Source)
at java.io.FileInputStream.<init>(Unknown Source)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:152)
... 11 more
[statistics] disconnected
Job routine ended at 15:14 19/10/2017. [exit code=1]