[resolved] tFileInputExcel - number of columns limited?

[Anonymous]

Hello there!
I am using metadata for tFileInputExcel, where I need to process xlsx file with many columns. Namely, around 90 of them.
Everything works perfectly if I work with smaller set of columns when specifying first and last column in metadata. 
But, when I set 7 as  start column and any number greater that 61 as end column in "Add Excel File" dialog, there is a following error which prevents me from proceeding:
" Last column parameter error. Bigger than schmea columns size" - literally, with this "schmea" typo.
Does this mean that this component has a limitation to number of columns that can be processed or this is a bug?
Is there a workaround for this - I need to process ALL the columns.
Thank you.
Cheers,

[Anonymous]

Hi,
I also faced same problem, here is work around for this
Metadata cannot read empty columns so it requires at least 1 value in last defined column. My advice is that you define your header name in your last column which you want to set in metadata it will work.

[Anonymous]

Thank you for the answer. The thing is the last column in my xlsx file is actually CL column and is not empty at all. There are no empty columns in between neither…

[Anonymous]

This is not even about the range of columns taken into account when processing. 
When I set First column to 60 and Last column to 62, it still reports the same message. 61 as value for Last column works though.

[Anonymous]

Yaa just tried same way & agreed it's not working with .xlsx but it is working with .xls
For .xlsx file follow below steps 
Step1 - Browse file & select "Less memory consumed for large excel(Event mode)" from drop-down
Step2- click on next and type 90 in your last column (still it will give you same error) than click on back and again click on next it will work

[Anonymous]

I've been trying to do it as you suggest, but there is something wrong - it doesn't work. After doing "back" and "next" the problem persists and I can't save my settings. Maybe I'm missing something. Here is a step-by-step explanation how I do it:

Create a metadata for excel where I select my .xlsx file and select sheets I want (just one for a start) in step 2 of the dialog. Then, in step 3 of the dialog, I  set 3 in header as a number of rows to be ignored, limit number of rows, set start column to 7 and leave end column blank, because setting 90 at that point gives an error. Then I proceed to step 4 where I can see the metadata schema and press finish.
In Basic Settings of my new tFileInputExcel component I choose "Repository" from Property Type drop down and then I choose metadata created previously in 1.
Then, in Basic settings of the component I click into Last column to set the value and the dialog "Edit parameter using repository" appears, so from there I choose "Update repository connection" option and then "Edit an Existing Excel File" dialog appears.
In Edit an Existing Excel File dialog I click next until I am again on "Step 3 of 4" where I enter 90 as end column, and immediately have the same error shown, so I click Back button (without deleting 90), then click Next button - and there is the same error as always "Last column parameter error. Bigger than schmea columns size".
I am out of ideas.
Thank you for your time!

[Anonymous]

Start from new file in Metadata and follow below screenshots
Step1 - Browse your file and select generation mode " Less memory consumed for large excel(Event mode)" as highlighted in screenshot. Click on Next

Step2 - On second dialog box put your start column and end column value as shown in below sceenshot and i can see same error.

Step 3 - Click on Back and again click on Next. you can see your last column in result view as shown in screenchot

Note - I tried in version open source 5.4.1 & 5.5.1

[Anonymous]

Thank you very much for detailed instructions - still, it doesn't work for me…Will let you know if I figure out something.

[Anonymous]

Hi! After restarting talend (a couple of times) I got my metadata definition working by editing one of existing metadata definitions that previously had reported errors. Still, when I tried to add a new definition from scratch, using the same .xlsx file and rules, it reported an error. Could be some memory refreshing issue.
Good thing is that I got my job working.
Have a nice weekend!

[rouxrei]

I know this is an old post, but I had a very similar issue and I was not satisfied with your final 'resolution'.

In the mean time I discovered something that actually solved the issue for me without any of the mentioned workaround hacks, so I wanted to share this with the community for other people landing on this post.

In step 2 of the metadata wizard, make sure that in the bottom right with "Please select schema" you have selected the correct excel sheet.

My first sheet has a lot fewer columns than the sheet I actually wanted to use as input.

So by having the first sheet selected, the wizards does not allow you to select the correct amount of columns in step 3.