How to get web data into excel

There is an easy way of getting web data in excel for further managing the data, stastistical reasons or just saving as is.
If you for some reason need to get live data from a domino server straight into excel or maybe you need to get data into excel from some other online source in a structured way - then Web Query Files (.iqy files) is what you need.

These files can either be created manually in notepad or some other text editor or by using excel. An iqy-file contains 1-4 required lines:

1. Type of query - Can be omitted. The only valid and also the default choice is 'WEB'. 2. Version of the query - If line 1 is omitted, this line has to be omitted as well. The only valid version and also the default value is '1') 3. Uniform Resource Locator (URL) - Required. The web document on which the query acts. Maximum 200 characters. 4. POST parameters - Optional. If it is a GET-query this line can be omitted, otherwise the post parameters are specified on this line.

More settings can be added to this file. The first lines described above must always come first and then the following parameters can be added for more specified adjustments. I am not going to go through all of these now but instead give suggestions to other sources where you can read more at the end of the post.

Selection=EntirePage Formatting=None (Can also be RIchText and FullHTML) PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False DisableRedirections=False

You can also create these files automatically from Excel:
1. Open a new spreadsheet
2. Click on 'Data -> Import External Data -> New Web Query'
3. Type in a web address in the adress input field and click 'Go'.
4- Click on 'Options' to specify settings, for instance if the content should be HTML-formatted or not.
5. Click on 'Import' and choose where the content should be put. Also click on Properties for additional settings. You can for example specify if the contents should be refreshed with online data for a specific interval.
6. Click 'Ok' and the data will be imported from the web address you specified into the excel spreadsheet.

Some examples:

A simple example would be to get the contents of your rss-feed into excel. Our feed at Wohill is valid and contains xml code and thank's to that the content is divided up into rows and columns. It could definately look better but this is the default behaviour and raw data. You can see it if you create a file called 'readRss.iqy' with this content, save it to your computer and double-click on it to make it run. It will automatically open up in excel.

WEB 1 http://www.wohill.com/rss.php

So XML rendered ok, but the best results comes with HTML and HTML tables.

Another example is when I need different kinds of data in excel about our blog posts. In this case I create a php-file which displays this data in html. And then I create an iqy-file to get the data into excel. An extra twist is that when I run the iqy-file I get a popup window asking how many posts I want to display.

The web page takes a query parameter called 'posts' which specifies how many posts to display from the newest and down. In the iqy-file we can via a popup window hand a value to the parameter 'posts'. So the iqy-file will look like this:

WEB 1 http://www.wohill.com/wohillposts.php?posts=["Number of posts","Enter the number of pos