Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. 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...