I am iterating files into DB
if there is an error in 1st excel file then it has to iterate the 2nd excel file.
But in my scenario if 1st excel file fails it is not taking 2nd excel file.
Could anyone help me to solve this issue?
@Lakshmi2411,there is an error mean which componet is failing?
for exampe as shown below my job was failling at excelinput ,use on Component error to tWarn/tLogRow.
The component is CVE(tfileinputExcel_1)
I am not getting any error in this componenet
if one excel has some issue.it has to go to next file and run the job.
@Lakshmi2411,if the excel file has issue with reading you the below way.
@Lakshmi2411,check this,should fix your issue.
@Lakshmi2411,can you check,have you checked Die on error in basic setting of tFileInputExcel? if yes do not check that option and still if the issue is there show the setting of tFileInputExcel.
@Lakshmi2411,the below is working fine,please check.
@Lakshmi2411,since you were using Excel files ,should have sheet name which the sheet name you specified in tFilinputExcel. it doest not matter to have data in that sheet. but sheet name shoould be presnt in all Excel Files.
Here i am getting file names from tfilelist compnent and i would like to take only sheet1 data
suppose i am having 3 excel files (may or maynot contain sheet1)
if sheet1 is there it will continue the process.
if sheet1 not there it gives error 'java.lang.RuntimeException: Special sheets not exist!'.
if this error displays for 1st excel file. the whole process is getting stopped.
But in my scenario: it has to the process should not get stopped and it should take next excel file.
@Lakshmi2411,i understand the requirement,but the sheet name should be presnt in all the excel files,otherwise it will stop the process and will not process the next file.
Is there any solution to compare sheet names and column headers and process?
My problem is here not only sheet name,column headers everthing should match.if it doesn't match it stops.
If you use have single data sheet in each excel file,you can specify the sheet position instead of sheet name.
1.how to specify sheet position and how to handle column names.
2.If column name doesn't match how can i iterate to next files.
3.How to retrieve file name, if column or sheet name doesn't match.
You can get around this with a bit of a hack. The issue here is that the tFileInputExcel does not handle missing sheets very well. It will always die. To get round this, you need a different approach. It isn't pretty, but it will work. Basically in your first subjob you need to create a list of filenames/paths and sheets available in all of your Excel files. Take a look at the screenshot below....
This will give you a distinct list of files and sheets in your tHashOutput which you can then use to filter only the files with the relevant sheets that can run (I'll leave that to you to do).
The important steps are shown in images below.....
In the tFileInputExcel set it as above. Ensure that "All Sheets" is ticked. Unfortunately (due to a bug on the version I am using ....and maybe all versions) the row limit field (left blank) does not work nicely with "All Sheets" so you have to return every row for every sheet. A bit inefficient, but not too bad so long as your sheets do not have too many rows.
I am simply passing the globalMap data for the current sheet (((String)globalMap.get("tFileInputExcel_2_CURRENT_SHEET"))) and the current file (((String)globalMap.get("tFileList_1_CURRENT_FILEPATH"))) to the output of the tMap. You will gets lots of the same values coming through, hence the tAggregateRow component....
Here we are just ensuring that duplicate rows are removed.
The next component is a tLogRow, just so that I could see that it worked as expected. After that it is a tHashOutput to collect the data.
What you will need to add is a tHashInput as the start of your new Subjob. A filter mechanism (possibly a tMap) to remove unwanted file/sheet combinations, a tFlowToIterate to connect to your tFileInputExcel and then you will need to get the appropriate globalMap values from the tFlowToIterate to populate your tFileInputExcel configuration (file and sheet). The rest of the job can be as you have it AFTER the tFileInputExcel.
Join us at the Community Lounge.
Talend named a Leader.
Kickstart your first data integration and ETL projects.
Watch the recorded webinar!
Read about OTTO's experiences with Big Data and Personalized Experiences
Pick up some tips and tricks with Context Variables
Learn how to publish your API Services to Talend Cloud