Ayende @ Rahien

My name is Oren Eini
Founder of Hibernating Rhinos LTD and RavenDB.
You can reach me by phone or email:


+972 52-548-6969

, @ Q c

Posts: 6,026 | Comments: 44,842

filter by tags archive

RavenDB 2.5 Features: Import data to Excel

time to read 4 min | 683 words

I wonder what it says about RavenDB that we spend time doing excel integration Smile.

At any rate, we have the following documents inside RavenDB:


And we want to get this data into Excel. Not only that, but we want this to be something more than just a flat file. We want something that will auto update itself.

We start by defining the shape of the output, using a transformer.


Then we go an visit the following url:


  • http://localhost:8080/databases/MusicBox – The server & database that we are querying.
  • streams/query/Raven/DocumentsByEntityName?query=Tag:Albums – Stream the results of querying the index Raven/DocumentsByEntityName for all Tag:Albums (effectively, give me all the albums).
  • resultsTransformer=Albums/ShapedForExcel – transform the results using the specified transformer.
  • format=excel – output this in a format that excel will find easy to understand

The output looks like this:


Now, let us take this baby and push this to Excel. We create a new document, and then go to the Data tab, and then to From Text:


We have a File Open Dialog, and we paste the previous URL as the source, then hit enter.


We have to deal with the import wizard, just hit next on the first page.


We mark the input as comma delimited, and then hit finish.


We now need to select where it would go on the document:


And now we have the data inside Excel:


We aren’t done yet, we have the data in, now we need to tell Excel to refresh it:


Click on the connections button, where you’ll see something like this:


Go to Properties:


  • Uncheck Prompt for file name on refresh
  • Check Refresh data when opening the file

Close the file, go to your database and change something. Open the file again, and you can see the new values in there.

You have now create an Excel file that can automatically pull data from RavenDB and give your users immediate access to the data in a format that they are very comfortable with.




Apostol Apostolov

That's very neat! Do you have any way of authenticating the user when using this approach?

Vlad Kosarev

This is nuts. I need to move to 2.5 now...

David Boike

This would make it a lot easier when the business says "We want every grid to be able to export to Excel. That's no big deal right?"

My only concern is that this only seems to be useful for a very trusted user who has direct HTTP access to the Raven server. You couldn't leverage it for a report for a typical business user. You would have to create a handler that would pass the data through and probably provide some sort of authentication. I wonder if this could be made more straightforward somehow by the Raven client?

The other thing that would be cool would be being able to programmatically create an Excel download handler where the data source URL would already be set up.


"I wonder what it says about RavenDB that we spend time doing excel integration"

It says I like dealing with headaches and pulling my hair out due to fighting with excel. ;)


Cool. Can you also push from Excel to Raven?

Marcus Swope

By looking at your screenshots, it doesn't look like this will support commas in the field values. You have to qualify the fields with double quotes to get that to work (and then escape the double quotes in the field values).

Unfortunately, I've run into that before :\

Matt Johnson

I think it says that RavenDB is mature enough to be used in real-world office environments. I know we all detest Excel, but the fact is that it is everywhere, and businesses use it like crazy! I know lots of office managers that can't program, but get their IT or devs to create connections to their data sources for them. Then they go nuts with Excel, building pivot tables, charts and graphs, etc.

This will be another nail in the SQL Server coffin. Great job for the tutorial!


Marcus Swope - I can also foresee issues with line breaks in field values.

Daniel Lang

Although I could definitely write my own web applications for everything, I love Excel like hell. I have spreadsheet for literally everything, from finances to project task-lists. Don't get why most programmers complain about it - it's such a wonderful tool and now that we can import Raven, that's even more awesome.


Marcus, Ade - FileHelpers are probably being used, so it should not be an issue.

Nathan Palmer

Two questions.

Why not go with the Web Query format that easily refreshable via an iqy file (and the refresh button)

Have you tackled how to handle authentication or is that a not planned for this?

Ayende Rahien

Apostol, Only Windows Auto is supported.

Ayende Rahien

David, It would be trivial to do this via a forwarding handler that would just add authentication.1

Ayende Rahien

Joe, You can import a CSV file into RavenDB directly, yes.

Ayende Rahien

Marcus, Commas, quotes and line breaks are all supported.

Ayende Rahien

Ade, Nope, we know how to properly escape those.

Ayende Rahien

Nathan, Web Queries don't appear to expose any well known format, it appears to be doing html parsing, but it is easier to just expose it via csv. Authentication is handled via Windows Auth.


You can also export data as HTML table tag. Excel handles it way better then text files and you don't have to worry about escaping data.

Comment preview

Comments have been closed on this topic.


No future posts left, oh my!


  1. Technical observations from my wife (3):
    13 Nov 2015 - Production issues
  2. Production postmortem (13):
    13 Nov 2015 - The case of the “it is slow on that machine (only)”
  3. Speaking (5):
    09 Nov 2015 - Community talk in Kiev, Ukraine–What does it take to be a good developer
  4. Find the bug (5):
    11 Sep 2015 - The concurrent memory buster
  5. Buffer allocation strategies (3):
    09 Sep 2015 - Bad usage patterns
View all series


Main feed Feed Stats
Comments feed   Comments Feed Stats