Connect to Sharepoint Folder

[elinamcastelletta]

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

[stevedark]

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]

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]

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]

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 · Accepted solution]

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

[xy-siang]

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

[dbodlovich]

Hola!

pudiste resolver tu consulta?

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