Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
dsharmaqv
Contributor 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
Highlighted
Frank_Hartmann
Honored Contributor II

Re: Load data from specific sheet of excel

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
Highlighted
Chanty4u
Esteemed Contributor III

Re: Load data from specific sheet of excel

Highlighted
dsharmaqv
Contributor III

Re: Load data from specific sheet of excel

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*

Highlighted
Frank_Hartmann
Honored Contributor II

Re: Load data from specific sheet of excel

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