Four Stars

tfileinputexcel with Event mode is not working with Limit clause.

Hi there,

 

I have created a very simple job to reproduce the scenario. 

tfileinput_excel --> tlogrow

1.) tfileinputexcel: The input file has 1000 rows. Trying to read xlsx file with Event mode to reduce less memory. Tried to limit the row from the file to 50. 

When I try to run the job, it is not limiting the output for 50 rows. Instead it runs for all rows.

 

Any suggestions.

 

This is a very simple job. 

 

The original Job (which I am working on)  tries to read input file (multiple spreadsheets with User mode.) in multiple jobs. The file is quite about 5M.  But it throws memory error because of the file size.

I thought to use the Event mode, but Event mode is not working with Limit clause as I described in my example job above.

17 REPLIES
Twelve Stars TRF
Twelve Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi,

 

What do you call "Event mode"?

Can you share your job design?


TRF
Seven Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

I am assuming you are referring to the generation mode when you talk about user mode or event mode. Irrespective of what this mode is set to, the limit clause should work.

Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

No. The limit cause is not working when using the event mode.
Seven Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

well like @TRF mentioned, we need the job design to identify what's the actual issue..

Ten Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Which version are you using? I tested this scenario in 6.3, and it works as expected, but there is an old (2014) bug tracker entry for this issue.
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi ,

I am using 5.6.1.
Ten Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

I believe that version predates the fix for this issue.
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi,

 

Please find the attached very simple job.

Version: 5.6.1
Build id: V5.6.1_20141207_1530

 

I have also found one more issue:

  • If in the Excel component, in the "Sheet List": If you specify the Sheet name, and run the job in Event mode , it works fine. But if you specify the sheet position (i.e. 0) , it will fail.
  • If you run the above scenarios in User mode, no issues at all.

 

 

 

Community Manager

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi sanketmistry
Can you download and test the latest versions 6.3.1 or 6.4.0? I have made some testing on v6.4.0 and it works fine.
BTW: v5.6.1 is a very old version.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

I can. But, may I know which one of them is the released version. From
Talend site, I cannot know the released version.
It simply says, download (but not mentioning the version).
The reason I am using 5.6.1 is, the Organization do not want to upgrade
because we have to upgrade to Java 1.8 as well.
There should be some strong reason if we have to move to latest version.
Can you please put some insight into it.

Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi Shong,

 

I have installed Talend 6.4.0 and tried to run the job in event mode.

Now it works only if you provide the Sheet name in the Sheet list. But if you provide the sheet position (i.e. 0) , it is failing.

My job needs to run the excel based on sheet position.

 

 

Community Manager

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hello
I have a made testing on version 5.6.1, it works! I read an excel file with 700000 rows, if I select user mode, it throws OutOfMemory exception, if I select Event model, the job runs fine in 23s.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi Shong,

It works if you read all line.

If you put the limit only 10 rows. It will not.

It is clearly not working in my environment with the job I have provided.

Also, can you please check by trying to put 0 in the sheet list under the Basic settings of tfileinput excel.

 

It will throw the error.

Community Manager

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hello
I got the same compilation error if i input sheet position 0 with event model, it should be a bug, I have open a jira issue on our bugtracker.
https://jira.talendforge.org/browse/TDI-39079
Please watch it and you will get email notification once it has update.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi Shong,

 

What I have seen that you have raised the bug i 6.4.0 beta.

 

Now, how can I know which one is the stable release and from where I can download it. It is bit confusing to find from Talend site.

Can you please help in that case.

 

Community Manager

Re: tfileinputexcel with Event mode is not working with Limit clause.

Hi snaketmistry,
I am using version 6.4.0.20170510_1410, it is stable release which you can download it from Talend website, I got the error on this version, but I don't see this version in the Found in release list when i raised a bug, so i select 6.4.0 beta.
I see this issue has been assigned to developer, he is starting to work on it.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
Four Stars

Re: tfileinputexcel with Event mode is not working with Limit clause.

Thanks Shong for the clarification.