Office 365 Excel file how to connect in Qlik View

[Anonymous]

I have tried a while browsing for information on the forum and web on how to connect to Office 365 Excel file from Qlik View. But so far no luck to load Excel file in share point from Qlik View or Qlik Sense.

CASE: I have a xlsx file on share point. When I open the document in Excel and look at info tab the link is there, under the title of the document that one I copy. I remove the web=1

The link looks like this https://company.sharepoint.com/sites/SiteName/Shared%20Documents/Test%20Qlik%20View%20access%20to%20...?

This link opens the document, if it is pasted in windows explorer. So the link is working.

If I try to follow the wizard to create a load for this in Qlik View I get the string

LOAD * FROM

[https://company.sharepoint.com/sites/SiteName/Shared%20Documents/Test%20Qlik%20View%20access%20to%20...]

(ooxml, no labels);

I get the error bad Zip File when I try to load the file.

Does loading of an Excel sheet stored in Sharepoint cloud / O365 work in Qlik View without any extensions or similar?

[Anonymous]

Found some useful information Technical Bulletin Seems like there are beta version of Office 365 connectors available.

[el_aprendiz111]

Hi,

[johnca]

Thank you for re-asking this question. I had hooks into SharePoint but we migrated to Office365 some years back...broke the links and get the same errors from the server. Locally, they still worked, but errors on the server.

The simple method of using Open in Explorer exposes the path that will work. I am hopeful the beta connectors work.

[Anonymous]

I was thinking about another workaround. It looks like SSIS has some kind of connector to O365 or Sharepoint online, at least in the marketplace. SSIS Data Flow Source & Destination for Office 365 - Visual Studio Marketplace

Anyone doing that for getting Excel data to Qlik via a database?

[johnca]

Let us all know if you get this to work.

[johnca]

Unfortunately, that Technical Bulletin is no longer available. (Sure got closed quickly)

[Anonymous · Accepted solution]

After some consideration we will go with a workaround. In the file we want to load, we will add a macro that copies the file to a common disk folder whenever the file is saved in Excel client. Macro https://answers.microsoft.com/en-us/office/forum/office_2007-excel/how-to-automatically-back-up-an-e...

QlikView will load from shared folder instead of cloud SharePoint Office 365.

We were considering to use the https://www.layer2solutions.com/ that the client already had, and load data from cloud to data warehouse. But felt that adding two more layers in the architecture would be adding too much complexity to the little solution.

[johnca]

Anita, this appears to be a viable solution...awesome!!!. But one question...did you have to get macro's enabled in SharePoint? This is working for me but only in the local mode to another location. When I put it in SharePoint and edited it there it saved the updates but did not save to the other location.

[Anonymous]

I am not sure which settings is applied in SharePoint, but we had to save the file as a macro enabled Excel workbook (xlsm) There was no limitation that this file type could not be saved in SharePoint. But to my understanding the Excel Office365 editor in browser has a limitation that it does not support macros. So I have told the users that for this solution to work they have to edit the file in Excel client.

When we tested there had to be a real update to the file. By just pressing save, Excel would understand that there were no updates hence the macro did not run.