topic Re: Load multiple excel files from Multiple excel sheetsinto QV in QlikView

Load multiple excel files from Multiple excel sheetsinto QV

Wed, 12 Sep 2012 19:18:55 GMT

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

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

I request you help and resolve this challenge

Thanks ahead !!!

Kind Regards

Anand

Re: Load multiple excel files from Multiple excel sheetsinto QV

stevedark — Thu, 13 Sep 2012 21:03:10 GMT

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/

Re: Load multiple excel files from Multiple excel sheetsinto QV

Fri, 14 Sep 2012 14:57:09 GMT

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

Re: Load multiple excel files from Multiple excel sheetsinto QV

Mon, 14 Jan 2013 15:56:27 GMT

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

Re: Load multiple excel files from Multiple excel sheetsinto QV

stevedark — Mon, 14 Jan 2013 16:02:39 GMT

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/

Re: Load multiple excel files from Multiple excel sheetsinto QV

Mon, 14 Jan 2013 16:21:36 GMT

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

Thanks in advance

Himanshu

Re: Load multiple excel files from Multiple excel sheetsinto QV

Tue, 15 Jan 2013 05:13:40 GMT

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;

Re: Load multiple excel files from Multiple excel sheetsinto QV

Wed, 24 Sep 2014 05:00:19 GMT

Hi All,

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

Re: Load multiple excel files from Multiple excel sheetsinto QV

stevedark — Wed, 24 Sep 2014 07:24:55 GMT

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

Re: Load multiple excel files from Multiple excel sheetsinto QV

dafnis14 — Thu, 10 Nov 2016 05:06:14 GMT

A "life saver", Steve.

Thanks so much for sharing!

Re: Load multiple excel files from Multiple excel sheetsinto QV

rustyfishbones — Tue, 07 Mar 2017 23:08:33 GMT

Hi Steve,

I have the following, while trying to Store into qvd's, all the qvd's get created, but the data is from the first Workbook 2014.

I have 3 excel workbooks - 2014,2015,2016, each workbook has 4 sheets and I get 12 QVD's 2014Jan.qvd 2014Feb.qvd..............................................

2014Jan.qvd is correct, but 2015Jan.qvd and 2016Jan.qvd have the data from 2014Jan.qvd

Any Ideas my friend

LET vFolder = '.\';

FOR EACH WorkBook IN FILELIST('$(vFolder)*.xlsx')

ODBC CONNECT32 TO [Excel Files;DBQ=$(WorkBook)];

exceltables:

SQLTABLES;

DISCONNECT;

LET vFileName = SUBFIELD(MID(WorkBook, INDEX(WorkBook, '\', -1) + 1),'.',1);

TRACE Excel FileName $(vFileName);

FOR i = 0 TO NOOFROWS('exceltables')-1

LET WorkSheet = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME',i, 'exceltables'),CHR(36)),CHR(39));

NOCONCATENATE

LOAD

FILENAME() AS Name,

Department,

Amount

FROM [$(WorkBook)]

(ooxml, embedded labels, table is [$(WorkSheet)]);

TRACE $(WorkSheet) **************************************;

SLEEP 1000;

STORE [$(WorkSheet)] INTO $(vFileName)$(WorkSheet).QVD(QVD);

TRACE $(WorkSheet) **************************************;

DROP TABLE exceltables;

NEXT WorkBook

Re: Load multiple excel files from Multiple excel sheetsinto QV

stevedark — Wed, 08 Mar 2017 00:04:04 GMT

Hi Alan,

You need to add a DROP TABLES line, after each STORE of the QVD:

DROP TABLE [$(WorkSheet)];

What will be happening at the moment is that a table called Jan will get created first, for 2014. This will still be there when you get back to that month for 2015. The 2015 load of Jan will therefore go into Jan-1 (because of the NOCONCATENATE). When you STORE Jan it will store the original one, rather than the new one. If you look at the data model after the load you will find loads of tables with numeric suffixes.

Pretty much every time you do a STORE in a load script it should be followed by a DROP.

Hope that stores your issue (and that I get a mention in one of your videos on the topic!).

Cheers,

Steve

Re: Load multiple excel files from Multiple excel sheetsinto QV

rustyfishbones — Wed, 08 Mar 2017 09:58:08 GMT

Thanks Steve,

You're a gentleman, a definite mention in the next video!

explained brilliantly too.

Regards

Alan

Re: Load multiple excel files from Multiple excel sheetsinto QV

Anonymous — Fri, 18 May 2018 06:48:50 GMT

Hello Steve... i have a strange problem. For me this code doesn't work if all pasted as it is including loops. i get a script error... no error number.

but it works if i run it for a single file with assigning filename and sheet name manually to the variables.

Re: Load multiple excel files from Multiple excel sheetsinto QV

stevedark — Fri, 18 May 2018 07:30:30 GMT

Which code are you trying?

Can you paste the code which works without a loop, and the code that fails with a loop, so I can see what is the likely problem. There is nothing in the screengrab that looks wrong. I might try dropping the first 10 though, as that could have an impact.