topic Re: Load data from specific sheet of excel in QlikView

Load data from specific sheet of excel

dsharmaqv — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Load data from specific sheet of excel

Chanty4u — Wed, 14 Mar 2018 10:02:29 GMT

check here

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

Re: Load data from specific sheet of excel

dsharmaqv — Wed, 14 Mar 2018 10:06:29 GMT

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*

Re: Load data from specific sheet of excel

Frank_Hartmann — Wed, 14 Mar 2018 13:30:45 GMT

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!