topic Re: loop thru excel worksheet with specific name in QlikView

loop thru excel worksheet with specific name

userid128223 — Sat, 15 Mar 2014 02:14:02 GMT

Hi Everyone

I am finding lot of threads that shows how to loop thru all worksheets in a workbook. However If i have a workbook with 15 sheets but I only want to loop thru all the sheets whose name end in *mod. how can I do that.

please share example.

Re: loop thru excel worksheet with specific name

felipe_dutra — Sat, 15 Mar 2014 06:15:29 GMT

Try this:

Set vPath = C:\Test;

FOR Each File in FileList('$(vPath)\*.xls')

ODBC CONNECT32 TO [Excel Files;DBQ=$(File)];

TabTmp:

SQLTABLES;

Tab:

LOAD

RowNo() as Line,

TABLE_CAT as X,

If(Right(TABLE_CAT, 3) like 'xls', TABLE_NAME, If(Right(TABLE_NAME, 1) like '$', Left(TABLE_NAME, Len(TABLE_NAME)-1), TABLE_NAME)) as Y,

If(Right(TABLE_CAT, 3) like 'xls', 'biff', 'ooxml') as Z

Resident TabTmp

Where Right(PurgeChar(TABLE_NAME, '$'), 3) like 'mod';

DROP Table TabTmp;

NEXT File

For vLine = 0 to Peek('Line', -1, 'Tab')-1 step 1

vX = Peek('X', $(vLine), 'Tab');

vY = Peek('Y', $(vLine), 'Tab');

vZ = '('&Peek('Z', $(vLine), 'Tab')&', embedded labels, table is $(vY))';

[$(vY)]:

LOAD *

FROM

[$(vX)]

$(vZ);

NEXT vLine

DROP Table Tab;

Re: loop thru excel worksheet with specific name

jagan — Sat, 15 Mar 2014 07:02:37 GMT

Hi,

Check this

How to read the data from multiple sheets of an excel file?

How to load excel excluding one sheet?

Excel with multiple sheets

Loading from multiple Excel files and multiple sheets

Regards,

Jagan.

Re: loop thru excel worksheet with specific name

stevedark — Sat, 15 Mar 2014 07:22:42 GMT

As you say there are many examples of looping through Excel sheets. You just need to find one that works for you and add two lines, to skip non 'mod' ones.

if lower(right(vTabName)) = 'mod' then

[... load sheet ...]

end if

Personally when I am loading from multiple tabs I prefer to add a metadata tab to the start of the spreadsheet listing the names of all tabs that should be loaded. This can then be enumerated around with a PEEK statement. This means if someone adds a random sheet it doesn't get pulled in by mistake.

A step I would add to Felipe's code is after retrieving File check for the ~ character. This is a system file created when a user has a spreadsheet open, and this can be picked up by a *.xls. Simply add this check after retrieving each file name:

if index(FILE, '~') = 0 then

This will then skip any files that are system generated.

Hope that helps,

Steve