Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Loading Data

Hi all,

How to load data if One excel sheet itself got 4 tables.

Janaki.

1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

Re: Loading Data

Hi,

This is the script in the second link

// Configure sheet names to this variable

SET vSheetNames = 'Jan Sales', 'Feb Sales','Mar Sales','Apr Sales','May Sales','Jun Sales','Jul Sales';

// Loop through the sheets and load the data

for each vSheetName in $(vSheetNames)

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT

OR

The second is no need to mention the Sheet name just remove the table is *** .

Note: This method only works for XLSX.

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, header is 2 lines);

Regards,

Jagan.

19 Replies
amit_saini
Honored Contributor III

Re: Loading Data

Hi Janaki,

Please share your excel sheet.

Thanks,
AS

ecolomer
Honored Contributor II

Re: Loading Data

You need to load each sheet

Not applicable

Re: Loading Data

Hi Amit

PFA2014-12-23_13-27-19.png

MVP & Luminary
MVP & Luminary

Re: Loading Data

Re: Loading Data

Hi,

You can try this expression and and specify the excel file name here.

For Each vFile in FileList('Data.xlsx')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRows('Sheets')-1

          Let vSheet = left(Peek('TABLE_NAME', i, 'Sheets'), len(Peek('TABLE_NAME', i, 'Sheets'))-1);

          LOAD '$(vSheet)' as [Tab Name], *

          From [$(vFile)]

          (ooxml, embedded labels, table is $(vSheet));

     Next;

Next;

Regards

Anand

Not applicable

Re: Loading Data

thank you Anand..

Not applicable

Re: Loading Data

Hi Jagan,

I can not open second link, it says access denied.

Thanks.

MVP & Luminary
MVP & Luminary

Re: Loading Data

Hi,

This is the script in the second link

// Configure sheet names to this variable

SET vSheetNames = 'Jan Sales', 'Feb Sales','Mar Sales','Apr Sales','May Sales','Jun Sales','Jul Sales';

// Loop through the sheets and load the data

for each vSheetName in $(vSheetNames)

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT

OR

The second is no need to mention the Sheet name just remove the table is *** .

Note: This method only works for XLSX.

Data:

LOAD *

     '$(vSheetName)' AS SheetName

FROM

[test.xlsx]

(ooxml, embedded labels, header is 2 lines);

Regards,

Jagan.

Not applicable

Re: Loading Data

Thanks jagan.