Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
soha1902
Creator
Creator

How to load a particular sheet from different excel??

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)

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

Please Help......

15 Replies
Not applicable

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

soha1902
Creator
Creator
Author

Hi Dmitry,

I already Tried this but it showing following error message:

BadZip.png

If I load single file then its working fine.

Please suggest.

shane_spencer
Specialist
Specialist

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

soha1902
Creator
Creator
Author

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

shane_spencer
Specialist
Specialist

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

shane_spencer
Specialist
Specialist

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.

Not applicable

PFA the solution.

soha1902
Creator
Creator
Author

Its not working Deepak.

soha1902
Creator
Creator
Author

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....