Load data from multiple worksheets in multiple Excel workbooks

[#1]

Have 50 workbooks - one for each customer - stored in "CustomerData" folder.  Workbooks can have 1 to 50 worksheets (one for each day with customer data.)  In each workbook, worksheets are named "1", "2", "3", etc. -- e.g. the first worksheet in workbook "A" is named "1" as is the first worksheet in workbook "B" -- worksheet names are not unique across workbooks.  For all worksheets, worksheet columns are identical - some have more rows (more records.)

With one load statement, I can load all of the worksheets named "1" from all the workbooks in "CustomerData."  What I want to do is load all the data from each worksheet in each workbook with one load statement if possible.  If needed, each worksheet contains a value for "CustomerName" and a value for "Date" which when combined are unique to that worksheet.

Any ideas on how to write the load statement or is this not possible?

Thanks

Bill

[stevedark]

Working example uploaded to http://community.qlik.com/message/258952 .

Regards,

Steve

[rwunderlich]

Note also that you can load sheets by position, rather than name.

"...table is @n"

where n is 1,2, etc.

-Rob

[stevedark]

Hi Rob - thanks for that tip - I was not aware of that syntax.  Do you know if there a simple way of getting the number of tabs (without the OLEDB connection), as my document could be simplified if so.

Cheers,
Steve

[rwunderlich]

I don't know of any other way to get the sheet count. But what I seen done is to asume a max number of sheets like 50, SET ErrorMode=0 and then just try from @1 to @50. Crude, but simple.

-Rob

[r3iuk]

Should this method also work for .xlsx files as we seem to be having difficulties with files of this format?

[r3iuk]

I developed the following workaround so that I can use a numeric sequence with .xlsx spreadsheets:

FOR vMonthNum = 1 to 12

   Let vSheet = pick( vMonthNum, 'For Jan','For Feb','For Mar','For Apr','For May',

   'For Jun','For Jul','For Aug','For Sep','For Oct','For Nov','For Dec');

   Data:
   LOAD

     <whatever>

   FROM [$(vSourceFile)]
   (ooxml, embedded labels, table is [$(vSheet)]);
NEXT

[stevedark]

Hi Mike,

That's a good use of pick there to get the sheet names.  I like my approach of having the sheets listed on a meta data sheet in the spreadsheet (as per my previous response) as new sheets can be added without amending QlikView code.  This approach works fine as long as the sheet names are not going to change - and I doubt anyone is going to add a new month name any time soon!

Cheers,

Steve