topic Re: Import from excel file - with multipule sheets in QlikView

Import from excel file - with multipule sheets

gidon500 — Fri, 18 Oct 2013 08:36:45 GMT

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

Re: Import from excel file - with multipule sheets

kaushiknsolanki — Fri, 18 Oct 2013 08:39:57 GMT

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

Re: Import from excel file - with multipule sheets

jyothish8807 — Fri, 18 Oct 2013 08:45:00 GMT

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

Re: Import from excel file - with multipule sheets

Gysbert_Wassenaar — Fri, 18 Oct 2013 08:47:25 GMT

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

Re: Import from excel file - with multipule sheets

jyothish8807 — Fri, 18 Oct 2013 10:53:44 GMT

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

Re: Re: Import from excel file - with multipule sheets

gidon500 — Fri, 18 Oct 2013 11:39:25 GMT

i cannot make it work

attacked excel file , vw model and the error

thanks

gidon

Re: Re: Re: Import from excel file - with multipule sheets

Fri, 18 Oct 2013 18:35:05 GMT

As @ gwassenaar says, use this, regards!!

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];
XlsTables:
SQLTables;
DISCONNECT;
LET vRows = NoOfRows('XlsTables');
SET vConcatenate =;
FOR i = 0 TO $(vRows)-1
LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);
Table1:
$(vConcatenate)
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(sheetName)');
Set vConcatenate = Concatenate(Table1);
NEXT i
DROP TABLE XlsTables;

Re: Import from excel file - with multipule sheets

javier_florian — Fri, 18 Oct 2013 20:08:26 GMT

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

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,

FROM

$(vFileName)

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

DROP TABLE Temp_Sheets;

Tell us if its work for you and your comments.

Regards,

Javier Florian