topic Re: [resolved] Use sheet position in tFileInputExcel in Talend Studio

[resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 09:21:38 GMT

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!

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 09:36:13 GMT

Hi,
Please show us your component setting screenshot of tFileInputExcel. Did you add quotes around the position?
Best regards
Sabrina

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 09:46:42 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 10:19:52 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 10:33:16 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Mon, 23 Jun 2014 11:40:39 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 08:13:42 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 08:16:55 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 08:29:40 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 08:57:31 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 09:01:05 GMT

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.

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 09:12:18 GMT

Hi,
What's your whole workflow? only tfileinputexcel-->output? What's your JDK version?
Best regards
Sabrina

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 09:12:50 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 09:27:21 GMT

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.

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 09:40:10 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 14:43:51 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Tue, 24 Jun 2014 17:52:13 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Wed, 25 Jun 2014 04:51:44 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Wed, 25 Jun 2014 08:34:28 GMT

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

Re: [resolved] Use sheet position in tFileInputExcel

Anonymous — Wed, 25 Jun 2014 08:36:29 GMT

Hi,
Thanks for your feedback. Feel free to let us know if there is any further help we can give.
Best regards
Sabrina