Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
dsharmaqv
Creator III
Creator III

Load data from specific sheet of excel

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!

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

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!

View solution in original post

3 Replies
Chanty4u
Champion III
Champion III

dsharmaqv
Creator III
Creator III
Author

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
Master II
Master II

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!

View solution in original post