Load data from specific sheet of excel

[dsharmaqv]

Hi All

I am trying to load from an excel file contains 2 sheets. IPV BSE Jan' 18 and IPV PRE Jan' 18.

How d I write the script which automatically picks the sheet name starts with IPV BSE* ?

Many thanks in advance!

[Chanty4u]

Hi

check here

Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[dsharmaqv]

Thanks for your reply chanty

Above link will load all the sheet .... where a I am looking to load the sheet when sheet name starts with IPV BSE*

[Frank_Hartmann · Accepted solution]

you could try like that:

FOR EACH file in FileList('C:\Users\admin\Desktop\Neuer Ordner (2)\*.xlsx');
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];


tables: 
SQLtables; 
DISCONNECT;


FOR i = 0 to NoOfRows('tables')-1
LET sheetName = (purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36)));


if left('$(sheetName)',7)='IPV BSE' then


Table:
Load
 *,
FileBaseName()as File,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From [$(file)] (ooxml, embedded labels, table is [$(sheetName)]);// where '$(sheetName)'='Tabelle1';
ENDIF
NEXT i
Next

hope this helps!