# Resolved - Logstash - CSV parse failure for some records **URL:** \ **Category:** Logstash\ **Created:** [March 5, 2019, 8:24am UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842 "2019-03-05T08:24:19Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [March 5, 2019, 8:24am UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/1 "2019-03-05T08:24:19Z")
Hi Team, I have a huge csv file exported from github which is the commit information of engineers. In the csv, there're two columns ( **COMMIT\_COMMENT** and **JIRA\_KEY** ) might contain special characters, but unlikely I am not able to knowin advance what and how many special characters the file will contain as the content are filled in by engineer manually in JIRA system and can't be controlled. Theoretically, they can input any special characters or even html tags. **1. Piece of Sample CSV file which has special characters** ``` > "GIT_ORG","GIT_REPOS","COMMIT_SHA1","COMMIT_AUTHOR","COMMIT_DATE","COMMIT_COMMENT","JIRA_KEY","JIRA_ISSUE_TYPE","JIRA_ISSUE_CATEGORY","JIRA_PRIORITY","JIRA_COMPONENTS","JIRA_DEFECT_TYPE","JIRA_TESTCASE_ID","JIRA_FIND_BY_AUTO" > "bizx","au-recruiting","d88e03469576f30079c366850fc952864815fb65","Kaderjan Ilghar","2018-01-24","fixing SML-710\, SML-714\,SML-708\,SML-709","","","","",,,, > "bizx","au-V4","f54abe7a0516407a8f6e2334ce8b1a1c229dd6aa","wwang","2016-09-19","TFT- DTCE new common component change ( Change FeatureEnum type)","","","","",,,, > "bizx","au-V4","da5ab4deecd598e514bc3848d1dd0204643ccc0a","I848428","2017-03-01","PLT-56858: Remove the \"field-permissions\" level message key for field-permissions.","PLT-56858","Defect","Internal Issue","3-Medium","|Z. DO NOT USE Permission|","New","","No" ``` **My Logstash configuration:** ``` input { file{ path => "C:/elkstack/elasticsearch-6.5.1/logs/git_commits.csv" start_position => "beginning" sincedb_path => "NUL" } } filter { csv { columns => [ "GIT_ORG", "GIT_REPOS", "COMMIT_SHA1", "COMMIT_AUTHOR", "COMMIT_DATE", "COMMIT_COMMENT", "JIRA_KEY", "JIRA_ISSUE_TYPE", "JIRA_ISSUE_CATEGORY", "JIRA_PRIORITY", "JIRA_COMPONENTS", "JIRA_DEFECT_TYPE", "JIRA_TESTCASE_ID", "JIRA_FIND_BY_AUTO"] separator => ","} translate { destination => "[@metadata][lookup]" dictionary_path => "C:/elkstack/elasticsearch-6.5.1/logs/LookUp.csv" field => "COMMIT_SHA1" } dissect { mapping => { "[@metadata][lookup]" => "%{FILE_CHANGED};%{FILE_TYPE};%{FILE_METHOD}" }} } output { elasticsearch { action => "index" hosts => "localhost:9200" index => "logstash-git" } stdout { codec => rubydebug } } ``` logstash trace log > ``` > > > { > > > "tags" => [ > > > [0] "_csvparsefailure" > > > ], > > > "host" => "PVGN50859047A", > > > "@version" => "1", > > > "@timestamp" => 2019-03-05T09:16:47.430Z, > > > "message" => "\"bizx\",\"au-V4\",\"da5ab4deecd598e514bc3848d1dd0204643ccc0a\",\"I848428\",\"2017-03-01\",\"PLT-56858: Remove the \\\"field-permissions\\\" level message key for field-permissions.\",\"PLT-56858\",\"Defect\",\"Internal Issue\",\"3-Medium\",\"|Z. DO NOT USE Permission|\",\"New\",\"\",\"No\"\r", > > > "path" => "C:/elkstack/elasticsearch-6.5.1/logs/less.csv" > > > } > > ``` **Please help advise how should I get rid of issue? Precondition is that I am not able to know how many special special characters will be in the csv. Is there a general way can resolve this?** ---
**Author:** ![Ganesh2303](https://avatars.discourse-cdn.com/v4/letter/g/57b2e6/32.png) [@Ganesh2303](https://discuss.elastic.co/u/Ganesh2303)\ **Post date:** [March 5, 2019, 10:51am UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/2 "2019-03-05T10:51:27Z")
HI, If you have special character in your file mean my best opinion is instead of going csv filter you can extract using grok filter to avoid that kind of issue. ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [March 5, 2019, 10:28pm UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/3 "2019-03-05T22:28:55Z")
Hi Ganesh - How to do it with grok filter ---
**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:** [March 5, 2019, 11:03pm UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/4 "2019-03-05T23:03:25Z")
The following will process the first 5 and last 7, leaving you with 2 fields in [message]. Not sure how to parse those 2. ``` if [message] =~ /^"GIT_ORG"/ { drop {} } #Strip and process 5 well formatted fields at the beginning grok { match => { "message" => '^(?("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*)"|),' } } mutate { gsub => ["message", '^("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),', "" ] } csv { source => "first5" columns => ["GIT_ORG", "GIT_REPOS", "COMMIT_SHA1", "COMMIT_AUTHOR", "COMMIT_DATE"] remove_field => ["first5"] } #Strip and process 7 well formatted fields at the end grok { match => { "message" => ',(?("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|))$' } } mutate { gsub => ["message", ',("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|),("[^"]*"|)$', "" ] } csv { source => "last7" columns => ["JIRA_ISSUE_TYPE", "JIRA_ISSUE_CATEGORY", "JIRA_PRIORITY", "JIRA_COMPONENTS", "JIRA_DEFECT_TYPE", "JIRA_TESTCASE_ID", "JIRA_FIND_BY_AUTO"] remove_field => ["last7"] } ``` It may require some adhocery, but for those 3 examples this would work ``` grok { match => { "message" => '^"%{DATA:COMMIT_COMMENT}","%{DATA:JIRA_KEY}"$' } } ``` ---
**Author:** ![Ganesh2303](https://avatars.discourse-cdn.com/v4/letter/g/57b2e6/32.png) [@Ganesh2303](https://discuss.elastic.co/u/Ganesh2303)\ **Post date:** [March 6, 2019, 9:38am UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/5 "2019-03-06T09:38:27Z")
> [@cheriemilk](#): > > Hi Ganesh - How to do it with grok filter Try with this pattern it will work but you need value on all the column after comma "%{WORD:data1}","?(?[a-zA-Z0-9-]+)","%{WORD:data3}","%{WORD:data4}","?(?[a-zA-Z0-9-]+)","?(?[A-Za-z0-9:\" .-]+)","?(?[a-zA-Z0-9-]+)","?(?[a-zA-Z0-9-]+)","?(?[a-zA-Z0-9 -]+)","?(?[a-zA-Z0-9-]+)","?(?[a-zA-Z0-9 .|-]+)","?(?[a-zA-Z0-9-]+)","?(?[a-zA-Z0-9 -]+)","?(?[a-zA-Z0-9-]+) ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [March 8, 2019, 1:31pm UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/6 "2019-03-08T13:31:08Z")
Thank you Badger. helpful suggestion. ---
**Author:** ![cheriemilk](https://avatars.discourse-cdn.com/v4/letter/c/c37758/32.png) [@cheriemilk](https://discuss.elastic.co/u/cheriemilk)\ **Post date:** [March 8, 2019, 1:32pm UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/7 "2019-03-08T13:32:38Z")
Thank you Ganesh, it prompts me another way. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 5, 2019, 1:32pm UTC](https://discuss.elastic.co/t/resolved-logstash-csv-parse-failure-for-some-records/170842/8 "2019-04-05T13:32:38Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.