Count number of Excel sheets before load script

[Lionel]

Hi Everyone,

This is now my first post and I am looking for your help.

I have a load script that loads data from excel, sheet(1).

I noticed recently that some time the volume of the data is bigger than 1048 rows, and the extraction system compiles the data on a second sheet, sometime even a 3rd.

Basically, I would load to know how to include a sheet counter associated with an If function, this is what i would expect:

if count  'number of sheet' < 2

load *

FROM [lib://Excel.xlsx]
(ooxml, no labels, table is [Sheet(1)]);

else

Load *

FROM [lib://Excel.xlsx]
(ooxml, no labels, table is [Sheet(1)]);

Load *

FROM [lib://Excel.xlsx]
(ooxml, no labels, table is [Sheet(2)]);

...

Load *

FROM [lib://Excel.xlsx]
(ooxml, no labels, table is [Sheet(n)]);

Would you have any idea how to achieve this ?

Thanks in advance for your help

[marcus_sommer]

It's not possible in this way. AFAIK the only way to get these information directly is to use an odbc-load on the excel: Data-structure-in-excel-files-ODBC-multiple-sheets-load.

An alternatively to this might be to use ERRORMODE like suggested here from Gysbert: Load-data-from-multiple-sheets-of-excel-file-dynamic-sheet-count.

If each sheet has a determined number of records and each file contains the same kind of data you might be also able to deduce the number of sheets from the: filesize('Path\Filename.xlsx') of the files - and in this case you could use the mentioned if-loop.

- Marcus

[Lionel]

Thanks a lot Marcus. Your help and effort are much appreciated.

I'll have a look to the posts, but might come back 🙂

Thanks

[JordyWegman]

Hi Lionel,

I would do the following, although it's a little bit cumbersome if you have a lot of data:

set errormode = 0;

for i = 1 to 255

	Table:
	LOAD 
           [JustOneField]
	FROM
	TEST.xlsx
	(ooxml, embedded labels, table is Sheet$(i));
	
next

set errormode = 1;

let vTableCount = Ceil(NoOfRows('Table') / 1048);

drop table Table;

For i = 1 to $(vTableCount)
	
        FinalTable:
	LOAD Field1, 
	     Field2, 
	     Field3
	FROM
	TEST.xlsx
	(ooxml, embedded labels, table is Sheet$(i));
	
next

This will first go through all your sheets, divide the amount of rows by 1048 (not 1024?) and ceil this number. Now you know the amount of sheets because the 1048 is always fixed. Then get another For loop where you will get all your data from.

The reason for not doing to right away is because of the errormode, when something isn't right your data load will fail, which is good then.

Jordy

Climber