One Star

[resolved] Use sheet position in tFileInputExcel

Hello,
I want to import only the first sheet of an Excel file by using the position of the sheet (0 or 1). So I disable "All sheets" and enter the position in the column "Sheet (name or position)". This gives an error "The method addSheetName(String, Boolean) in the type ExcelReader is not applicable for the arguments (int, boolean)". Tried to use "0" but then the sheet with the name "0" is not found.
Am I doing something wrong or is this a bug? How can I import only the first sheet without using the name?
Thanks for any suggestions!
1 ACCEPTED SOLUTION

Accepted Solutions
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Problem solved, although it's a small bug in Open Studio. This is how I reproduced this behavior.
I've setup a new test set and added all parts step by step to see if I could reproduce this behavior. It occurred again when I set the property type of the tInputFileExcel component to "Repository", select the correct file from the repository and then edit the File Name/Input Stream to "context.fileToImport".
When trying to edit this property, Open Studio provides a pop-up with the options to either "Change to built-in property" or "Update repository connection". I've chosen to revert back to the built-in property and the changed the "Sheet (name or position)" to 0.
Now when I run the job the error about the int input is shown and the job won't run.
So, I removed the component form the job, add it again and set all settings (with "Sheet (name or position)" to 0) and it works. Just don't touch the "Property Type" and I'm fine...
Thanks for the fast replies and your help!
Regards,
Pierre
26 REPLIES
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
Please show us your component setting screenshot of tFileInputExcel. Did you add quotes around the position?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: [resolved] Use sheet position in tFileInputExcel

"0" is the string representation and not a 0 integer position
Check using 0 instead of "0"
Vaibhav
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Thanks for your reply. I've added the screen shot to my post. I tried adding quotes around the position but then I get the error as mentioned.
Regards,
Pierre
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
Quotes should not be added around sheet position. The sheet position should be 0 or 1. Is it OK with you when removing quote? Is sheet name(sheet 1, sheet2....) working well for you?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
When I remove the quotes (like in the screen shot) and i run the job I get an error "The method addSheetName(String, Boolean) in the type ExcelReader is not applicable for the arguments (int, boolean)".
If I use the sheet name it works, but the sheet names differ and also the number of sheets, So thats why I need to use the position to always import only the first sheet.
Regards,
Pierre
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Somebody an idea how to solve this or work around it? I really need to solve this.
Thanks for any suggestions!
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi pfacto,
Sorry for delay.
Could you tell us the build version you are using? We will make a testing on it.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Thanks for your reply. I'm using Open Studio for Data Integration version 5.5.0 build r117820-20140523-0610 on a Windows 8.1 machine. I had the same problem with version 5.4.1 build r111943-20131212-1133.
Regards,
Pierre
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
I have made a testing on Talend Open Studio for Data Integration 5.4.1 build r111943. Everything is OK with me.
Please have a look at my screenshots for details.

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

What setting did you use for the Sheet name or position on the tFileInputExcel? Ok, replied to quickly. See it.
If I use 0 for input, I get this error.
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
What's your whole workflow? only tfileinputexcel-->output? What's your JDK version?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Four Stars

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
What is the value of your context variable which is used in file name?
Whether the file is available at respective location?
Thanks
Vaibhav
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
If I setup a test like you did, it works fine. I'm using JDK 1.7.
I've added the complete workflow of the job with this weird behavior. The steps before opening the Excel sheet are as follows:
1. I'm loading the context from a file with the use of a subjob (LibContextreader). It will determine on which OS the job is running and load the context file from the correct location (development on Windows, production on Linux).
2. In the context there's a variable with the id of the file to load. The file names are in a MySQL database table and I'm getting the name from the table. The file itself isn't stored in the database, but on the file system. So I'm only getting the name of the file and set the complete path (which differs on development and production) in the context (context.fileToImport).
3. The tFileInputExcel is then opening the file and processes it. If I use the sheet name, it works fine.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Vaibhav,
Thanks for your reply. The context variable with the file name is correct and the file is present since everything works if I use the name of the sheet in tFileInputExcel in stead of 0. It's something with the method being called to open the sheet, not the file itself. The method doesn't accept integer input for whatever reason.
Regards,
Pierre
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Do you have any suggestions on how to solve this or work around it? Do you need more information?
Regards,
Pierre
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Problem solved, although it's a small bug in Open Studio. This is how I reproduced this behavior.
I've setup a new test set and added all parts step by step to see if I could reproduce this behavior. It occurred again when I set the property type of the tInputFileExcel component to "Repository", select the correct file from the repository and then edit the File Name/Input Stream to "context.fileToImport".
When trying to edit this property, Open Studio provides a pop-up with the options to either "Change to built-in property" or "Update repository connection". I've chosen to revert back to the built-in property and the changed the "Sheet (name or position)" to 0.
Now when I run the job the error about the int input is shown and the job won't run.
So, I removed the component form the job, add it again and set all settings (with "Sheet (name or position)" to 0) and it works. Just don't touch the "Property Type" and I'm fine...
Thanks for the fast replies and your help!
Regards,
Pierre
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi Pierre,
Would you mind opening a jira issue of DI project on Talend Bug Tracker, our developer will check if it is a bug. Many thanks.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Just tried to reproduce the error again to provide the steps for the Jira issue, but the error doesn't occur anymore?! So didn't add the issue yet, will do so if it occurs again and I can reproduce.
Regards,
Pierre
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi,
Thanks for your feedback. Feel free to let us know if there is any further help we can give.
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
The sheet position is not working in the component tfileinptexcel for the .xlsx files when we are in Generation Mode: less memory consumed for the large files(Even Mode). It is giving error when I given sheet position is 0.
for Memory consuming(User mode): The sheet position is working very well is 0 for the "Sheet1".
Could you guys please provide your ideas how to handle ..its very urgent for me to implement some job.
Note: I have to work on very large files so that I need to work on Generation Mode: less memory consumed for the large files(Even Mode) in Advanced settings.
Waiting for your answers and very big advance thanks to your answers..
Kind Regards,
Raghav K 
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi Raghav K ,
Could you please indicate the build version you are using? What's the volume of your large file? What does this error you are facing look like?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Thanks for your reply.
I have been working with build version: TOS_BD-20150508_1414-V5.6.2
OS: windows 7
The volume of large file which i am working is around 500MB. If i try to load this file in the Generation Mode: Memory- consuming(User Mode) i am getting the Out of memory exception. So that i am working with the excel files Advance Settings- Generation Mode: less memory consumed for the large file(Event Mode).
Note : Please confirm what is maximum size of the files does supports tfileinputexcel?
Generation Mode(User mode):  The sheet position is working as expected. In detail I want to read a excel file sheet name "Sheet1".
To read the sheet1 in Basic settings I am giving Sheet(name or position): 0(not in quotes). This case is working fine.
Generation Mode(Event Mode): I tried to read the Sheet1 with the Sheet(Name or position): 0 (no in quotes) which throwing the error message as below..
Error: The method addSheetName(String, Boolean) in the type ExcelReader is not applicable for the arguments (int, boolean)
As i know excel sheet name is string , So that i tried to read the sheet name like "0". In this case i am facing the error no matching sheets.
Note: I am facing this issue for the small and large files  as well when the advance settings are Generation Mode: less memory consumed for the large file(Event Mode)
Please find the attached input file and error snapshot. 
Great Thanks again for your help and waiting for your valuable suggestion.
Kind Regards,
Raghava K 
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sabrina,
Could you please provide the solution/help about this excel sheet position issue.

Kind Regards-
Raghav K
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi !
I got the same problem than Raghav and I don't found a solution for this ...
My Excel file have only one Sheet ... if I try to read this sheet with the name, I've got this error :
"Cannot format given Object as a Number" and if I add one sheet ... the error disappears. But it's a file from another person and I can't modify it.
So I tried to use the Sheet position but got the same problem thant Raghav ....
Any solutions ?
Best regards
Sangoval
Moderator

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sangoval,
We have replied to your another topic:https://www.talendforge.org/forum/viewtopic.php?id=51725
Could you please take a look at it?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
One Star

Re: [resolved] Use sheet position in tFileInputExcel

Hi Sangrovel-
If you want to go with the memory consuming mode you can use the sheet name/position option 0 (note: with out quotes)to reading the first excel sheet.
for the big files it is not applicable.
Kind Regards-
Raghav