Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
Not applicable

How to pull in multiple sheets from excel file?

Hello,

I know this has to be so simple but i can't figure out how to format the load script to pull in multiple sheets of my excel spreadsheet without having to upload multiple files. How should it look? Is it something you do in the table files upload process or do you do it right in the load script? Thank you!

FROM

C:\Users\Name\Documents\Forums\20120521_Forums.xls]

(biff, embedded labels, table is Sheet1$);

5 Replies
Not applicable

How to pull in multiple sheets from excel file?

Hi,

You can use a loop variable to do that...

//for a workbook with 5 sheets, you'd use:

for loop=1 to 5  //n =5 (i.e., no of sheets in your excel file)

Test Data:

LOAD

field1,

field2,

field3  

FROM

Test_Book.xlsx

(ooxml, embedded labels, table is Sheet$(loop));

NEXT

Hope that helps.

-Khaled.

Not applicable

Re: How to pull in multiple sheets from excel file?

What if a workbook has 4 sheets and the other workbook has 5 sheets?

cbsbrasil
New Contributor

Re: How to pull in multiple sheets from excel file?

Is an example.
SET PATH01='\\br-s-file01\documentos\Orçamentos\Planejamento\Budget 2015-2016\Board Presidency';
SET PATH02='\\br-s-file01\documentos\Orçamentos\Planejamento\Budget 2015-2016\Board Finance'; 
SET XLS01='02 - 01.01 - Diretoria - Presidência - Orçamento (dados) - 2015-2016 v.1.xlsx';
SET XLS02='02 - 01.03 - Diretoria - Financeira - Orçamento (dados) - 2015-2016 v.1.xlsx'; 
CENTROS_CUSTOS01: //Board Presidency
LOAD * INLINE [
E01, N01, C01, P01
1, 1, 2, Total Diretoria Presidência
1, 1, 4, 4-Dir. Presidência - 01-Udia ]
;

CENTROS_CUSTOS02:
//Board Finance
LOAD * INLINE [
E02, N02, C02, P02
1, 1, 10, Total Diretoria Financeira
1, 1, 12, 12-Dir. Financeira - 01-Udia ]
;

//************************************************************************************01

FOR i = 0 to NoOfRows('CENTROS_CUSTOS01') - 1

LET vCDEmpresa01 = peek('E01',$(i), 'CENTROS_CUSTOS01');
LET vCDNegocio01 = peek('N01',$(i), 'CENTROS_CUSTOS01');
LET vCDCentroCusto01 = peek('C01',$(i), 'CENTROS_CUSTOS01');
LET vNMCentroCusto01 = peek('P01',$(i), 'CENTROS_CUSTOS01');

BUDGET_V1:
LOAD
'$(vCDEmpresa01)'
as [CD Empresa],
'$(vCDNegocio01)'
as [CD Negócio],
'$(vCDCentroCusto01)'
as [CD Centro Custo],
'$(vNMCentroCusto01)'
as Planilha,
X as [CD Conta],
AD as [Budget V1 Jul-15],
AE as [Budget V1 Ago-15],
AF as [Budget V1 Set-15],
AG as [Budget V1 Out-15],
AH as [Budget V1 Nov-15],
AI as [Budget V1 Dez-15],
AJ as [Budget V1 Jan-16],
AK as [Budget V1 Fev-16],
AL as [Budget V1 Mar-16],
AM as [Budget V1 Abr-16],
AN as [Budget V1 Mai-16],
AO as [Budget V1 Jun-16],
AQ as [Budget V1 Total]
FROM
[$(PATH01)\$(XLS01)]
(
ooxml, explicit labels, header is 175 lines, table is '$(vNMCentroCusto01)')
Where isnum (X) and X > '100000' and X < '999999' and AQ <> 0;

NEXT

//************************************************************************************02

FOR i = 0 to NoOfRows('CENTROS_CUSTOS02') - 1

LET vCDEmpresa02 = peek('E02',$(i), 'CENTROS_CUSTOS02');
LET vCDNegocio02 = peek('N02',$(i), 'CENTROS_CUSTOS02');
LET vCDCentroCusto02 = peek('C02',$(i), 'CENTROS_CUSTOS02');
LET vNMCentroCusto02 = peek('P02',$(i), 'CENTROS_CUSTOS02');

BUDGET_V1:
LOAD
'$(vCDEmpresa02)'
as [CD Empresa],
'$(vCDNegocio02)'
as [CD Negócio],
'$(vCDCentroCusto02)'
as [CD Centro Custo],
'$(vNMCentroCusto02)'
as Planilha,
X as [CD Conta],
AD as [Budget V1 Jul-15],
AE as [Budget V1 Ago-15],
AF as [Budget V1 Set-15],
AG as [Budget V1 Out-15],
AH as [Budget V1 Nov-15],
AI as [Budget V1 Dez-15],
AJ as [Budget V1 Jan-16],
AK as [Budget V1 Fev-16],
AL as [Budget V1 Mar-16],
AM as [Budget V1 Abr-16],
AN as [Budget V1 Mai-16],
AO as [Budget V1 Jun-16],
AQ as [Budget V1 Total]
FROM
[$(PATH02)\$(XLS02)]
(
ooxml, explicit labels, header is 175 lines, table is '$(vNMCentroCusto02)')
Where isnum (X) and X > '100000' and X < '999999' and AQ <> 0;

NEXT

jsakalis
New Contributor III

Re: How to pull in multiple sheets from excel file?

See attached.

The QVW will load a single file with varying number of Excel tabs. Another FOR loop surrounding the script provided here can load in multiple files, again with varying number of tabs. This assumes that each sheet in each file will contain the same fields.

Notice that the script filters out sheets that are named in a particular way (IF) - feel free to modify to exclude tabs that should be ignored.

Hope this works for you.

MVP
MVP

Re: How to pull in multiple sheets from excel file?