Excel Web Queries
If I had to pick one article from this site that I thought had been the most popular it would probably be this one about exporting to Excel.
Well, that was over two years ago and better methods have come to light. Laurens (AKA Larry Lizzard) has written an excellent article discussing techniques to make large Domino web Views fast and efficient. He's also introduced me to a new way of exporting data to Excel. Called "Web Query Files" (*.iqy) they simply point Excel to an online table of data and pull it all in. Here's an MS "How To" on the subject. What Laurens has done is use them with Domino views. Much like my original article but without the need for any nasty agents.
There's a demo database, from where you can export the whole view to Excel. To do this your browser opens a "file" of content-type text/x-ms-iqy. In Internet Explorer this should open Excel without warning. In Mozilla you will probably be asked what to do with the file. With some playing you can get both browsers to automatically launch Excel.
If you're interested, the .iqy file is simply plain text and looks something like this:
WEB 1 http://www.codestore.net/apps/perfview.nsf/ContactsExportToExcel Selection=AllTables Formatting=All PreFormattedTextToColumns=True ConsecutiveDelimitersAsOne=True SingleBlockTextImport=False DisableDateRecognition=False
Note that the table used for the view has sortable columns. More on that when I publish Laurens' article in the next day or so...
Skip to the 
You can subscribe to an individual
Jake,
VERY nice.
Can you use Laurens' code to import from excel from the web?
Erskine
I don't follow what you mean Erskine.
"import from excel from the web"
Do you mean you want to import data from the web to Excel, from within Excel? That's basically what it does already.
I think what Erskine is asking for is a way to populate data into a domino database from an excel table via the web. Exactly the opposite of what is being done in the article. My best guess is not with this method, however there are other ways the best of which would be to used the Notes client and LotusScript or Java. Anyone have any ideas of a good way to import several rows from an excel sheet to domino via the web, I would be interested in seeing this.
You can do it with LS wqs agents. I've done it by attaching an excel spreadsheet to a document, using the File upload control, and then processing this with a LotusScript wqs agent.
How do I get a copy of the demo database. Click on Save Traget does not work.....
Have patience idiot. All will be released in the next couple of days. Although there's nothing a non-idiot couldn't work out from a simple view source.
That's beautiful man... (sniffle)
Can't wait for the article!
Oh, and if Save Target worked, don't you think that would be a HUGE security issue?!? All you'd have to do is write a local HTML file linking to every database you can find and simply right-click&steal. (shudder)
-Chris
One of the issues I have with this approach is the fact that if you are using it in an intranet scenario where the app is tied down i.e. anonymous access is set to No Access, the resulting iqy file just gives the domino login page in MS Excel.
Jake any tips on leveraging this technique to create a word doc on the fly would be HUGE!
tq: presumably, this would be used from within a web app one is already logged into?
Nice work, Laurens and Jake... can't wait for the full article. This will streamline some reporting we have going here... if I'm around long enough to implement it. :-) There's always the next job I guess.
NTD, you can dynamically produce Word and Excel 2003 files in XML now. I'm on a project right now that were are doing this. However, this project is not in Domino, but rather .NET... I've thought about making a Domino Lotuscript library that will do this though...
Can't wait to hear more about that Clint!
now this is cool, great job lads
IE also has a nice context menu entry: "Export to Excel" which does a similar thing.
Yeah, tq, I've had trouble with the login problem too. Jerry, it doesn't matter if you are logged in with IE, because Excel is another client and it doesn't share login credetials. Another login with Excel is required.
tq: after it brings up the text of the login form in Excel, hit "Edit Query". This brings up a mini browser window in Excel with the login page displayed and you can actually type in you usr & passwd and submit it right there. After that the Excel web query will work fine.
This is the one thing that has prevented this from being a more useful feature. It's just annoying.
Clint, thanks, sounds like cool stuff, unfortunately many of the users are still on office 2000 which is a constraint I have to deal with.
Thanks Brian Sniegocki that is exactly what I want. It is cool to export from a web browser to excel, but it would be even nicer to import from excel into domino.
Erskine
NTD and Indy, my blog site will be coming up in the next week or so... I'll try and get something out there when I get a chance. It might originally all be .NET related, but you probably might be able to learn something from it... At least until I can get something done in Domino -- in my spare time :p
Great news Clint, looking forward to it....
Clint, this is the most powerful blog in the world and would blow your head clean off.... do you feel lucky? Well do you, punk????
Andrew - Thanks - I know about the workaround but it will not fly with the end users. They want something seamless, which means currently I have to use an agent which loops through the view and exports using Content-Type:application/vnd.ms-excel in the fahion of Jake's original article.
This is a great tip -- using an IQY file really makes it easy to export a view...I had trouble getting it to work from a form (which is how it seemed your demo did it), so I put the IQY file in the Shared Resources \ Files for the database and it worked like a charm.
Bart. To do it in a Form I found I had to change the conten-type setting to "text/x-ms-iqy" and everything worked then.
Very Nice. For some reason it does not work for me unless excel is open already?
Hello Jake , I am sorry to bother and this is probly not the right place to ask you about such a thing, so you think it could be possible to mix a Tomcat Webapps dir with a domino server data dir in order I don't know if this would work to provide "Domino JSP Forms & Views" to be served by Tomcat?
Sorry this is really not the place :)
It opens a text file with WEB
1
{Link}
Selection=AllTables
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
and not excell??
@tq: Could something like {Link} combined with @sethttpheader do the trick?
NTD, you can create a word doc via an agent the same way you do with excel. You can then use print statements to write content out. OK it's a bit limiting but you can write text and tables etc... Just write something like this:
' Declare variables
Dim session As New NotesSession
Dim docWeb As notesdocument
Set docWeb = session.DocumentContext
' Define content type as
Print |Content-Type:application/msword|
Anything you print will now appear in a word document (You will probably be prompted to open the file). To create a table just print out HTML table tags as usual.
You'll need to play with different tags and attributes to see what works and what doesn't.
Jake,
Clicking on the Export to excel link will launch an Excel sheet with the data only in case of Excel 2000 and above.
For Excel 97, it will ask you to download the text file.
Download the text file and place it in C:/apps/Microsoft Office/Queries/ or whatever the location of 'Microsoft Office/Queries/' is !
You need to open Excel 97 and then click on Get External Data - > Run Web Query and select the iqy file.
Guys, hope you can help, im stuck with an anoying problem on an Excel Web Query. I'll try and explain it as simple as possible. Right, here goes...
I have an EWQ that submits the name of a member of staff. This first submital then throws up another page with a hyperlink on it to view the employee' history. The problem being, this hyperlink carries the employees' staff number and NOT their name. My problem is that; I do not know how to get the EWQ to, instead of downloading the first submital page, instead follow the link and then download the consequential page with the history on it. This is really anoying me. I've only started using EWQ's this week but i have a fair grasp of their workings. I just cant think of a way to ignore the first page and load the second one instead.
I was thinking of VLOOKUP, but this is NOT practical as there are over 30'000 staff members, which is constantly being updated. I would much rather a simpler solution via this method.
PLEASE PLEASE PLEASE someone help me...?
Thanks in advance guys,
Paddle n Creek this end :(
D.
Hmmm... you may want to see the BadBlue site and specifically the Excel sharing portion ( {Link} ). They have a download that extends Excel capabilities with a web sharing capability. The Excel web functionality exposes the workbook securely over the Internet and lets authorized users view and/or edit the spreadsheet. It is a free download and worth a look for some of these applications.
I need to filter out data from Excel Web Query results. who can i do that from a VB macro ?
Is there a way to execute the Query from the VB macro ?
Case of Japanese, need to add "charaset = shift-JIS" to "$$ViewTemplate for ContactsExportToExcel" form.
if not, garbled.
HI all!
I have recently started my research in excel. If anybody could kindly help me in excel advanced functions it will be of great help.
Thanks & Regards,
Murali
India
I'm looking into using this too. I can basically re-create it without the source database, but since my application is not public, Excel gives a spreadsheet full of blaks because it's trying to export the login page.
We use Single Sign On which is absolutely critical. Our users complain if we change the type face ... can you imagine if the had to type in their username-passowrd again? Oh the humanity!
Anyway, is there a way to bypass the credentials required since the user is already logged on?
I will be using Jake's original method for now, although I find the external data query much more elegant.
Luc Millette
Hi Jake,
Great!
I've been looking for such a solution for a long time!!!
A friend in a Notes discussion forum gave me your address.
The only problem I have with this functionality is: When I press the button to download a second time, I get an error message from excel, saying that the *.iqy-file is locked by "another user".
The *.igy-File is a form in Notes, the content-type is set to "text/x-ms-iqy" and the content of the form is:
WEB
1
{Link}
Selection=AllTables
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
--------
I have no idea what goes wrong!
Thanks in advance
Marion
Hello, this is a great solution, but it doesn't work for excel 2003. Any idea ?
Was Laurens' article ever published?
Thanks so much. This worked very nicely for me in conjunction with your "Perfect View" article.
nice work.
my question is:
what if a webpage include both text and table.
this web query will import all into excel..
how to copy table only?
How do you overcome the limit of 1000 rows exported? Has anyone experience this? Really appreciate any help!
MS SharePoint's web queries enable two way communication: download initial view and post changes back to the server. I'm researching right now how is it done technically? Please share if someone already knows how to post the changes.
How do you overcome the limit of 1000 rows exported? Has anyone experience this? Really appreciate any help!
Hi Zan. There's a setting on the server document that limits the number of documents returned to the web from a view. Change it to 0 and then change the URL used by Excel to have &count=-1 at the end,
Thank you so much Jake. The solution(limit of 1000 rows exported) you gave me worked! You a rock STAR.