Qlik Community

Ask a Question

Connectivity & Data Prep

Discussion board where members can learn more about Qlik Sense Data Connectivity.

Announcements
Sept. 16, Qlik Product Portfolio Strategy and Roadmap for Data Integration 11 AM ET REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
elinamcastelletta

Connect to Sharepoint Folder

Hi,

I need to connect  excel files saved in a Sharepoint folder. I managed to get the URL and it works if I create a Web file connection for each excel file. However this is not an optimal solution. I prefer to have a unique connection to the folder.

Could you help me to understand how should I create a unique connection to the main folder and script the data load for each file?

 

Thanks

 

 

1 Solution

Accepted Solutions
stevedark
MVP
MVP

Hi @elinamcastelletta 

The format specifier qvx is used for Sharepoint Lists, and a lot of the other data that Qlik Web Connectors serves up.

For Excel you need all of the format specifiers to make it work:

LOAD
*
FROM [lib://GenericWeb]
(URL IS [$(vblefolder)$(vblefile)&appID=], ooxml, embedded labels, table is [Sheet_name])

 

Hope that cracks it.

Steve

View solution in original post

6 Replies
stevedark
MVP
MVP

Hi @elinamcastelletta 

What version of Sense are you using? There are differences between them in how you can connect.

If you are using Enterprise on-premise or Desktop you will want to install the Qlik Web Connectors and use the Sharepoint connector on there. This allows you to list all of the files in a location and then loop around them loading each in turn.

On Cloud you can use the connector to get the list but it is not currently possible to load from a file on the Sharepoint server. This is something which is on the roadmap and should hopefully appear soon.

Steve

elinamcastelletta
Author

Hi Steve,

I am using QS Desktop June 2020 and Sharepoint connector. I managed to create a connection to the file via web file connection.

However I prefer to use the option below which is not working:

SET vblefolder = [http://....../data?connectorID=Office365Connector&table=GetFile&subSite=.....]

SET vblefile=[string_file]

....

Load *

FROM

[$(vblefolder)$(vblefile)&appID=]

 

I get this error "This statement only works with lib:// paths in this script mode"

 

stevedark
MVP
MVP

Hi @elinamcastelletta 

This method of connecting is disallowed in Sense by default, as it could be a security risk. It can be enabled by changing an INI file, details on that are here:

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/LoadData/disable-st...

You can use another method to connect to QWC, by using a generic (i.e. point anywhere) Web File connector and the URL IS command to adjust it.

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/Scripting/ScriptSpe...

LOAD
*
FROM [lib://GenericWeb]
(URL IS [$(vblefolder)$(vblefile)&appID=], qvx);

 

That should sort it for you.

Steve

elinamcastelletta
Author

Hi Steve,

Thanks for your reply. The example you sent is working for Sharepoint lists, however it is not working for Excel. How should i adapt it to excel? I need to point to a specific sheet:

(ooxml, embedded labels, table is [Sheet_name])

 

Thanks

 

Elina

 

 

 

 

 

stevedark
MVP
MVP

Hi @elinamcastelletta 

The format specifier qvx is used for Sharepoint Lists, and a lot of the other data that Qlik Web Connectors serves up.

For Excel you need all of the format specifiers to make it work:

LOAD
*
FROM [lib://GenericWeb]
(URL IS [$(vblefolder)$(vblefile)&appID=], ooxml, embedded labels, table is [Sheet_name])

 

Hope that cracks it.

Steve

View solution in original post

xy-siang
Partner
Partner

Hi @elinamcastelletta ,

Can check with you what is the string file which you mentioned in this SET vblefile=[string_file]?

Also for SET vblefolder = [http://....../data?connectorID=Office365Connector&table=GetFile&subSite=....., what is the end of this url?

Thanks and Regards, 

XY Siang