Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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