Excel Sheet Name

More
1 year 7 months ago #21623 by bruce.gibbins
AETLP v6.3.8.3 x32

Hi

We noticed that in AETLP when reading an Excel Workbook either as a READER or via the Package Excel Operation Action we can g et malformed sheetnames.

As per the screenshot you will see that we have opened an Excel file, in this case a .XLS file and the SheetName is 'ActVsStratWgt_InvestmentManager' which is 31 characters and matches the Excel limit

However, AETL shows the SheetNames differently. It shows

ActVsStratWgt_InvestmentManagerComparison
ActVsStratWgt_InvestmentManagerComparison:

In fact it shows two sheets when there is only 1 and the length is greater than 31 characters and notice that the second name shows a colon followed by a non-printable character.

In an attempt to isolate if the source excel file actually has these sheet names I saved it as an XML file and there is only a single sheet with the expected name.

Therefore it is strange as we are getting a sheetname that exceeds the excel sheetname limitation and the I can't find any evidence of the word 'Comparison' and why it would be getting added to the SheetName.

Even opening the VBA content for the excel sheet shows it as 'ActVsStratWgt_InvestmentManager'

So the question is why are there 2 sheets shown and both with a malformed name and also a name that doesn't seem to equate to what Excel itself thinks it should be. Is there perhaps a fault with the Excel reader component?

Thanks in advance



Attachments:

Please Log in or Create an account to join the conversation.

More
1 year 7 months ago #21624 by admin
Replied by admin on topic Excel Sheet Name
Hi Bruce

The excel reader shows to kind of objects sheets and ranges

the second object in your case is a range

Sheetname:Rangename

www.contextures.com/xlnames01.html

Mike
ETL Architect

Please Log in or Create an account to join the conversation.

More
1 year 7 months ago #21625 by bruce.gibbins
Replied by bruce.gibbins on topic Excel Sheet Name
Thanks again, Mike.

Appreciate letting me know about Named Ranges. Question.... Is there anyway we can reference a sheet by Index rather than name?

In this case there is actually there is a named range called "Print_Titles" that doesn't show up in AETL.

Also, just confirming that Excel is Visually 'visually' truncating the SheetName to 31 characters which seems to be a limit enforced by Microsoft UI. Which, in this case coincidently lands on a word boundary and it appears correct. AETL is showing the whole SheetName when greater than 31 characters.

Internally, the SheetName is actually being stored in full as proven by a small Python script I wrote to dump the sheet names.

Attachments:

Please Log in or Create an account to join the conversation.

More
1 year 7 months ago #21626 by admin
Replied by admin on topic Excel Sheet Name
Is there anyway we can reference a sheet by Index rather than name?

That might be possible but I am not sure what is the best way to implement the interface

Mike
ETL Architect

Please Log in or Create an account to join the conversation.

More
1 year 7 months ago #21627 by bruce.gibbins
Replied by bruce.gibbins on topic Excel Sheet Name
Hi Mike

I am not familiar enough of what limitations may exist. But if you already read the workbook metadata then maybe you can retrieve the index count which would give you the MAX and then provide an OPTION choice that is either ByIndex or ByName and then if ByIndex present/unhide an integer input or spinwheel for the user to chose. If ByName is chosen then maybe default the index to an unusable value like -99 or something and hide it. That way you can know internally what should happen with the choice.

I guess the challenge is that this does not handle Named Ranges. But if you chose the ByIndex option then the assumption would be that you are not attempting to access a NamedRange.

Just my 10cents worth

cheers

Please Log in or Create an account to join the conversation.