topic Re: How to load a particular sheet from different excel?? in QlikView

How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 07:17:58 GMT

Hi All,

I am having 10 excel file in one url. (In future it’ll increase or decrease)

All excel files names are different but all having the same structure (Same sheet names and column names)

All excel files having the 3 sheets like Sheet A, B, and C.

I want to load Sheet B only from all excel files through one LOAD statement.

Can anyone please suggest me how to do this???

For example:

Following are 5 sheets and all are having 3 sheets like Sheet A, B, and C.

I want to load Sheet B only from all excel files through one LOAD statement.

(All are dummy url only)

https //sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/JDJ_025M_1.0_First.xlsm"
https // sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/JDJ_025M_1.0_Second.xlsm"
https // sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/JDJ_025M_1.0_Third.xlsm"
https // sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/JDJ_025M_1.0_Fourth.xlsm"
https // sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/JDJ_025M_1.0_Fifth.xlsm"

Please Help......

Re: How to load a particular sheet from different excel??

Fri, 04 Jul 2014 07:23:28 GMT

Use * (star) for load some files.

Document*.xls load all files started with "document". For Example^

Document_1.xls

Dicument-12lh.xls

etc

And in filters please select needed sheet.

Load

From

https // sharepoint.abc.com/def/TSM/project/123jpo345/Docs/Jan_Feb_Doc/*.xls

(ooxml, embedded labels, table is sheetB);

Re: How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 08:04:49 GMT

Hi Dmitry,

I already Tried this but it showing following error message:

If I load single file then its working fine.

Please suggest.

Re: How to load a particular sheet from different excel??

shane_spencer — Fri, 04 Jul 2014 08:12:05 GMT

Does it work fine for every single file if you load the individually? That looks like a bad file.

Re: How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 08:18:03 GMT

Yes Shane, If I am loading individually then its working fine without any issue.

Re: How to load a particular sheet from different excel??

shane_spencer — Fri, 04 Jul 2014 08:35:56 GMT

Have you run a debug? It looks like it's failing on 1 particular file. If you can share the files I can look at why it's failing but it's difficult to advise without trying myself.

have a look at other threads about "bad zip file"

http://www.qlikcommunity.com/thread/15957

http://community.qlik.com/thread/75785

Re: Re: How to load a particular sheet from different excel??

shane_spencer — Fri, 04 Jul 2014 08:51:45 GMT

Try this load script instead:

directory 'insert file location here'

for each xlsmfile in FileList('JDJ_025M_1.0_*.xlsm')

LOAD *
FROM $(xlsmfile)
(ooxml, embedded labels, table is sheetB);

next xlsmfile

If that does not work try moving the files to a normal share and doing a test load from there. I think I had issues when loading from sharepoint in the past.

Re: How to load a particular sheet from different excel??

Fri, 04 Jul 2014 09:23:16 GMT

PFA the solution.

Re: How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 09:45:21 GMT

Its not working Deepak.

Re: How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 10:36:36 GMT

Hi Shane I tried your suggested code. It is not showing any error but it also not loading any data.

Please suggest. Its very urgent for me.

Thanks for your help....

Re: How to load a particular sheet from different excel??

amit_saini — Fri, 04 Jul 2014 10:47:17 GMT

Hi Soha,

For example I'm having these three sheet as below:

Operations Panel ChartsKautex 2013 ABC.xlsx

Operations Panel ChartsKautex 2013 DEF.xlsx

Operations Panel ChartsKautex 2013 AXZ.xlsx

mention below is the link which will help you to call all the sheets (Condition you should have similar column names inside for all of them)

[http://....../Operations/global/Documents/Reporting/Operations%20Panel%20ChartsKautex%202013%20$(P).xlsx]

In this link I'm calling 'P' as vaiable having values ABX, DEF,AXZ....etc

Thanks,

Re: How to load a particular sheet from different excel??

amit_saini — Fri, 04 Jul 2014 10:48:45 GMT

Hi Soha,

See the attachment.

Thanks,

Re: How to load a particular sheet from different excel??

Fri, 04 Jul 2014 11:16:31 GMT

It works for me Soha. I had similar situation to load the data from files of different month like Apr.xls, May.xls. You have to copy all the files at the same location and then run a for loop with the filename .

Re: How to load a particular sheet from different excel??

soha1902 — Fri, 04 Jul 2014 11:17:33 GMT

Hello Amit,

Can you please let me know where we need to assign value in variable 'P'.

In your case you didn't assign any value in variable 'P'.

How it fetch all the data from different excel file???

Thanks for you reply...

Re: How to load a particular sheet from different excel??

amit_saini — Fri, 04 Jul 2014 12:04:02 GMT

Soha,

You have to create a vairable in your application and assign value as in my case it should be ABC,DEF,AXZ to read all these three sheet from one link.

Thanks,
AS

Re: How to load a particular sheet from different excel??

shane_spencer — Fri, 04 Jul 2014 13:02:55 GMT

If you can provide some sample files I'll try to build a load script for you.