How to pull in multiple sheets from excel file?

[#1]

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$);

[#2]

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.

[#3]

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

[cbsbrasil]

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]

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.

[maxgro]

an example

http://community.qlik.com/message/589459#589459