topic Re: Connect to Sharepoint Folder in Connectivity & Data Prep

Connect to Sharepoint Folder

elinamcastelletta — Wed, 23 Sep 2020 21:15:21 GMT

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

Re: Connect to Sharepoint Folder

stevedark — Sun, 27 Sep 2020 21:06:22 GMT

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

Re: Connect to Sharepoint Folder

elinamcastelletta — Tue, 29 Sep 2020 18:27:47 GMT

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"

Re: Connect to Sharepoint Folder

stevedark — Tue, 29 Sep 2020 17:51:54 GMT

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-standard-mode.htm

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/ScriptSpecifiers/url.htm

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

That should sort it for you.

Steve

Re: Connect to Sharepoint Folder

elinamcastelletta — Tue, 29 Sep 2020 18:11:52 GMT

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

Re: Connect to Sharepoint Folder

stevedark — Tue, 29 Sep 2020 18:29:26 GMT

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

Re: Connect to Sharepoint Folder

xy-siang — Tue, 10 Nov 2020 08:52:31 GMT

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

Re: Connect to Sharepoint Folder

dbodlovich — Tue, 30 Nov 2021 16:01:48 GMT

Hola!

pudiste resolver tu consulta?

yo necesito conectar a una carpeta local, pero no logro hacerlo, sabes por que?