Load Multiple Excel Sheets

[QS_User_2019]

Hi All,

I am new to QS and try to load two excel files. Each excel files contain three sheets. These excel files name 2018 and 2019. The sheets name for those excel files are 07, 08 and 09. I would like to create two Tables in QS but not sure why it will combine into one Table after loading below script. Can anyone help me to check? Thank you.

LET vStartSheetNumber2019 = 7;
LET vEndSheetNumber2019 = 9;
FOR index2019 = vStartSheetNumber2019 TO vEndSheetNumber2019

2019:
LOAD
*,
'2019' as [Year]
FROM [lib://Data/2019.xlsx]
(ooxml, embedded labels, table is [$(index2019)]);
Next

LET vStartSheetNumber2018 = 7;
LET vEndSheetNumber2018 = 9;
FOR index2018 = vStartSheetNumber2018 TO vEndSheetNumber2018

2018:
LOAD
*,
'2018' as [Year]
FROM [lib://Data/2018.xlsx]
(ooxml, embedded labels, table is [$(index2018)]);
Next

[ajaykakkar93]

Hi,

you can use a loop in backend and achieve this

[Ksrinivasan]

hi,

You have to introduce DROP TABLE between the 2019 and 2018 as i mentioned in tour prog.

LET vStartSheetNumber2019 = 7;
LET vEndSheetNumber2019 = 9;
FOR index2019 = vStartSheetNumber2019 TO vEndSheetNumber2019

2019:
LOAD
*,
'2019' as [Year]
FROM [lib://Data/2019.xlsx]
(ooxml, embedded labels, table is [$(index2019)]);
Next

Drop table 2019;

LET vStartSheetNumber2018 = 7;
LET vEndSheetNumber2018 = 9;
FOR index2018 = vStartSheetNumber2018 TO vEndSheetNumber2018

2018:
LOAD
*,
'2018' as [Year]
FROM [lib://Data/2018.xlsx]
(ooxml, embedded labels, table is [$(index2018)]);
Next

K. Srinivasan

[QS_User_2019]

Hi Srinivasan,

I try to insert Drop table 2019; inside the script but the 2019 table gone after loading. Is it possible to keep two separat table? Thank you.

[QS_User_2019]

Hi Kakkar,

Sorry that I can't get you. Can you provide me more detail?  I am not sure if this related to my fields name issue. All fiels name for 2018 and 2019 files are exactly the same. Thank you.

[Ksrinivasan]

Hi,

with out drop table all excel sheet field names are joining if, filed Name is same, so you cant make TWO tables,

if you introduced Drop Table, Even thou filed name are same in 2019 and 2018, it wont join together, so you can maintain

TWO table as you asked, 

K. Srinivasan.

[anushree1]

use no cancantenate in between 2018 and 2019 table .

Please check : https://help.qlik.com/en-US/qlikview/April2019/Subsystems/Client/Content/QV_QlikView/LoadData/concat...

[QS_User_2019]

Hi,

When I add "noconcatenate" between "Load" and "2018:". Those sheets saved in 2018.xlsx will splite into three tables automatically (2018, 2018-1, 2018-2).

[anushree1]

Try like below:

LET vStartSheetNumber2019 = 7;
LET vEndSheetNumber2019 = 9;
FOR index2019 = vStartSheetNumber2019 TO vEndSheetNumber2019

2019:
LOAD
*,
'2019' as [Year]
FROM [lib://Data/2019.xlsx]
(ooxml, embedded labels, table is [$(index2019)]);
Next

LET vStartSheetNumber2018 = 7;
LET vEndSheetNumber2018 = 9;
FOR index2018 = vStartSheetNumber2018 TO vEndSheetNumber2018

noconcatenate

2018:
LOAD
*,
'2018' as [Year]
FROM [lib://Data/2018.xlsx]
(ooxml, embedded labels, table is [$(index2018)]);
Next

[Ksrinivasan]

Hi, You have to maintain '2019' as [Year] '2018' as [YEAR] then TWO independent table will form. LET vStartSheetNumber2019 = 7; LET vEndSheetNumber2019 = 9; FOR index2019 = vStartSheetNumber2019 TO vEndSheetNumber2019 2019: LOAD *, '2019' as [Year] FROM [lib://TEST/2019.xlsx] (ooxml, embedded labels, table is [$(index2019)]); Next LET vStartSheetNumber2018 = 7; LET vEndSheetNumber2018 = 9; FOR index2018 = vStartSheetNumber2018 TO vEndSheetNumber2018 2018: LOAD *, '2018' as [YEAR] FROM [lib://TEST/2018.xlsx] (ooxml, embedded labels, table is [$(index2018)]); Next K. Srinivasan