Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading Multiple Excel Files Directly From Sharepoint Into Qlikview

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.

18 Replies
Not applicable
Author

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Not applicable
Author

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

cleveranjos‌ would you be able to help on this?

Clever_Anjos
Employee
Employee

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

Please check this: Dynamically Load Files From SharePoint

Not applicable
Author

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

Anyone any other thoughts?

Not applicable
Author

QV Experts,

Any idea on this case?

Clever_Anjos
Employee
Employee

>> it doesn't work either.

Any error messages?

anandathome
Creator
Creator

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

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