loop thru excel worksheet with specific name

[userid128223]

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.

[felipe_dutra]

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;

[jagan]

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.

[stevedark]

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