topic Re: loading all sheets data from excel in QlikView

loading all sheets data from excel

Sat, 28 Jun 2014 09:28:24 GMT

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

Re: loading all sheets data from excel

Anonymous — Sat, 28 Jun 2014 10:20:21 GMT

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))

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))

Re: loading all sheets data from excel

swuehl — Sat, 28 Jun 2014 10:38:31 GMT

Have a look at the solutions suggested here:

Load data from multiple worksheets in multiple Excel workbooks

Re: loading all sheets data from excel

maxgro — Sat, 28 Jun 2014 19:34:23 GMT

here a possible solution

how to count no.of sheets in execl file

Re: loading all sheets data from excel

Wed, 23 Jul 2014 01:45:42 GMT

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.

Re: loading all sheets data from excel

bimala0507 — Wed, 29 Apr 2015 02:36:41 GMT

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