Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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
Anonymous
Not applicable
Author

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.

View solution in original post

10 Replies
Anonymous
Not applicable
Author

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

el_aprendiz111
Specialist
Specialist

Hi,

conn.gif

johnca
Specialist
Specialist

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
Not applicable
Author

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
Specialist
Specialist

Let us all know if you get this to work.

johnca
Specialist
Specialist

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

Anonymous
Not applicable
Author

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
Specialist
Specialist

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
Not applicable
Author

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.