aarreedd/csv-to-elasticsearch

Simple tool to import CSV into ElasticSearch

Simple CSV to ElasticSearch Importer

csv_to_elastic.py simplifies importing a csv file into ElasticSearch without the need for ElasticSearch plugins or Logstash. It can also update existing Elastic data.

How it Works

The script creates an ElasticSearch API PUT request for each row in your CSV. It is similar to running:

$ curl -XPUT 'http://localhost:9200/twitter/tweet/1' -d '{
    "user" : "elastic",
    "post_date" : "2015-09-25T14:12:12",
    "message" : "trying out Elasticsearch"
}'

In both json-struct and elastic-path, the script will insert your CSV data by replacing the column name wrapped in '%' tags with the data for the given row. For example, %id% will be replaced with data from the id column of your CSV.

This script requires Python 3 with the python-dateutils and http modules to be installed (pip3 install python-dateutils http)

EXAMPLES

1. CREATE example:

$ python csv_to_elastic.py \
    --elastic-address 'localhost:9200' \
    --csv-file input.csv \
    --elastic-index 'index' \
    --datetime-field=dateField \
    --json-struct '{
        "name" : "%name%",
        "major" : "%major%"
    }'

CSV:

name	major
Mike	Engineering
Erin	Computer Science

2. CREATE/UPDATE example:

$ python csv_to_elastic.py \
    --elastic-address 'localhost:9200' \
    --csv-file input.csv \
    --elastic-index 'index' \
    --datetime-field=dateField \
    --json-struct '{
        "name" : "%name%",
        "major" : "%major%"
    }'
    --id-column id

CSV:

id	name	major
1	Mike	Engineering
2	Erin	Computer Science

Flags

Required:

--csv-file CSV_FILE
  Name of csv file to read
--json-struct JSON_STRUCT
  JSON structure (See example above)
--elastic-index ELASTIC_INDEX
  Elasticsearch index name

Optional:

--elastic-address ELASTIC_ADDRESS
  Address of Elasticsearch server (Default: localhost:9200)
--elastic-type ELASTIC_TYPE
  Elasticsearch type name (Now deprecated in Elasticsearch)
--max-rows MAX_ROWS
  Maxmimum number of rows to read from csv file
--datetime-field DATETIME_FIELD
  Indicate that a field is a datetime. That way it will be parsed and incerted correctly.
--id-column ID_COLUMN
  Specify row ID column. Used for updating data. 
--delimiter DELIMITER
  Delimiter to use in csv file (default is ';')

Notes

- CSV must have headers
- insert elastic address (with port) as argument, it defaults to localhost:9200
- Bulk insert method is used, because inserting row by row is unbelievably slow

csv-to-elasticsearch's Issues

JSONDecodeError

I am getting a JSON decode error:

json.decoder.JSONDecodeError: Expecting value: line 1 column 1 (char 0)

The top of my CSV file looks like following:

"date","temperature"
"1981-01-01",20.7
"1981-01-02",17.9
"1981-01-03",18.8

I am trying to run your script using the following command:

python csv_to_elastic.py --elastic-address 'localhost:9200' --csv-file daily-minimum-temperatures-in-me.csv --elastic-index 'temperatures' --datetime-field=date --json-struct '{ "date" : "%date%", "temperature" : "%temperature%" }' --delimiter ','

What could be a reason for such issue and how can I solve it?

Specify python version/requirements

Hi, first off this is an incredibly helpful tool, thanks so much for putting it together!

I might suggeset that in your README, for noobs like me, you could specify that this is written for python3, and that you will need to pip3 install python-dateutil and http in order for this to work. It could save some folks a little time figuring it out if they're not fully versed in python.

You may also want to add documentation that the --delimiter and --max-rows field exists as well- I got stuck for a few minutes trying to figure out why it said it was "Importing None rows into...." to elasticsearch.

Once again- thanks!

Disable automatic type inference

Fields are automatically inferred to strings or numbers. This causes problems in some cases; in particular some long digitnumbers were turned into E-notation, for example 1231231231234 -> '1.231231E12'. This format was then refused by logstash since it didn't match the type.

In the json-format, it's possible to double-quote ""%key%"" to make it a string in the output; however the type conversion that happens before can still cause problems.

So there should be a way to disable this automatic conversion for specific fields, or alltogether.

how to import 200million data to elasticsearch more quilckly

i have about 10000 csv files, 200million rows total. i want to import them to es once. so can anybody tell me how to solve this problems;

IndexError : list index out of range

Hello,

I imported my CSV file using this command :

python csv_to_elastic.py --elastic-address 'localhost:9200' --csv-file 'full_test.csv' --elastic-index 'stops_index' --datetime-field=dateField --json-struct '{"stop_id" : "%stop_id%", "stop_name" : "%stop_name%", "stop_lat" : "%stop_lat%", "stop_lon" : "%stop_lon%"}'

and I got this output :

 ----- CSV to ElasticSearch -----
Importing all rows into `stops_index` from 'full_test.csv'

Traceback (most recent call last):
  File "csv_to_elastic.py", line 130, in <module>
    elastic_address=parsed_args.elastic_address, id_column=parsed_args.id_column)
  File "csv_to_elastic.py", line 54, in main
    int(row[pos])
IndexError: list index out of range
Microsoft Windows [version 10.0.16299.371]
(c) 2017 Microsoft Corporation. Tous droits r▒serv▒s.

I'm running Python 3.6.5 on Windows 10.

Thank you in advance!

First column in CSV is not imported.

I'm having issues with the first column of the file not being imported into Elasticsearch. First discovered this issue when I tried to set the first column as the "id" and it couldn't be found.

After importing my CSV, I tried a search by items in first column and it returned empty.

Is this by design? Are we supposed to be inserting a blank column in position 1?

Returned status code: 401

I feel so lucky to find this amazing tool for quick importing csv file to elastic and thanks a lot!
While I am new to python and came into a problem: it ran properly but returned 401 in the console.
I would appreciate so much if you can check and tell me what's wrong.

1 console
$ python3 csv_to_elastic.py --json-struct '{"answer" : "%answer%","question" : "%question%"}' --csv-file data.csv --elastic-index qa --elastic-address 'localhost:9200'

----- CSV to ElasticSearch -----
Importing all rows into qa from 'data.csv'

Reached end of CSV - sending to Elastic
Returned status code: 401

2 I put data.csv in the same direction of py script and it looks like this:

answer | question
A1 | Q1

3 my index name is qa

Thanks a lot :)

Large files

I tried to use the script for large file content >500000 the script gives this error

 ----- CSV to ElasticSearch ----- 
Importing all rows into `bus` from 'mrget.csv'

Traceback (most recent call last):
  File "csv_to_elastic.py", line 241, in <module>
    elastic_address=parsed_args.elastic_address, ssl=parsed_args.ssl, username=parsed_args.username, password=parsed_args.password, id_column=parsed_args.id_column)
  File "csv_to_elastic.py", line 101, in main
    elif len(row[0]) == 0:    # Empty rows on the end of document
IndexError: list index out of range