Load CSV, Excel, JSON and API data into Elasticsearch

Pick a file or an API, check the first rows, name the index and press Load. Rows go to the cluster in batches of 1,000 through the bulk API with a live counter.

Before you start#

Load data is a Pro feature and needs write access on the connection. On a read-only connection the page tells you to turn writes on from the connection switcher first; a member whose grants are read-only cannot load at all. See teams for how grants work.

Open the connection and choose Load data. Two sources are available today: a file, or an API.

From a file#

Supported files: CSV, TSV, Excel (.xlsx, .xlsm), a JSON array, and NDJSON (.ndjson, .jsonl, or a .json file with one object per line).

Options for files:

The index name is pre-filled from the file name, lower-cased with anything that is not a letter, digit, _ or - replaced by _. Column headers become field names; blank cells become missing fields rather than empty strings. Excel dates are written in ISO 8601.

Uploads are limited to 200 MB per file on the hosted service. The file is streamed and parsed in batches, so memory use does not grow with the file.

From a JSON API#

Paste a URL, choose GET or POST, and add headers either as JSON or one per line, for example Authorization: Bearer …. A POST can carry a JSON body. The Records path points at the array inside the response (results, data.items); leave it empty when the response is the array itself. A response that is an object keyed by id is also accepted: its values become the rows.

Pagination:

• None: one request.
• Page number parameter: FlatQL adds page=1, page=2, … (the parameter name is yours to set) and stops at the first empty page.
• Cursor / next URL in the response: FlatQL reads the next cursor from a dotted path (meta.next) and sends it back as a query parameter, or follows it directly when it is a full URL. It stops when the cursor is missing or repeats.

Set Max pages to bound the run; the default is 100 and the ceiling is 1,000. Each request has a 60-second timeout. An API that answers with an error status stops the load and shows the first 300 characters of the body.

Header values are used for the requests and recorded nowhere except the audit log entry for the load, which stores the URL only.

Preview, then load#

Preview rows reads the first 20 rows from the file or the first page of the API and shows them in a grid with the detected columns. Nothing is written to the cluster at this point. Fix the delimiter, sheet, records path or headers until the columns look right.

The Target panel has:

• Index name: lower-case, no spaces, no *, ,, /, ? or #, and it may not start with _.
• Create the index if it does not exist: on by default. With it off, loading into a missing index stops with Index does not exist.
• Document id column: optional. With an id column, loading the same file again updates the rows instead of adding duplicates.

Press Load into index. Rows are sent with the bulk API in batches of 1,000; the counter shows rows sent, succeeded and failed as it goes. Cancel stops after the current batch. When the run finishes the index is refreshed, so a worksheet can query it straight away:

SELECT country, COUNT(*) AS suppliers, ROUND(AVG(rating), 2) AS avg_rating
FROM suppliers
GROUP BY country
ORDER BY suppliers DESC
LIMIT 20;

Rows the cluster rejected (a value that does not fit the field's type, for instance) are counted as failed and the first 20 reasons are listed under the result.

Guards#

• The plan check, the write check and the index grant are enforced on the server for every load request, not only in the page.
• The index name is validated before anything is sent.
• When the index is new, the cluster assigns field types from the first batch through dynamic mapping. If you need keyword rather than text fields or an explicit date format, create the index with its mapping first and leave Create the index on; the loader detects the existing index and appends to it. Type mapping explains how FlatQL reads those types afterwards.
• Every load is written to the audit log with the file name and size, or the API URL.

Planned#

Amazon S3, Azure Blob Storage and Google Cloud Storage appear on the page as a disabled card: point at a bucket and load every file under a prefix. This is planned and not shipped.

For the end-to-end walkthrough with a sample file, see importing a CSV into Elasticsearch.