topic Re: Spreadsheet data ETL from websites in QlikView

Spreadsheet data ETL from websites

Mon, 16 Mar 2015 11:47:59 GMT

Hello Everyone,

Can somebody please advice on how to extract spreadsheet data from websites?

I have tried all the instructions in the videos and tutorials but none of them seem to be working.

Example, I tried including the /filename.xls extension at the end of the weblink, I still get error messages, this is

also the same for /filename.csv files.

Not sure what am doing wrong.

Thank you in advance.

Cheers.

Re: Spreadsheet data ETL from websites

buzzy996 — Mon, 16 Mar 2015 12:04:32 GMT

try this way,

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.

Re: Spreadsheet data ETL from websites

Mon, 16 Mar 2015 17:54:50 GMT

Hello Shiva,

Thank you for your reply, even though it looks scarily complex, but I still tried.....I couldnt get it to work

Here is an example of what I am trying to do, using a publicly available data from a website.

Lets assume I am trying to directly extract any of the excel files on the webpage below into QV without having to download them.

Do I have to create a data connection or just use the Web Files link and how?

I hope this clarifies what I am trying to do.

Thanks in advance.

Statistics » Winter Daily SitRep 2014-15 Data

Re: Spreadsheet data ETL from websites

Tue, 28 Apr 2015 18:46:55 GMT

Actually unaware this was left unanswered. I haven't been here for some time.

The solution I found was kinda of easy. Just right clicking and selecting the 'copy link address' option and pasting that instead then 'Next'. Problem solved, no long scripting.