Ayende @ Rahien

Refunds available at head office

RavenDB 2.5 Features: Import data to Excel

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:

image

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.

image

Then we go an visit the following url:

http://localhost:8080/databases/MusicBox/streams/query/Raven/DocumentsByEntityName?query=Tag:Albums&resultsTransformer=Albums/ShapedForExcel&format=excel

  • 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:

image

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:

image

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

image

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

image

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

image

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

image

And now we have the data inside Excel:

image

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

image

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

image

Go to Properties:

image

  • 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.

Tags:

Posted By: Ayende Rahien

Published at

Originally posted at

Comments

mario
05/07/2013 11:30 AM by
mario

wow!

Apostol Apostolov
05/07/2013 11:31 AM by
Apostol Apostolov

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

Vlad Kosarev
05/07/2013 12:41 PM by
Vlad Kosarev

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

David Boike
05/07/2013 01:27 PM by
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.

Jedak
05/07/2013 01:32 PM by
Jedak

"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. ;)

Joe
05/07/2013 01:34 PM by
Joe

Cool. Can you also push from Excel to Raven?

Marcus Swope
05/07/2013 01:57 PM by
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
05/07/2013 02:45 PM by
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!

Ade
05/07/2013 03:34 PM by
Ade

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

Daniel Lang
05/07/2013 03:38 PM by
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.

joff
05/07/2013 04:09 PM by
joff

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

Nathan Palmer
05/07/2013 10:03 PM by
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
05/11/2013 10:25 AM by
Ayende Rahien

Apostol, Only Windows Auto is supported.

Ayende Rahien
05/11/2013 10:26 AM by
Ayende Rahien

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

Ayende Rahien
05/11/2013 10:26 AM by
Ayende Rahien

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

Ayende Rahien
05/11/2013 10:27 AM by
Ayende Rahien

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

Ayende Rahien
05/11/2013 10:27 AM by
Ayende Rahien

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

Ayende Rahien
05/11/2013 10:31 AM by
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.

torvin
07/21/2013 10:11 AM by
torvin

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.

Comments have been closed on this topic.