loading all sheets data from excel

[#1]

how to load all sheets data with 'single load stamt'

[Anonymous]

Hi,

Its depend on the naming convention of sheets. For example, if you have yearly sheets than use FOR loop function. See below,

For i = 2010 to 2014

Load

     .....

FROM <Excel Name>

(ooxml, embedded labels, table is $(i))

Next

If you have monthly sheets or mix naming than use FOR Each loop function. See below,

For Each i in 'Jan','Feb','Mar' etc

Load

     .....

FROM <Excel Name>

(ooxml, embedded labels, table is $(i))

Next

[swuehl]

Have a look at the solutions suggested here:

Load data from multiple worksheets in multiple Excel workbooks

[maxgro]

here a possible solution

how to count no.of sheets in execl file

[#5 · Accepted solution]

Hi there - you will need to put a nested loop construct in place, firstly for each workbook in the folder and then for each sheet in the workbook.

For each workbook you will require code similar to this:

for each vFileName in FileList('.\*.xls')

And then within that loop, you will need to loop for each sheet.  It is possible to get a list of all sheets in an Excel spreadsheet by connecting to it with an ODBC connection and calling the SQLTables function.  But as you know the sheets are sequential from 1 that seems overboard.  I would probably put an extra sheet 0 on each workbook that contained a cell with the number of sheets.  The code would then be a bit like this:

Temp_Sheets:

LOAD

     Sheets

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is [0$]);

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD

          [... Field List ...]

     FROM $(vFileName)

     (biff, embedded labels, header is 1 lines, table is [$(vSheetNo)$])

next

Obviously this does rely on the sheets being sequential from 1, otherwise the load will fall over.  In this case explore the SQLTables route.

[bimala0507]

Hi:

While I was trying to do this example, I am facing the error saying ' CAN NOT READ BIFF FILE'. In my case it is a Single workbook with different sheets in it and I want to load all the sheets at one shot. My excel file is in .xlsx format.

Could anyone please guide me, how to go about it. Appreciate a quick response.

Kind Regards,

Bimala