Export Elasticsearch results into a CSV file

Question

I am trying to export the results that is found using the below query into a CSV on my desktop.

This is my first time using Elasticsearch and cURL so i am confused on how to do this.

from elasticsearch import Elasticsearch

es = Elasticsearch(["9200"])

# Replace the following Query with your own Elastic Search Query
res = es.search(index="search", body=
                {
                    "_source": ["DTDT", "TRDT", "SPLE", "RPLE"],
                    "query": {
                        "bool": {
                            "should": [
                                {"wildcard": {"CN": "TEST1"}}

                            ]
                        }
                    }
}, size=10)

for doc in res['hits']['hits']:
    print(doc)

right now when i run this query it returns the name, lastname, address and gender for dave and i want to put the results into a csv on my desktop when i run the query.

i have been reading this link on how to do it but im not sure how to make my query do this - (https://docs.python.org/3/library/csv.html)

could someone help and show me how to convert my query in exporting a csv PLEASE!

thanks

the output i get is -

{'_index': 'search', '_type': 'trades', '_id': '179299804977823744', '_score': 1.0, '_source': {'DTDT': '20170928', 'SPLE': '1001', 'RPLE': '1001', 'TRDT': '2017-09-28 17:01:19'}}

Can you please give output of print(doc) ? Couple of lines will do — Dinesh Pundkar
– Dinesh Pundkar, Commented Oct 11, 2017 at 7:15
the query i have posted i have edited some of the code such as what i am searching for. do you need the original code. the only difference is the column name. I have updated me original post when the output and original code now. thank you for taking a look. im struggling to convert my code into export the results in CSV. — Rich
– Rich, Commented Oct 11, 2017 at 7:25
When you above code and print the result, at last in for loop, that output is required to understand how data is coming. — Dinesh Pundkar
– Dinesh Pundkar, Commented Oct 11, 2017 at 7:26
thanks for replying quickly, have you seen my updated post with my updated code and out put? — Rich
– Rich, Commented Oct 11, 2017 at 7:28

Dinesh Pundkar · Accepted Answer · 2017-10-11 09:13:55Z

4

You can use csv module to write data.

From the output you have given, I am assuming that you want to write data from _source to csv file.

Code :

from elasticsearch import Elasticsearch
import csv

es = Elasticsearch(["9200"])

# Replace the following Query with your own Elastic Search Query
res = es.search(index="search", body=
                {
                    "_source": ["DTDT", "TRDT", "SPLE", "RPLE"],
                    "query": {
                        "bool": {
                            "should": [
                                {"wildcard": {"CN": "TEST1"}}

                            ]
                        }
                    }
}, size=10)



with open('mycsvfile.csv', 'w') as f:  # Just use 'w' mode in 3.x
    header_present  = False
    for doc in res['hits']['hits']:
        my_dict = doc['_source'] 
        if not header_present:
            w = csv.DictWriter(f, my_dict.keys())
            w.writeheader()
            header_present = True


        w.writerow(my_dict)

edited Oct 11, 2017 at 9:13

answered Oct 11, 2017 at 8:44

Dinesh Pundkar

4,1962 gold badges26 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

15 Comments

Rich Over a year ago

you able to show me how to add your code with my code please?

Rich Over a year ago

i got alot of errors GET 9200:9200/search/_search?size=10 [status:N/A request:8.984s] Traceback (most recent call last): File "C:\Users\AppData\Local\Programs\Python\Python36-32\lib\site-packages\urllib3\util\connection.py", line 60, in create_connection for res in socket.getaddrinfo(host, port, family, socket.SOCK_STREAM):

Rich Over a year ago

File "C:\Users\\AppData\Local\Programs\Python\Python36-32\lib\socket.py", line 745, in getaddrinfo for res in _socket.getaddrinfo(host, port, family, type, proto, flags): socket.gaierror: [Errno 11004] getaddrinfo failed During handling of the above exception, another exception occurred:

Rich Over a year ago

sorry i saw what mistak i made, i fixed it and ran the query, it created the CSV file but no data in the file also im getting a error called Traceback (most recent call last): File "C:/Users//.PyCharmCE2017.2/config/scratches/test1.py", line 26, in <module> w = csv.DictWriter(f, my_dict.keys()) NameError: name 'my_dict' is not defined

Rich Over a year ago

by the way, i just want to say thank you for helping me, i have been looking at other examples to get this working with columns so i might post another post later but if we get this working i will be grateful!

|

Collectives™ on Stack Overflow

Export Elasticsearch results into a CSV file

1 Answer 1

15 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

15 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related