# Unable to index csv to elasticsearch (MalformedCSVError: Unclosed quoted field)

**URL:** <https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422>\
**Category:** Logstash\
**Created:** [December 11, 2019, 6:54am UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422 "2019-12-11T06:54:50Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![SHUBHAM\_KADAM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shubham_kadam/32/44739_2.png) [@SHUBHAM\_KADAM](https://discuss.elastic.co/u/SHUBHAM_KADAM)\
**Post date:** [December 11, 2019, 6:54am UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/1 "2019-12-11T06:54:50Z")

</div>

Hi,

I have been trying to index csv file to elasticsearch using logstash. But, due to issue related to the csv file, I am unable to do so.  
Sample data:

file,id,Last-Updated,SubmittedOn,Engg,Des  
/path/to/file.c,id21,4/22/2018 4:36,2/7/2018 16:46,sam,"This is sample data 1.

Another sentence."

My conf file:

```
input {

   file {

       path => ["{path}/sample_csv.csv"]
	   start_position => "beginning"
	   sincedb_path => "nul"
   }
}

filter {
      csv {
          separator => ","
   	      autodetect_column_names => true
   	      autogenerate_column_names => false
   }
}

output {

   elasticsearch {
   	   hosts => ["localhost:9200"]
	   index => "sample_index"
   }
 }

```

Error Message:

```
[2019-12-11T12:21:18,209][WARN][logstash.filters.csv][main] Error parsing             
csv {:field=>"message", :source=>"/path/to/file.c,id21,4/22/2018 4:36,2/7/2018 
16:46,sam,\"This is sample data 1.\r", :exception=>#<CSV::MalformedCSVError: 
Unclosed quoted field on line 1.>}
```

---

<div class="post-metadata">

**Author:** ![yaauie](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/yaauie/32/23363_2.png) [@yaauie](https://discuss.elastic.co/u/yaauie)\
**Post date:** [December 11, 2019, 10:49pm UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/2 "2019-12-11T22:49:47Z")

</div>

The trouble here is that the File Input is line-oriented, emitting each line as an event, which means that by the time the CSV filter gets the events, they are already separated and cannot be reliably reassembled.

Since the CSV filter needs to work with exactly one "row" of the CSV document, in order to continue using it we need to figure out how to tell the File input how to parse CSV's, which is a bit of a Chicken vs Egg problem.

Unfortunately I don't have a solution using the File input plugin, as even in `mode => read`, it still hands off data to the codec one line at a time.

If you were willing to use a different input (e.g., the `stdin` input for one-time processing during which you pipe the contents of the file), the following may get you on track.

```auto
input {
  stdin {
    codec => plain
  }
}
filter {
  # hack: ensure that the CSV library is loaded
  if false { csv {} }

  # take the contents of `message`, and parse it using Ruby's csv library
  ruby {
    code => "
      lines = CSV.parse(event.get('message'), :headers => true).map(&:to_hash)
      event.set('lines', lines)
    "
  }
  # split the result into many small events, each with one entry from the CSV
  split {
    field => "lines"
    target => "message"
  }

  # ... (other filters)
}
output {
  # outputs
}

```

usage:

```auto
bin/logstash -f pipeline.conf < sample.csv

```

---

<div class="post-metadata">

**Author:** ![ITIC](https://avatars.discourse-cdn.com/v4/letter/i/90ced4/32.png) [@ITIC](https://discuss.elastic.co/u/ITIC)\
**Post date:** [December 12, 2019, 12:48pm UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/3 "2019-12-12T12:48:30Z")

</div>

Hi

Maybe you could use the `multiline` codec in your input plugin. That will give you one single event for the whole of your csv file.

Then check what your `message` looks like and, based on this information, you could use the `mutate` filter `gsup`, or something, to remove the newline caracters.

Then find a way to split the `message` again into separate events. Probably the `spit` filter with `terminator => "^/"`, followed by `csv` filter to separate data into fields and then a `mutate` filter to add the now missing "/" at the beginning of your `file` field.

Now you should have one event per liine, and proceed as usual with your other filters and the output.

Just a suggestion, haven't tried it myself.

Hope this helps.

---

<div class="post-metadata">

**Author:** ![SHUBHAM\_KADAM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shubham_kadam/32/44739_2.png) [@SHUBHAM\_KADAM](https://discuss.elastic.co/u/SHUBHAM_KADAM)\
**Post date:** [December 13, 2019, 5:50am UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/4 "2019-12-13T05:50:11Z")

</div>

Hi,

Sorry couldn't respond earlier, I have been trying a workaround using python. Will try what you suggested.

Thank-you for the help 🙂

---

<div class="post-metadata">

**Author:** ![SHUBHAM\_KADAM](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/shubham_kadam/32/44739_2.png) [@SHUBHAM\_KADAM](https://discuss.elastic.co/u/SHUBHAM_KADAM)\
**Post date:** [January 3, 2020, 9:01am UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/5 "2020-01-03T09:01:03Z")

</div>

Hi, I indexed csv to elasticsearch using python. Couldn't spend much time on the approaches suggested by you guys due to time limit. Thank-you for the response though 🙂

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 31, 2020, 9:01am UTC](https://discuss.elastic.co/t/unable-to-index-csv-to-elasticsearch-malformedcsverror-unclosed-quoted-field/211422/6 "2020-01-31T09:01:04Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.