Import from excel file - with multipule sheets

[gidon500]

Hi All

i have a customer with few  excel files  that have many sheets , can be more than 20

the sheet name change

the the fields in all files are the same

like the sample I have attached

i want to load all sheets in one

script line

is it possible ?

thanks

gidon

[kaushiknsolanki]

Hi,

     Have a look at the link below.

     http://qlikviewnotes.blogspot.in/2008/09/loading-multiple-excel-sheets.html

     http://www.qlikviewaddict.com/2012/03/looping-through-excel-sheets.html

Regards,

Kaushik Solanki

[jyothish8807]

Yes this is possible.

first rename all sheets as 1,2,3,4..... and so on.

Then load first sheet and use loop statement.

for a=1 to 3 //no of sheets you have

LOAD employee

FROM

Looping\constructs1.xlsx // this is the excel sheet name

(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a

Next

Regards

Jyothish KC

[Gysbert_Wassenaar]

No need to rename sheets. See this discussion: Re: Pulling multiple excel sheets into QV when all headings are the same

[jyothish8807]

Hi G Wassenaar,

Is it fine what i have mentioned above ???, because i am using that script to export data from multiple sheets.

Thanks & Regards

Jyothish KC

[gidon500]

hi

i cannot make it work

attacked excel file , vw model and the error

thanks

gidon

[#7]

As @ gwassenaar says, use this, regards!!

1. ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx]; 
2.  
3. XlsTables: 
4. SQLTables; 
5. DISCONNECT; 
6.  
7. LET vRows = NoOfRows('XlsTables'); 
8. SET vConcatenate =; 
9.  
10. FOR i = 0 TO $(vRows)-1 
11.  
12. LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1); 
13.       
14. Table1: 
15. $(vConcatenate) 
16. LOAD * 
17. FROM [ExcelFile.xlsx] 
18. (ooxml, embedded labels, table is '$(sheetName)'); 
19. Set vConcatenate = Concatenate(Table1); 
20. NEXT i 
21.  
22. DROP TABLE XlsTables;

[javier_florian]

Hi Gidon,

I have a similar issue, however IT send me files with different file names and different sheet names, so I create a Temp_Sheets table to call these ones (my sheets names were ProductName_Date_Check, didn't have a structure in Check)... Try with the follow script:

Temp_Sheets:

LOAD * INLINE

[Sheets

1,

2,

3];

FOR i=0 TO NoOfRows('Temp_Sheets')-1

LET vFileName = '';

//LET vSheetName = 'C58CO02TS_VIGENTES'& purgeChar(peek('Sheets', i, 'Temp_Sheets'), chr(39)) &'_20131010';

LET vSheetName = purgeChar(peek('Sheets', i, 'Temp_Sheets'), chr(39));

Initial_Data:

LOAD a,

    b,

    c

FROM

$(vFileName)

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

NEXT

DROP TABLE Temp_Sheets;

Tell us if its work for you and your comments.

Regards,

Javier Florian