Load multiple excel files from Multiple excel sheetsinto QV

[#1]

Hi everybody,

can anybody help me out in completing this challenge

.

well the challenge is

I have a folder in which i have the source files along with the qv file

and the source files which are all xlsx  files

Things to remembe are before understandinng the result i need:

-Source files are not limited in number and may have different names but are all xlsx files

- source files may have no of worksheets and are not limited in number and may vary from book to book

-Name of every sheet in the every excel file is different

-In every source excel file only the 1st sheet will have the header and all other following sheets in that file will have no headers

- The no of rows filled are not confined to anylimit and may vary from sheet to sheet in the file

-the headers are similar in all the source files provided in the 1st sheet of every source file

Result i need :

based on the above conditions ,If i reload the qv by placing all the source files in the folder ,i need to the qv to be reloaded completely with the data in the source files

for refernce the source files are as attached in the image and are attched in excel formats fr quick reference

The code im trying in qlikview is as per the following

***********************************************************************************

for each vFileName in FileList('D:\Users\aju\Desktop\anand\*.xlsx');

for Each vSheet in $(vFileName)

Temp_Sheets:

LOAD *

FROM $(vFileName)

(biff, embedded labels, header is 1 lines, table is $(vSheet));

let vMaxSheet = peek('Sheets', -1, 'Temp_Sheets');

DROP TABLE Temp_Sheets;

for vSheetNo = 1 to $(vMaxSheet)

     Data:

     LOAD*

     FROM $(vFileName)

     (biff, embedded labels, header is 1 lines, table is $(vSheet));

next

****************************************************************************************************************

I request you help and resolve this challenge

Thanks ahead !!!

Kind Regards

Anand

[stevedark · Accepted solution]

Hi Anand,

Please find here a working example of pulling in the Excel files you uploaded.

Note it may not work without tweaking for Excel files other than the ones you uploaded, or on a machine with slightly different Excel drivers.  As far as I can see it works without issue over your files.

Please let me know if you would be happy for me to upload the example to the Shared QlikViews area ( http://community.qlik.com/qlikviews ) as this will then make it Personal Edition enabled for other users.

Post back if you have any problems with the file.

Regards,

Steve

http://www.quickintelligence.co.uk/

[#3]

Hi Steve,

Thanks a ton Steve, Working on it , would be going on a vacation for couple of days and so will have no access to QV ,thus would revert to you soon Im back in office.

Thanks for the help

Kind Regards

Anand

[#4]

Dear All,

My Excel Sheets workbook contains space in Name i.e.  Data 1,Data 2.

I tried the code above but doesn't work where there is space in workbook name.

Please help

Kind Regards

Himanshu

[stevedark]

You may be requiring square brackets around the sheet name?

Try using the wizard to pull in a single sheet and compare that to the code

in your loop.

- Steve

http://www.quickintelligence.co.uk/

[#6]

Can you please help with an Example, that would be a great help for me

Thanks in advance

Himanshu

[#7]

Thanks to all,

I resolved it.

Here is the code

ODBC CONNECT TO [Excel Files;DBQ=D:\Users\Himanshu\Desktop\Harsh FSource\DBHarsh.xlsx];

tables:

SQLTables;

DISCONNECT;

Tables1:

load *,

replace(TABLE_NAME,chr(39),'') as NEW

resident tables;

DROP Table tables;

let var=NoOfRows('Tables1');

FOR i = 0 to $(var)-1

let sheetName=subfield(peek('NEW', i,'Tables1'),'$',1);

[$(sheetName)]:

LOAD *

FROM

(ooxml, embedded labels, table is [$(sheetName)])

;

NEXT i

DROP Table Tables1;

[#8]

Hi All,

Had one concern. Can we automate the QV Source file (say Excel) refresh through a CRON job?

[stevedark]

Hi,

You can kick things off from the command line, which should mean you can use CRON to do this (I have only used Windows Scheduler and QMC in the past).

The best article I have seen on the parameters you have available is by Tim Benoit and can be found here:

http://qlikviewmaven.blogspot.co.uk/2008/08/qlikview-command-line-and-automation.html

Hope that helps,

Steve

[dafnis14]

A "life saver", Steve.

Thanks so much for sharing!