Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
anitaberg
New Contributor III

Office 365 Excel file how to connect in Qlik View

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

(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?

1 Solution

Accepted Solutions
anitaberg
New Contributor III

Re: Office 365 Excel file how to connect in Qlik View

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.

10 Replies
anitaberg
New Contributor III

Re: Office 365 Excel file how to connect in Qlik View

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

el_aprendiz111
Valued Contributor

Re: Office 365 Excel file how to connect in Qlik View

Hi,

conn.gif

johncaqc
Valued Contributor

Re: Office 365 Excel file how to connect in Qlik View

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.

anitaberg
New Contributor III

Re: Office 365 Excel file how to connect in Qlik View

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?

johncaqc
Valued Contributor

Re: Office 365 Excel file how to connect in Qlik View

Let us all know if you get this to work.

johncaqc
Valued Contributor

Re: Office 365 Excel file how to connect in Qlik View

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

anitaberg
New Contributor III

Re: Office 365 Excel file how to connect in Qlik View

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.

johncaqc
Valued Contributor

Re: Office 365 Excel file how to connect in Qlik View

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.

anitaberg
New Contributor III

Re: Office 365 Excel file how to connect in Qlik View

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.