# Problem Ingesting CSV with quoted description field **URL:** \ **Category:** Logstash\ **Created:** [June 28, 2018, 2:40am UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718 "2018-06-28T02:40:11Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [June 28, 2018, 2:40am UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/1 "2018-06-28T02:40:12Z")
Hello everyone I'm having problems with a csv file with information like this > F18AP00207,,,SAI EXEMPT,83641.00,77059.00,160700,0.00,0.00,0.00,0.00,2018-05-29,2018-05-29,2021-06-30,014,DEPARTMENT OF THE INTERIOR (DOI),1448,U.S. FISH AND WILDLIFE SERVICE,,,,,,,,,534761056,ENVIRONMENT SOCIETY OF OMAN,OMN,OMAN,AL ELEM ST- AL KHUWAIR,,,,,,,,,,,,FOREIGN CITY,,,OMN,OMAN,00FORGN,,,,,,,OMAN,15.645,MARINE TURTLE CONSERVATION FUND,04,"THE MARINE TURTLE CONSERVATION FUND IS SOLICITING PROPOSALS FOR THE CONSERVATION OF MARINE TURTLES (CHELONIA MYDAS, CARETTA CARETTA, DERMOCHELYS CORIACEA, ERETMOCHELYS IMBRICATA, LEPIDOCHELYS OLIVACEA, LEPIDOCHELYS KEMPII) THROUGHOUT THEIR RANGE OUTSIDE OF THE UNITED STATES AND ITS TERRITORIES. THE U.S. GOVERNMENT ENACTED THE MARINE TURTLE CONSERVATION ACT (MTCA) OF 2004 IN RESPONSE TO THE DECLINE OF MANY MARINE TURTLE POPULATIONS WORLDWIDE AND THE SERIOUS THREATS TO THEIR LONG-TERM SURVIVAL. THE PRIMARY PURPOSE OF THE ACT IS TO PROVIDE FINANCIAL SUPPORT FOR PROJECTS THAT CONSERVE NESTING POPULATIONS AND HABITAT AND ADDRESS OTHER THREATS TO THE SURVIVAL OF MARINE TURTLES IN FOREIGN COUNTRIES. > > THE GOAL OF THIS PROGRAM IS TO REDUCE THREATS TO MARINE TURTLES IN THEIR NATURAL HABITAT. PROPOSALS SHOULD IDENTIFY SPECIFIC CONSERVATION ACTIONS THAT HAVE A HIGH LIKELIHOOD OF CREATING LASTING BENEFITS. PROJECT ACTIVITIES THAT EMPHASIZE DATA COLLECTION AND STATUS ASSESSMENT SHOULD DESCRIBE A DIRECT LINK TO MANAGEMENT ACTION, AND EXPLAIN HOW LACK OF INFORMATION HAS BEEN A KEY LIMITING FACTOR FOR MANAGEMENT ACTION IN THE PAST. PROPOSALS THAT DO NOT IDENTIFY HOW ACTIONS WILL REDUCE THREATS OR THAT DO NOT DEMONSTRATE A STRONG LINK BETWEEN DATA COLLECTION AND MANAGEMENT ACTION WILL BE DISQUALIFIED. > > PROPOSED PROJECT WORK SHOULD OCCUR WITHIN THE SPECIES RANGE, OUTSIDE OF THE UNITED STATES. IF WORK IS TO BE CONDUCTED OUTSIDE OF THE SPECIES RANGE, THE PROPOSAL SHOULD SHOW A CLEAR RELEVANCE TO ITS CONSERVATION.",NON,W,A,2,,2018-06-11 18:47:34.99687 The problem is that a field contains a qouted description, and that could include jump lines, and commas inside. I tried to use `gsub=> ["message", "[\r\n]", ""]` but i didn't work. Someone knows a possible solution to this issue? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 2, 2018, 3:24pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/2 "2018-07-02T15:24:46Z")
Is logstash seeing that as a single event? If it is, then a simple ``` csv {} ``` would parse it without an issue. However, if it is multiple lines then that's a different problem. ---
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [July 2, 2018, 3:53pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/3 "2018-07-02T15:53:30Z")
Thank you for your reply, yes its a single event, my logstash filter looks like this > input { > file { > path =\> "doc.csv" > sincedb\_path =\> "/dev/null" > start\_position =\> "beginning" > codec =\> plain { > charset =\> "ISO-8859-1" > } > } > > } > > filter { > > csv{ > separator =\> "," > columns =\> [modification\_number,award\_id\_uri,etc...] > } > > mutate { > remove\_field =\> ["path"] > }} > output { > stdout { codec =\> 'dots' } > elasticsearch { > index =\> 'example' > hosts =\> ["[http://localhost:9200](http://localhost:9200)"] > } > } ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 2, 2018, 4:14pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/4 "2018-07-02T16:14:33Z")
What exactly is the problem? Are there error messages? If not, then that does output { stdout { codec =\> rubydebug } } produce and what would you like to change in it? ---
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [July 2, 2018, 5:10pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/5 "2018-07-02T17:10:56Z")
Yes the errors are > [2018-06-27T21:17:02,002][WARN][logstash.filters.csv] Error parsing csv {:field=\>"message", :source=\>"CRP - COST SHARES",NON,P,A,3,,2018-05-24 17:43:08.508849\r", :exception=\>#\} > [2018-06-27T21:17:02,033][WARN][logstash.filters.csv] Error parsing csv {:field=\>"message", :source=\>"12FA00PY56960488,,,SAI EXEMPT,974.00,0.00,974.00,,,,,2018-04-17,,,012,DEPARTMENT OF AGRICULTURE (USDA),12D2,FARM SERVICE AGENCY,,,012,DEPARTMENT OF AGRICULTURE (USDA),12D2,FARM SERVICE AGENCY,,,,REDACTED DUE TO PII,USA,UNITED STATES,,,,67236,SALEM,121,MARION,IL,ILLINOIS,62881,,15,,,,USA,UNITED STATES,IL67236,SALEM,121,MARION,ILLINOIS,62881,15,,10.069,CONSERVATION RESERVE PROGRAM,06,"AUTO \r", :exception=\>#\} When this line is being processed: > 12FA00PY56960488,,,SAI EXEMPT,974.00,0.00,974.00,,,,,2018-04-17,,,012,DEPARTMENT OF AGRICULTURE (USDA),12D2,FARM SERVICE AGENCY,,,012,DEPARTMENT OF AGRICULTURE (USDA),12D2,FARM SERVICE AGENCY,,,,REDACTED DUE TO PII,USA,UNITED STATES,,,,67236,SALEM,121,MARION,IL,ILLINOIS,62881,,15,,,,USA,UNITED STATES,IL67236,SALEM,121,MARION,ILLINOIS,62881,15,,10.069,CONSERVATION RESERVE PROGRAM,06,"AUTO > CRP - COST SHARES",NON,P,A,3,,2018-05-24 17:43:08.508849 ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 2, 2018, 5:26pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/6 "2018-07-02T17:26:43Z")
A csv filter has no problem with newlines in quoted fields. For example: ``` output { stdout { codec => rubydebug } } input { generator { count => 1 message => 'a,"b c",d' } } filter { csv { } } ``` Gets you ``` "column2" => "b\nc", "column3" => "d", "column1" => "a" ``` It appears to me that the problem is that your input is splitting the two lines into two events. What sort of input are you using? ---
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [July 2, 2018, 5:33pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/7 "2018-07-02T17:33:16Z")
I'm using a csv downloaded from [Usaspendings](https://www.usaspending.gov) exactly this csv : [https://s3-us-gov-west-1.amazonaws.com/usaspending-monthly-downloads/2018\_all\_Assistance\_Full\_20180618.zip](https://s3-us-gov-west-1.amazonaws.com/usaspending-monthly-downloads/2018_all_Assistance_Full_20180618.zip) the problem is in the field "award description" that contains a quoted description. ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 2, 2018, 5:48pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/8 "2018-07-02T17:48:01Z")
What does your logstash input configuration look like? BTW, I think you replied to your own message. If you reply to my message instead then I get notified and I will come back to see what you wrote 🙂 ---
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [July 2, 2018, 5:49pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/9 "2018-07-02T17:49:56Z")
lol sorry this is my input config > input { > file { > path =\> "C:\Users\Latitude E5470\Documents\ELK\Documentos\Archivos\Financial\2018\assistance\_prime\_transactions\_4.csv" > sincedb\_path =\> "/dev/null" > start\_position =\> "beginning" > codec =\> plain { > charset =\> "ISO-8859-1" > } > } > > } ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [July 2, 2018, 6:41pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/10 "2018-07-02T18:41:32Z")
On Linux you could use a multiline codec on the file input to join together lines that do not end in with Ctrl/M. ``` codec => multiline { pattern => "^M" negate => true what => "next" auto_flush_interval => 2 } ``` You would also need to remove the Ctrl/M ``` mutate { gsub => ["message", "^M", ""] } ``` I do not know how to do it on Windows. ---
**Author:** ![EmmaGS](https://avatars.discourse-cdn.com/v4/letter/e/b782af/32.png) [@EmmaGS](https://discuss.elastic.co/u/EmmaGS)\ **Post date:** [July 2, 2018, 8:01pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/11 "2018-07-02T20:01:15Z")
Ok I will try it in linux and search a way to do it in windows. Thanks for your help! ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 30, 2018, 8:01pm UTC](https://discuss.elastic.co/t/problem-ingesting-csv-with-quoted-description-field/137718/12 "2018-07-30T20:01:16Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.