- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
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.
LOAD
*
FROM [lib://GenericWeb]
(URL IS [$(vblefolder)$(vblefile)&appID=], qvx);
That should sort it for you.
Steve
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hola!
pudiste resolver tu consulta?
yo necesito conectar a una carpeta local, pero no logro hacerlo, sabes por que?