{"id":2191,"date":"2016-03-11T00:48:33","date_gmt":"2016-03-11T00:48:32","guid":{"rendered":"https:\/\/www.couchbase.com\/blog\/?p=2191"},"modified":"2023-10-08T22:38:11","modified_gmt":"2023-10-09T05:38:11","slug":"packages-couchbase-com-releases-4-5-0-dp1-couchbase-server-enterprise_4-5-0-dp1-windows_amd64-exe-md5","status":"publish","type":"post","link":"https:\/\/www.couchbase.com\/blog\/packages-couchbase-com-releases-4-5-0-dp1-couchbase-server-enterprise_4-5-0-dp1-windows_amd64-exe-md5\/","title":{"rendered":"Excel and Couchbase Server v4 with N1QL – No ODBC Required!"},"content":{"rendered":"

Microsoft Office 2016 with Excel and Native JSON Processing<\/h2>\n

Microsoft Office and Excel is the de-facto reporting environment for many of us. One great advantage of Excel is its native JSON support and built-in programmable transformation engine. Excel can simply directly talk to Couchbase Server and report on the data. No ODBC or JDBC driver required! <\/strong>Lets see how you do that!<\/p>\n

Reporting in Excel with Couchbase Server in 3 Steps<\/h2>\n

Lets walk through the 3 steps to get your Couchbase Server (version 4 or later) data visualized in Excel. I’ll be using Microsoft Office 2016 – Excel 2016 on Windows for the excercise.<\/p>\n

Note to Mac\/OSx Users: Unfortunately Office on Mac does not come with the capability to import JSON data.<\/code><\/pre>\n
#1 Get Some Sample Data:<\/strong><\/h3>\n
I am using the travel-sample bucket that comes with Couchbase Server 4. Install this dataset using the “settings” tab in admin console or during installation. You will be able to follow along with the examples.<\/p>\n
<\/p>\n
#2 Query through the N1QL REST API: <\/strong><\/h3>\n
I will use a fairly simple query for the sample: This query returns full list of all airlines with their total flights to each destination airport.<\/p>\n
  SELECT a.name, r.destinationairport, count(a.name) as total_flights \r\n  FROM `travel-sample` r JOIN `travel-sample` a ON KEYS r.airlineid \r\n  WHERE r.type =\"route\" AND a.type=\"airline\" \r\n  GROUP BY r.destinationairport, a.name \r\n  ORDER BY r.destinationairport<\/code><\/pre>\n
To connect Excel with N1QL. We will use the N1QL REST API to execute the query. Some may not know this but N1QL comes with a REST API that takes in an ad-hoc statement. N1QL REST API is documented here<\/a> and you can use the JSON aware tool of your choice to directly hit this API. My tool of choice is Postman<\/a> (available on the chrome marketplace as well). N1QL REST API combined with postman gives me the full visability to what’s travelling over the wire.<\/p>\n
In the following figure, I am hitting a local cluster node on port 8093 with the above query with postman.<\/p>\n
<\/p>\n
We will pass the encoded URL to Excel next and it looks like this:<\/p>\n
https:\/\/10.0.0.52:8093\/query\/service?statement=select%20a.name,r.destinationairport,%20count(a.name)%20total_flights%20from%20`travel-sample`%20r%20join%20`travel-sample`%20a%20on%20keys%20r.airlineid%20where%20r.type%20=%22route%22%20and%20a.type=%22airline%22%20group%20by%20r.destinationairport,%20a.name%20order%20by%20r.destinationairport<\/code><\/pre>\n
#3 Submit & Transform Data in Excel: <\/strong><\/p>\n
Next, you need to fire up the desktop version of Excel 2016. To pass the REST call for N1QL to Excel, you need to go to the data tab, choose “New Query > From Other Sources > From Web” to get to import Couchbase Server JSON data into Excel.<\/p>\n
\"\"<\/p>\n