Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Case Portal has moved to Qlik Community! Read the FAQs to start exploring Support resources.
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
hector_munoz
Specialist
Specialist

Hi Deb,

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

Regards,

H

Anil_Babu_Samineni

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

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

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

hector_munoz
Specialist
Specialist

Not applicable
Author

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.

Anil_Babu_Samineni

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

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)
hector_munoz
Specialist
Specialist

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

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

Not applicable
Author

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

Not applicable
Author

@Munoz,

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