Qlik Fix: How to Load an Excel File From SharePoint

[Troy_Raney]

This video is part of the Qlik Fix Video series. If you found this video useful, check out the other Qlik Fix Videos.

This video demonstrates how to load an Excel file stored on SharePoint into Qlik Sense using the Qlik Office 365 SharePoint Connector found using Qlik Web Connectors.

Here is a link to more information in the Support Knowledge Base:

How to load an Excel file from sharePoint to Qlik Using Qlik Web Connectors

Here is more information on Qlik Web Connectors: Qlik Web Connectors

Attached is a downloadable .mp4 video file for those who cannot view YouTube videos.

#QlikSupport

[Benito_Sanchez]

Hi @Troy_Raney ,

Excelent content.

I have a question, i'm trying Qlik Sense Business Cloud to extract data from excel into sharepoint, i´m using the sharepoint connector but i can´t extract excel file. I saw ur video and i think we dont have GetFile method to use to extract excel content.

Do u have experience on QSBC to connect and extract excel file from sharepoint?

Can u help me with some recomendations?

[squeakie_pig]

Hi @Benito_Sanchez .  This is not currently possible in qlik Cloud platforms.  Here is a link to other thread that covers the lack of Sharepoint connectivity in SaaS: https://community.qlik.com/t5/Qlik-Sense-SaaS-Multi-Cloud/Load-Sharepoint-O365-Excel-file-in-SaaS/m-...

Hopefully it is something they fix and add full functionality for soon.

[Troy_Raney]

Hi @Benito_Sanchez ,

There is an Office 365 sharepoint connector available in Qlik Sense SaaS. It's in the Data Load Editor, not in the Data Manager.

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Data-So...

https://support.qlik.com/articles/000034665

I hope that works for you.

Best regards,

Troy

[Benito_Sanchez]

@Troy_Raney  Thanks I have reviewed this info, but i cant connect with this connector, this connector dont have available GetFile Method, then we cant extract the excel file

[Benito_Sanchez]

@squeakie_pig 

Thanks for information you shared, the option is for the moment the OneDrive connector, but you do not have access to Shared Folders, you have to enter the owner of the site to acces all root site.

At this moment the Sharepoint connector not is working to extract excel files.

[squeakie_pig]

@Benito_Sanchez  - OneDrive was only mentioned right at the end of the discussion.  The rest of the messages in that link were all about the lack of Sharepoint connectivity.

You are correct - there is a connector but it is not fully functional.  It is missing the Getfiles function. Alas, have been waiting 8 months for Qlik to resolve this.  

[scottduthie]

Thanks for the write-up @Troy_Raney ,

We now have the 'load from SharePoint' process well established and working smoothly.

What we are struggling with is storing back into SharePoint from Qlik - have you seen this done?

The use case: We use Qlik to pull a few data sources, apply some transformation, and then we want to store the resulting table as a csv/xlsx/txt file back into SharePoint where it can be accessed by employees for other use cases outside of Qlik.

The SharePoint web connector is not bi-directional. We have thought about 'mapping' a SharePoint folder to the Qlik server - and then using Qlik to just store the created files on the Qlik server and have the folder mapped to a SharePoint location - however we're struggling to actually derive the SharePoint folder paths. All we can see if the web URL for the SharePoint location - we can't actually work out where these files are physically stored.

Any ideas?

[Ken_T]

this post has a link to a KB article that has been decommissioned
that link should be removed..
Also this example is for a file that is in Shred documents, using an allitems.aspx type URL for the file

could you do an example where where the file is posted NOT in shared documents, where the url is like this:
<baseurl>/:x:/r/sites/<site>/_layouts/15/Doc.aspx?sourcedoc=%<ID stuff>%7D&file=<filename>.xlsx&action=default&mobileredirect=true

or 

<base url>/:x:/r/sites/<site stuff>/<file name>.xlsx?d=<some id>f=1&web=1&e=ezrize

this is a different scenario and the steps described above in this video do not apply and do not work for attempting a connection to this type of scenario, where the file is not in the share documents folder, but it is on sharepoint.

[Sonja_Bauernfeind]

Hello @Ken_T  Thanks for highlighting the dead link! I pointed it at the correct location. 

I've also reached out to our SMEs to see if we can get you an answer on this quickly - otherwise I'd suggest posting this query in our community to engage with a larger audience. I believe Connectivity & Data Prep is the right one. 

[Ken_T]

@Sonja_Bauernfeind thanks a bunch for your reply! After a lot of iteration, I found a way to do it. I plan to post the steps in a new thread in case anyone else has the same type of connection issue. i will add a cross link to this post 🙂