# Data transformation for date field **URL:** \ **Category:** Elasticsearch\ **Created:** [August 30, 2018, 1:33pm UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713 "2018-08-30T13:33:49Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![Gauti](https://avatars.discourse-cdn.com/v4/letter/g/cdc98d/32.png) [@Gauti](https://discuss.elastic.co/u/Gauti)\ **Post date:** [August 30, 2018, 1:33pm UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/1 "2018-08-30T13:33:49Z")
Hi All, We are trying to upload a csv file where it has a date field, so using date filter we are converting the field from string to date, the problme we are facing here is, few fields has "proper date" few fields are "NA" so we planned to use a IF condition in logstash to convert only date field and remove the NA field but it was not successful. Here is my config file: ``` input { file { path => "/opt/installables/csv/data.csv" start_position => "beginning" sincedb_path => "/dev/null" codec => plain { charset => "ISO-8859-1" } } } filter { csv { separator => "," columns => ["Asset No","Asset type","Asset in Store","Critical Asset","Assigned Flag","Assigned to","Licence till","Software Type","Software Provider","Device","Model","Manufacturer","Version","Installation Date","Licence Expired","Under Warranty","End of life","Under AMC","Compliance","Decommissioned","Unpatched Software"] } if [Installation Date] == "NA" { mutate{ remove_field => ["Installation Date"] } } else { date { match => ["Installation Date", "YYYY"] } } } output { elasticsearch { hosts => "1.1.1.6:9200" index => "assetmgmt" } ``` Instead of sending to elasticsearch if i use rubydebug i can see the results which are expected, looks like there is a problem while indexing. have done proper mapping in kibana as well. This is wat i did in kibana ``` PUT assetmgmt { "mappings": { "doc": { "properties": { "Installation Date": { "type": "date", "format": "year"} } } } } ``` Instead of "year" i had tried "YYYY" also, didnt help Any advice please, i'm breaking my head on this from past one week. Thanks Gauti ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [September 4, 2018, 8:14am UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/2 "2018-09-04T08:14:51Z")
can you please check your logstash logfiles and also attach any possible error messages there? Can you also include the rubydebug output so people can compare things? Also, I personally would refrain from using whitespaces and capital letters in field names and just go with snake case, but that might just be me. --Alex ---
**Author:** ![Gauti](https://avatars.discourse-cdn.com/v4/letter/g/cdc98d/32.png) [@Gauti](https://discuss.elastic.co/u/Gauti)\ **Post date:** [September 4, 2018, 11:16am UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/3 "2018-09-04T11:16:49Z")
@spinscale here is the rubydebug output ``` { "path" => "/opt/installables/csv/assetmgmt.csv", "Assigned Flag" => "Yes", "Assigned to" => "RMZ", "message" => "Cell58263,Cell Phones,No,No,Yes,TCS,NA,Cell Phones,2017,MI,MI4,NA,No,NA,NA,NA,No\r", "Licence Expired" => "NA", "@timestamp" => 2018-08-30T05:05:40.246Z, "Installation Date" => "NA", "Under Warranty" => "NA", "Device" => "MI", "Software Type" => "Cell Phones", "End of life" => "No", "host" => "0.0.0.0", "Asset type" => "Cell Phones", "Version" => "No", "Software Provider" => "2017", "@version" => "1", "Critical Asset" => "No", "Asset in Store" => "No", "Model" => "MI4", "Asset No" => "Cell58263", "Licence till" => "NA", "Manufacturer" => "NA" } { "Version" => "NA", "Installation Date" => "2018", "Software Provider" => "Vmware", "Model" => "NA", "path" => "/opt/installables/csv/data.csv", "Asset type" => "Software", "Asset in Store" => "No", "Decommissioned" => "NA", "End of life" => "NA", "Licence Expired" => "No", "Manufacturer" => "NA", "@timestamp" => 2017-12-31T18:30:00.000Z, "Assigned to" => "TCS", "Asset No" => "VM8563", "Device" => "NA", "Under AMC" => "NA", "message" => "VM8563,Software,No,NA,Yes,TCS,2020,Vcentre,Vmware,NA,NA,NA,NA,2018,No,NA,NA,NA,Yes,NA,No\r", "Licence till" => "2020", "Unpatched Software" => "No", "Assigned Flag" => "Yes", "Compliance" => "Yes", "host" => "0.0.0.0" "Critical Asset" => "NA", "@version" => "1", "Under Warranty" => "NA", "Software Type" => "Vcentre" } ``` There are no errors in the log files, if i start sending the data to elasticsearch whereever "Installation Date" is NA only those are getting loaded which ever has date in it is not loaded into elasticsearch. Any suggestions? Thanks Gauti ---
**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\ **Post date:** [September 5, 2018, 1:32pm UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/4 "2018-09-05T13:32:25Z")
can you try a manual dev-tools or curl call and index a document into that index? does it work using `NA` or a year? ---
**Author:** ![Gauti](https://avatars.discourse-cdn.com/v4/letter/g/cdc98d/32.png) [@Gauti](https://discuss.elastic.co/u/Gauti)\ **Post date:** [September 7, 2018, 1:24pm UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/5 "2018-09-07T13:24:38Z")
@spinscale while uploading document through dev-tools if i specify **"Installation date"** as some **year (2018)** its successfully updating but if i mention **"Installation Date"** as **"NA"** then i'm getting the below error ``` { "error": { "root_cause": [ { "type": "mapper_parsing_exception", "reason": "failed to parse [Installation Date]" } ], "type": "mapper_parsing_exception", "reason": "failed to parse [Installation Date]", "caused_by": { "type": "illegal_argument_exception", "reason": "Invalid format: \"NA\"" } }, "status": 400 } ``` Thanks Gauti ---
**Author:** ![Gauti](https://avatars.discourse-cdn.com/v4/letter/g/cdc98d/32.png) [@Gauti](https://discuss.elastic.co/u/Gauti)\ **Post date:** [September 20, 2018, 9:06am UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/6 "2018-09-20T09:06:03Z")
Bingoooo............Got the solution.......... You have to mention "ignore\_malformed": true in your mappings in kibana. ``` PUT snow1 { "mappings": { "doc": { "properties": { "result.closed_at": { "type": "date", "format": "YYYY-MM-dd HH:mm:ss", "ignore_malformed": true } } } } } ``` Thanks Gauti ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [October 18, 2018, 9:06am UTC](https://discuss.elastic.co/t/data-transformation-for-date-field/146713/7 "2018-10-18T09:06:09Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.