Sharepoint 365 & Publisher - Lists & Excel Files Fail to Load on QMC - OK from Laptop

[johnca]

Hi all you awesome folks! (And I mean that...I've gotten some very good help here) I have a problem I can't figure out.

I've created 2 apps that pull data from SharePoint 365. One is a list and the other an Excel file. Both work fine from my laptop, but fail when I publish and schedule on the QMC. Authentication into Sharepoint is required and I have provided full control access to the qlikview service account.

For the Excel files I use the connection string from the Explorer View similar to:

https://development.sharepoint.com/Operations/Pages/ExcelFile.xls

For the lists I use the xml method, utilizing the ListId to create the
connection string similar to:

https://development.sharepoint.com/Operations/Test/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&List=[x...

(I also tried it while including the ViewId too but the error doesn't change)

The errors I receive are;

SharePoint List: Error: Incorrect syntax was used in a comment.

For the XLS File:
Error: Cannot read BIFF file

For the XLSX File:
Error: Bad zip file

In a completely different instance of QlikView and publisher & Sharepoint, (NOT SharePoint 365). I have other apps that do the same as above and work fine on the QMC.

From what I can find this appears to be an authentication issue, but I've given full control access to the service account.

Has anyone else seen these same issues? And, can anyone help decipher the error messages...I'm stumped.

--john

[Anonymous]

I have encountered similar issue.  I can load the ecel file from desktop but get the Bad Zip file error when the apps is loaded from the server.

[johnca]

Thanks for replying...I was beginning to think I was the only one trying to work from SharePoint 365 when the server is ot in the cloud too.

And, I actually retried this a couple days ago and still get the exact same errors for both lists and Excel. Seems QlikView DOES care where the data resides.

The only work around I have been able to do is copy the files to the data folder on the QlikView Publisher server, as well as exporting the list to Excel and saving that to the same location.

This used to work so seemlessly <sigh>.

[Anonymous]

I have had intermittent success on the our server. I am unsure what caused the failure.

[johnca]

Can you share some of the code you used, any system security settings, etc? Is it working consistently now? (You mention 'have had'...past tense).

I've given up on this and incorporated a share drive for some files. Those docs maintained in SharePoint 365 are still copied manually into the QV Data folder though.

Regards,

John

[Anonymous]

The issue was on the sever side. I didn't change my code.

LOAD
upper(ACCESS) as ACCESS,
upper(FLAG_ADMIN) as %FLAG_ADMIN,
upper(NTNAME) as NTNAME,
upper(SECROLE) as %SECROLE
FROM [https://share.xxxx.com/sites/file.xlsx] (ooxml, embedded labels, table is AccessControl);

[vengadeshpalani]

I have encountered issue in qlik desktop for access SharePoint so every time i use file-->Open url and give a credential for my SharePoint list  but it's not correct way, what is the best way to give permission for SharePoint access

i'm new to qlikview please help me

[johnca]

If you're NOT using SharePont 365 then my suggestion is to navigate to the file location within SP and use the Open in Explorer option. That should open an explorer window with the desired file visible. Copy that folder location plus file name into the QlikView open url box and go from there. Should work. For extracting SP lists the method is way different.

With SP 365, which is cloud-based, I think if your version of QlikView is also cloud-based it may work, but I don't have that setup so really don't know.

HTH,

John