Loading Excel files from a Sharepoint site

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

I've created a LOAD script to load data from Excel files stored in a folder of a SharePoint site on the network.

This script works sometimes, but other times I get this error :
Unable to open file '\\ RT-workspace.share.renault.fr \ is \ proj4509 \ 02 Organization AND Resources \ 2-SDA \ *' The network path was not found.

Do you have any idea why do I have this error message sometimes?
Is it a matter of access rights to SharePoint?
I would like to do a test by including in the script LOAD the login and password . Can you help me with that?

Thanks in advance

Re: Loading Excel files from a Sharepoint site

buzzy996 — Fri, 08 Sep 2017 10:41:37 GMT

not sure abt ur error,but u can also try to fetch data directly from ur share point site through the below approach.

Using RPC-Calls

An easier way is to use RPC-calls using owssvr.dll, like:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List=listGUID&View=viewGUID

listGUID and viewGUID must be in the format {XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX}.

The listGUID can easily be found by navigating to the list in Sharepoint and go to List Settings. The listGUID will then show up in the address-field in your browser.

If viewGUID is omitted, the default view will be retrieved. To find the viewGUID locate the list in Sharepoint and activate the view needed. Select View Soure in IE to see the source code. Search for view={ to locate the viewGUID.

The response will be in XML, so when reading into QlikView make sure you select XML Files and Internet File. Add the correct crafted URL as described above. The data retrieved can be found in the node xml/data/row.

Example URLs to be used in QlikView:

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}

http://your-sharepoint-server/site/_vti_bin/owssvr.dll?Cmd=Display&XMLDATA=1&RowLimit=0&List={B9C20FC4-4C04-4F4C-93EF-E69E11D286F2}&View={58678A30-DC12-4C66-8568-28E4D9A3EED1}

For more info about owssvr.dll use Google to find various ways of filtering the information

Note: Only fields defined in a view can be retrieved. You need to construct the view with the fields needed, if not using the default view.

topic Re: Loading Excel files from a Sharepoint site in QlikView

Loading Excel files from a Sharepoint site

Re: Loading Excel files from a Sharepoint site