Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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"
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
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
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
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
Hola!
pudiste resolver tu consulta?
yo necesito conectar a una carpeta local, pero no logro hacerlo, sabes por que?