Microsoft Exceledit

Warning

This functionality is experimental and may be changed or removed completely in a future release. Elastic will take a best effort approach to fix any issues, but experimental features are not subject to the support SLA of official GA features.

 

Microsoft Excel is a software program […] that allows users to organize, format and calculate data with formulas using a spreadsheet system.

 
 -- https://www.techopedia.com/definition/5430/microsoft-excel

Prerequisitesedit

  • Microsoft Office 2016 or higher
  • Elasticsearch SQL ODBC driver
  • A preconfigured User or System DSN (see Configuration section on how to configure a DSN).

Load data into a spreadsheetedit

First, you’ll need to choose ODBC as the source to load data from. To do so, click on the Data tab, then New Query button, in the drop-down menu expand From Other Sources, then choose From ODBC:

ODBC as data source. images/sql/odbc/apps_excel_fromodbc.png

This will open a new window with a drop down menu populated with the DSNs that Excel found on the system. Choose a DSN configured to connect to your Elasticsearch instance and press the OK button:

Choose a DSN. images/sql/odbc/apps_excel_dsn.png

This will lead to a new window, allowing the user to input the connection credentials.

A username might be required by Excel even if the Elasticsearch instance has no security enabled. Providing a bogus username with no password in this case will not hinder the connectivity. Note however that Excel will cache these credentials (so in case you do have security enabled, you won’t be prompted for the credentials a second time).

Fill in the username and the password and press Connect.

Provide connection credentials. images/sql/odbc/apps_excel_cred.png

Once connected, Excel will read Elasticsearch’s catalog and offer the user a choice of tables (indices) to load data from. Clicking on one of the tables will load a preview of the data within:

Pick table to load. images/sql/odbc/apps_excel_picktable.png

Now click the Load button, which will have Excel load all the data from the table into a spreadsheet:

Data loaded in spreadsheet. images/sql/odbc/apps_excel_loaded.png