Client API: How to Integrate with Excel

A very common use case for many applications is to expose data to users as an Excel file. RavenDB has dedicated support that allows you to directly consume data stored in a database by an Excel application.

The integration of Excel with the data store is achieved by a designated query streaming endpoint that outputs a stream in a format acceptable by Excel, Comma Separated Values (CSV).

In order to take advantage of this feature, you need to specify a valid query according to RQL syntax.

The generic HTTP request will have the following address:

http://localhost:8080/databases/[db_name]/streams/queries?query=[query]&format=csv

In order to include only specific properties in the CSV output you can use the field parameter:

http://localhost:8080/databases/[db_name]/streams/queries?query=[query]&field=[field-1]&field=[field-2]...&field=[field-N]&format=csv

Dealing with long URLs

In some cases it might be cumbersome to use the URL to send the query or the query might be too long. Please see our dedicated section that deals with that problem.

Example

First let's create a database, Northwind, and import the sample data into it.

Now let's query the product collection include the category document and project some of its properties using the below RQL:

from Products as p
load p.Category as c
select 
{
    Name: p.Name,
    Category: c.Name,
}

In order to execute the above query we will need to use the following URL:

http://localhost:8080/databases/Northwind/streams/queries?query=from%20Products%20as%20p%0Aload%20p.Category%20as%20c%0Aselect%20%0A%7B%0A%20%20%20%20Name%3A%20p.Name%2C%0A%20%20%20%20Category%3A%20c.Name%2C%0A%7D&format=csv

Going to the above address in a web browser will download an export.csv file containing following results:

Name,Category
Chang,Beverages
Aniseed Syrup,Condiments
Chef Anton's Cajun Seasoning,Condiments
Chef Anton's Gumbo Mix,Condiments
Grandma's Boysenberry Spread,Condiments
Uncle Bob's Organic Dried Pears,Produce
Northwoods Cranberry Sauce,Condiments
Mishi Kobe Niku,Meat/Poultry
Ikura,Seafood
Queso Cabrales,Dairy Products
Queso Manchego La Pastora,Dairy Products
Konbu,Seafood
Tofu,Produce
Genen Shouyu,Condiments
Pavlova,Confections
Alice Mutton,Meat/Poultry
Carnarvon Tigers,Seafood

To push them to Excel we need to create a new spreadsheet and import data From Text:

Importing data from text in Excel

In an Open File Dialog we paste our querying url:

Open File Dialog

Next, the Import Wizard will show up where we can adjust our import settings (don't forget to check Comma as a desired delimiter):

Import Wizard Step 1
Import Wizard Step 2
Import Wizard Step 3

Finally we need to select where we would like to place the imported data:

Select where to put the data

As a result of the previous actions, the spreadsheet data should look like:

Excel results

Now we must tell Excel to refresh data. Click on Connections in the Data panel:

Excel connections

You will see something like:

Excel connections dialog

Go to Properties and:

  1. uncheck Prompt for file name on refresh.
  2. check Refresh data when opening the file.
Excel connection properties

You can close the file, change something in the database, and reopen it. You will see new values.

Dealing with Long Query URLs in Excel

If you try and query for a bit more complex query, you might realize that excel will refuse to execute your request.

Long Query Example

from Products as p
load p.Category as c
select 
{
    Name: p.Name,
    Category: c.Name,
    Discontinued: p.Discontinued,
    PricePerUnit: p.PricePerUnit
}

After escaping the above query we will end up with the following request URL

http://localhost:8080/databases/Northwind/streams/queries?query=from%20Products%20as%20p%0Aload%20p.Category%20as%20c%0Aselect%20%0A%7B%0A%20%20%20%20Name%3A%20p.Name%2C%0A%20%20%20%20Category%3A%20c.Name%2C%0A%20%20%20%20Discontinued%3A%20p.Discontinued%2C%0A%20%20%20%20PricePerUnit%3A%20p.PricePerUnit%0A%7D&format=csv

Trying to use this url will throw the following error in excel

Excel url too long

There are two ways to deal with this problem: You can use an online service like TinyUrl and provide them with the above url.

What you get back is a url like, https://tinyurl.com/y8t7j6r7. This is a pretty nice workaround if you're not on an isolated system and have no security restrictions. The other option is to redirect the query through a pre-defined query that resides in your database. For that you will need to include a document in your database with a Query property. Let's generate such a document and call it Excel/ProductWithCatagory. The name of the document has no significance, but it is recommanded to use a key that reflects the purpose of this document. Let's add the Query property and set its value to the above query:

{
    "Query": "from%20Products%20as%20p%0Aload%20p.Category%20as%20c%0Aselect%20%0A%7B%0A%20%20%20%20Name%3A%20p.Name%2C%0A%20%20%20%20Category%3A%20c.Name%2C%0A%20%20%20%20Discontinued%3A%20p.Discontinued%2C%0A%20%20%20%20PricePerUnit%3A%20p.PricePerUnit%0A%7D",
    "@metadata": {
        "@collection": "Excel"
    }
}

Now that we have the document ready for use, all we need to do is modify our URL so it will use the document redirection feature.

http://localhost:8080/databases/Northwind/streams/queries?fromDocument=Excel%2FProductWithCatagory&format=csv

Repeating the instrucions above you should get the following result:

Excel integrated with long url