topic Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview in QlikView

Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Tue, 11 Apr 2017 19:54:09 GMT

Currently I am using the below script to load multiple files from my local desktop and it's working fine:

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[..\..\..\..\..\Desktop\JG_ALL\*.xlsx]
(ooxml, no labels, table is [Report Data 1])

where
C = 'Document'
and C <> 'Item Type'
and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>'
and D <> 'User Id'
and E <> 'Document Location'
and E LIKE '*sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*')
and F <> 'Occurred (GMT)'
and G <> 'Event'
and I <> 'Event Source'
;
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS.QVD(QVD);

Now,

With the same script I need to load same multiple files from SharePoint but I am unable to do so.

For example if my SharePoint site is http://abc.com/Reports

and the file names in the Reports folder are as follows:

Audit_Log_2017-03-01T080000

Audit_Log_2017-03-01T080000_(5)

Audit_Log_2017-03-01T080000_(10)

Audit_Log_2017-03-01T080000_(9)

Audit_Log_2017-03-01T080000_(8)

Audit_Log_2017-01-29T080002_(8)

Audit_Log_2017-01-29T080002

Audit_Log_2016-12-01T080000_(10)

Audit_Log_2016-12-01T080000_(2)

Audit_Log_2016-08-01T070000

Run_a_custom_report_2016-11-04T090238

Run_a_custom_report_2017-1-02T0142566

and so on...and there are files for year before 2016 in this folder and for each month there are almost 10 files ending in _(2) so on to _(10).

Log files are added to this folder every month and there are multiple files added as all data doesn't fit in one excel document.

So my question is how do I pull only the Audit_Log_* files for year 2016 and 2017 excluding all the Run_* files directly from sharepoint location and load into Qlikview.

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

hector_munoz — Tue, 11 Apr 2017 21:19:03 GMT

Hi Deb,

Coul you load at least one of them? In that case, could you share the script?

Regards,

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Anil_Babu_Samineni — Wed, 12 Apr 2017 05:46:43 GMT

You can use where condition with wild match like for Share point link with qvd

WildMatch(FieldName, 'Audit_Log_*') and Match(Year, '2016','2017');

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 15:34:18 GMT

@Anil

Thanks for the reply.

Can you tell me where to incorporate that condition in the above script? I placed it where all my conditions are but it's still throwing the same error. . It's a Zip error.

What should be the from statement. For example my SharePoint Location for the files is http://abc.com/Reports and if I put from http://abc.com/Reports/*.xlsx it won't work.

So with your suggestion can you tell me what my from statement should be and where should I place your suggestion condition in the script?

@Munoz

Yes I can pull a single file directly from SharePoint. In that case you just have to put the direct SharePoint site in the FROM statement and rest of the script is the same. For example if you want to pull file named 'Audit_Log_2017-03-01T080000'

From statement should be like this:

FROM
[ http://abc.com/Reports/Audit_Log_2017-03-01T080000.xlsx]
(ooxml, no labels, table is [Report Data 1])

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

hector_munoz — Wed, 12 Apr 2017 15:41:25 GMT

Hi Deb,

Try:

LOAD *

FROM [http://abc.com/Reports/Audit_Log_20??-??-?????????.xlsx]

Regards,

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 16:00:12 GMT

Hi Munoz,

Let's rethink this. Below is the script which works fine when I pull a single excel file from SharePoint. But the same doesn't work will I try to pull all the files with *.xlsx .

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[http://abc.com/Reports/Audit_Log_2017-03-01T080000_(10).xlsx]
(ooxml, no labels, table is [Report Data 1])

where

C = 'Document'
and C <> 'Item Type'
and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>'
and D <> 'User Id'
and E <> 'Document Location'
and E LIKE '*sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*')
and F <> 'Occurred (GMT)'
and G <> 'Event'
and I <> 'Event Source'
;
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS_DIRECTFETCH.QVD(QVD);

In the above script how should your suggestion be incorporated. Can you resend the above script with your updates to pull all the excel files in the folder for year 2016 and 2017.

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Anil_Babu_Samineni — Wed, 12 Apr 2017 16:01:48 GMT

All the file means? Do you have pagination over Sharepoint?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

hector_munoz — Wed, 12 Apr 2017 16:05:29 GMT

Try:

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
[http://abc.com/Reports/Audit_Log_20??-??-??T??????*.xlsx]
(ooxml, no labels, table is [Report Data 1])

where

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 16:07:27 GMT

@ Anil,

All the files means I need to pull all the files in 'Reports' folder from the SharePoint path http://abc.com/Reports/

The files in the folder are named like below:

Audit_Log_2017-03-01T080000

Audit_Log_2017-03-01T080000_(5)

Audit_Log_2017-03-01T080000_(10)

Audit_Log_2017-03-01T080000_(9)

Audit_Log_2017-03-01T080000_(8)

Audit_Log_2017-01-29T080002_(8)

Audit_Log_2017-01-29T080002

Audit_Log_2016-12-01T080000_(10)

Audit_Log_2016-12-01T080000_(2)

Audit_Log_2016-08-01T070000

Run_a_custom_report_2016-11-04T090238

Run_a_custom_report_2017-1-02T0142566

So I want to be able to pull all the files in that folder where the file names start like Audit_Log_2016 and 2017 only.

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 16:12:33 GMT

@Munoz,

Having *.xlsx in the from statement will never work to pull multiple files from SharePoint. There has to be some other workaround.

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 16:13:48 GMT

stalwar1‌

Do you have any suggestion on this post?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Anil_Babu_Samineni — Wed, 12 Apr 2017 16:31:16 GMT

May be cleveranjos‌ helps you. Because, Once upon a time I've seen his responsibilities

Or From my end

Create one variable for this

SET VarLink = 'http://abc.com/Reports/'

Load FileName() as FileName,

SubField(FileName(), '/', -1) as FileNames

Autogenerate 1;

Then use this script, I am not sure(Due to i never worked with SP) as functionally i am explaining you

SP_ASSETS:
LOAD
C,
D,
E,
F,
G,
I
FROM
$(VarLink)&FileNames
(ooxml, no labels, table is [Report Data 1])

where C = 'Document' and C <> 'Item Type' and D <> 'MOSS-Prod-SearchSvc <GLOBAL\moss-prod-searchsvc>' and D <> 'User Id' and E <> 'Document Location' and E LIKE *sales/sales_central/SalesEssentialsAssets/*'
and not WildMatch(E,'sales/sales_central/SalesEssentialsAssets/Forms/*') and F <> 'Occurred (GMT)'
and G <> 'Event' and I <> 'Event Source';
STORE SP_ASSETS INTO $(vQVD)CBIC\SP_ASSETS_DIRECTFETCH.QVD(QVD);

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Wed, 12 Apr 2017 17:42:28 GMT

@Anil..Thanks for the input but it didn't work either.

cleveranjos‌ would you be able to help on this?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Clever_Anjos — Thu, 13 Apr 2017 10:59:12 GMT

Maybe using UNC instead of http could be a solution in your case

Please check this: Dynamically Load Files From SharePoint

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Thu, 13 Apr 2017 21:12:04 GMT

cleveranjos‌ Thanks for your input. I have tried that earlier but it doesn't work either.

Anyone any other thoughts?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Mon, 17 Apr 2017 20:01:45 GMT

QV Experts,

Any idea on this case?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

Clever_Anjos — Tue, 18 Apr 2017 11:20:46 GMT

>> it doesn't work either.

Any error messages?

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

anandathome — Mon, 16 Sep 2019 09:14:17 GMT

Sorry, replying to an earlier post. But this post has most relevant replies, so adding some more comments.

I encountered the below error in QlikSense ( with UNC path)

The following error occurred:

Cannot open file: 'lib://SharePoint/ExcelFiles_*.xlsx'

(Native Path: *** System error: ***)

Re: Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

anandathome — Mon, 16 Sep 2019 09:25:46 GMT

Just found this reply from Jonathan:

Will have to find work around...

https://community.qlik.com/t5/QlikView-Scripting/Loading-multiple-Excel-files-from-SharePoint/m-p/557293/highlight/true