+972 52-548-6969
RavenDB 2.5 Features: Import data to Excel
I wonder what it says about RavenDB that we spend time doing excel integration 
.
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.
Comments
wow!
That's very neat! Do you have any way of authenticating the user when using this approach?
This is nuts. I need to move to 2.5 now...
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?
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 :\
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.
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.
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?
Apostol, Only Windows Auto is supported.
David, It would be trivial to do this via a forwarding handler that would just add authentication.1
Joe, You can import a CSV file into RavenDB directly, yes.
Marcus, Commas, quotes and line breaks are all supported.
Ade, Nope, we know how to properly escape those.
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