One Star

tFileInputExcel - can't make selection of worksheet to work

I have a spreadsheet with reference data in it.
Each sheet is to be loaded into a different reference table and so has different formats. I then have to load each sheet in turn via a different process because loading other sheets will cause format conflicts.
There is also a sheet at the front that describes how to use the spreadsheet.
But when I use tFileInputExcel, it almost always reads from only the first sheet. Whatever I do and whatever settings I change, I cannot get it to read from the second sheet or the third etc. This was until last night, when I managed to get it to work by saving the file as Excel 2003 (xls) instead of Excel 2010 (xlsx). I thought perhaps Talend could only read 2007 files and not 2010, and Excel 2010 doesn't allow you to save as 2007. However, this morning it isn't working again, and I'm using the same components.
I have the "Sheet (name or position)" set to the exact name of the worksheet from which I wish to extract. Yet it ignores this completely.
I've read many articles on here and elsewhere on how to use tFileInputExcel, and most only read the first sheet, while others iterate through sheets reading them all. But I only want to read one sheet.
A few articles use components from Exchange that can read one sheet at a time, and I got this to work eventually (by saving as Excel 2003) but it didn't stop at the end of the spreadsheet, tried to load a blank record with null values, and so also failed.
I'm sure it is me getting the usage wrong on the component, but why is it ignoring my sheet selection?
I may have to switch to CSV files I think. But CSV files are not fantastically business friendly.
3 REPLIES
Moderator

Re: tFileInputExcel - can't make selection of worksheet to work

Hi,
How did you set the schema in tFileInputExcel when you want iteration between these sheets one by one? Which custom component are you using?
Is there any screenshot of your job design.
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: tFileInputExcel - can't make selection of worksheet to work

The schema is set to the schema of worksheet #2. The schemas of worksheet #1 and #3 are different but irrelevant.
When I read the worksheet, it kept returning me the first worksheet, but I clearly specified worksheet 2.
I'm not using custom components any more. I tried to, but they failed because they didn't stop reading the excel sheet at the first blank row.
Update - This is now working for Excel 2003 files, and I've not obviously changed anything. The Data previewer returns the first worksheet rather than the second, but the component when run does return the second worksheet - I guess that's a bug in the data previewer. However the component doesn't seem to read Excel 2010 files, just Excel 2007, and if you've got 2010 you can't save as 2007, just 2003 or earlier...
I'll keep investigating ways to use Excel 2010/2007 as I cannot guarantee the version of office that will be used.
Unfortunately I cannot post screenshots (a) because I'm a new user, and (b) because it will reveal confidential information.
One Star

Re: tFileInputExcel - can't make selection of worksheet to work

I found that the data previewer does not work - it returns the first tab whatever option is selected, but the spreadsheet is read correctly by the component. I now have this working for Excel 2010 files, and my solution loads as expected.