Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

[#1]

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.

[#2]

stalwar1‌

Do you have any suggestion on this post?

[Anil_Babu_Samineni]

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

[#4]

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

cleveranjos‌ would you be able to help on this?

[Clever_Anjos]

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

Please check this: Dynamically Load Files From SharePoint

[#6]

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

Anyone any other thoughts?

[#7]

QV Experts,

Any idea on this case?

[Clever_Anjos]

>> it doesn't work either.

Any error messages?

[anandathome]

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: ***)

[anandathome]

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