# Process - Reshape a CSV file using logstash **URL:** \ **Category:** Logstash\ **Created:** [August 14, 2019, 9:14pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270 "2019-08-14T21:14:58Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 14, 2019, 9:14pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/1 "2019-08-14T21:14:58Z")
Is there a way where I can process any csv file dynamically detect the data\_type for the columns, either float or string. as an example, I have this csv file table ![04%20PM](https://us1.discourse-cdn.com/elastic/original/3X/5/f/5fb62f3bc341d70a82246ce0e3a3fc299ee990df.png) and I need to dynamically turn it into this schema and then push it to Elastic search so I can do dome aggregations on it. ![52%20PM](https://us1.discourse-cdn.com/elastic/original/3X/9/3/93586275d089c85acb8c970eaccda05e91859580.png) not sure If I can do it using logstash / ruby or not. Thanks for any help or suggestion in advance. ---
**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:** [August 14, 2019, 9:28pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/2 "2019-08-14T21:28:06Z")
> [@baselai](#): > > not sure If I can do it using logstash / ruby or not. If you are willing to use ruby you can make logstash do anything. You can turn it into a C++ compiler with enough ruby code. Does this really need to dynamically detect the column type or can you hardcode that [size] is a float? ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 12:13am UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/3 "2019-08-15T00:13:16Z")
@Badger For the dynamic parse, the column name might be anything, and I might have more than one column with a float value. the example I provided was just to demonstrate the idea. actually I don't mind using any method in order to achieve what I'm looking for. since I'm a beginner in logstash/ruby can you help me or provide a guidance on achieving it? I couldn't see clear samples that I can rely on. ---
**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:** [August 15, 2019, 1:33pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/4 "2019-08-15T13:33:37Z")
``` csv { source => "message" target => "[@metadata][row]" autodetect_column_names => true } ruby { code => ' r = event.get("[@metadata][row]") rn = r["row_number"] if rn a = [] r.each { |k, v| h = {} h["row_number"] = rn h["column_name"] = k if v =~ /^\s*[+-]?((\d+_?)*\d+(\.(\d+_?)*\d+)?|\.(\d+_?)*\d+)(\s*|([eE][+-]?(\d+_?)*\d+)\s*)$/ h["column_value_float"] = v h["column_value_string"] = "" else h["column_value_float"] = "" h["column_value_string"] = v end a << h } event.set("foo", a) end ' } split { field => "foo" } ruby { code => ' event.get("foo").each { |k, v| event.set(k,v) } event.remove("foo") ' } ``` ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 4:00pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/5 "2019-08-15T16:00:17Z")
@Badger Thanks for the support, this is the input csv file: row\_number,user\_name,text,size 1,Mike,Hello,11.5 2,Nicolas,Test Test,0.25 3,Sandy,Test text,1.25 and this is the part I added: ``` output { csv { fields => ["column_name", "column_value_string", "column_value_float", "row_number"] path => "/usr/share/output/output.csv" } stdout { codec => rubydebug { metadata => true } } } ``` but I got this output, it is missing the headers "columns names" and an entire row, which it is no. 3 ![image](https://us1.discourse-cdn.com/elastic/original/3X/c/3/c3383f887a729cf767da8a07f9c63e1da293089c.png) ---
**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:** [August 15, 2019, 5:24pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/6 "2019-08-15T17:24:24Z")
Please do not post pictures of text. Just post the text. A csv output does not add headers by default. If you tell it to add headers it will add one row of headers for every line it outputs. Are you sure there is a newline at the end of the third line? Maybe add a blank line to be certain. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 5:51pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/8 "2019-08-15T17:51:59Z")
> [@Badger](#): > > Are you sure there is a newline at the end of the third line? Maybe add a blank line to be certain I checked the file and found that there is no newline and i fixed it. for telling the csv to put the headers in the output, I got this output ![image](https://us1.discourse-cdn.com/elastic/original/3X/6/8/6810a2c8930469e8e41826da6d549f7d82e21615.png) **"Note: I pasted the csv as text, but I got the image auto-generated"** column\_name column\_value\_float column\_value\_string row\_number user\_name Mike 1 column\_name column\_value\_float column\_value\_string row\_number size 11.5 1 column\_name column\_value\_float column\_value\_string row\_number text Hello 1 column\_name column\_value\_float column\_value\_string row\_number row\_number 1 1 column\_name column\_value\_float column\_value\_string row\_number user\_name Nicolas 2 column\_name column\_value\_float column\_value\_string row\_number size 0.25 2 column\_name column\_value\_float column\_value\_string row\_number text Test Test 2 column\_name column\_value\_float column\_value\_string row\_number row\_number 2 2 column\_name column\_value\_float column\_value\_string row\_number user\_name Sandy 3 column\_name column\_value\_float column\_value\_string row\_number size 1.25 3 column\_name column\_value\_float column\_value\_string row\_number text Test text 3 column\_name column\_value\_float column\_value\_string row\_number row\_number 3 3 ``` csv { fields => ["column_name", "column_value_float", "column_value_string", "row_number"] path => "/usr/share/output/output.csv" csv_options => { "write_headers" => true "headers" =>["column_name", "column_value_float", "column_value_string","row_number"] } } ``` but it is not correct, there is row\_number in the columns value, and the new row of headers after each row, this is weird! ---
**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:** [August 15, 2019, 6:32pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/9 "2019-08-15T18:32:46Z")
> [@baselai](#): > > the new row of headers after each row, this is weird! The csv output calls to\_csv for each event. If the to\_csv options say to add a header it will do it for every event. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 6:35pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/10 "2019-08-15T18:35:48Z")
> [@Badger](#): > > If the to\_csv options say to add a header it will do it for every event is there a way to make it called once? so I end up with a proper csv file? I used this property **autogenerate\_column\_names =\> true** but it didn't help ---
**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:** [August 15, 2019, 6:41pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/11 "2019-08-15T18:41:48Z")
> [@baselai](#): > > is there a way to make it called once? No, I do not think so. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 6:42pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/12 "2019-08-15T18:42:35Z")
> [@Badger](#): > > No, I do not think so. not even building a whole csv file using ruby instead? ---
**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:** [August 15, 2019, 6:52pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/13 "2019-08-15T18:52:22Z")
> [@baselai](#): > > not even building a whole csv file using ruby instead? Yes, if you wrote the file in a ruby filter instead of using an output I guess it could be done, since you could add the header in the init. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 7:01pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/14 "2019-08-15T19:01:05Z")
> [@Badger](#): > > Yes, if you wrote the file in a ruby filter instead I wrote this ruby filter to remove a whole row that has a row\_number value but it only removed the cell's value ``` ruby { code => ' hash = event.to_hash hash.each do |k,v| if v == "row_number" event.remove(k) end end ' } ``` because I will not have the row\_number in the input and I need to remove it from the output while keeping and populating the row's number in the output even if I don't have it in the input. I mean that my input might be: user\_name,text,size Mike,Hello,11.5 Nicolas,Test Test,0.25 Sandy,Test text,1.25 I used this filter instead, and it didn't work: ``` csv { remove_field => ["row_number"] } ``` ---
**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:** [August 15, 2019, 7:53pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/15 "2019-08-15T19:53:12Z")
> [@Badger](#): > > h["row\_number"] = rn Why not just remove this line if you do not want row\_number in the output? ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 8:14pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/16 "2019-08-15T20:14:30Z")
> [@Badger](#): > > Why not just remove this line if you do not want row\_number in the output? This will remove it from the output, but I need it in the output even if it is not in the input, eventually the input will look like this user\_name,text,size Mike,Hello,11.5 Nicolas,Test Test,0.25 Sandy,Test text,1.25 So, there should be an auto-numbering for the rows in the output. one more thing, would it be possible to have the "headers" after each row removed if I turned the output into JSON instead of csv? ---
**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:** [August 15, 2019, 8:16pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/17 "2019-08-15T20:16:03Z")
> [@baselai](#): > > would it be possible to have the "headers" after each row removed if I turned the output into JSON instead of csv? That should work. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 15, 2019, 8:40pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/18 "2019-08-15T20:40:32Z")
> [@Badger](#): > > That should work. would that require changing the ruby code you provided? or the way I'm processing the file? I don't have enough knowledge in that context. ---
**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:** [August 15, 2019, 9:19pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/19 "2019-08-15T21:19:52Z")
I thought you meant replacing the csv output with a file input plus a json\_lines codec. ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 16, 2019, 3:25pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/20 "2019-08-16T15:25:21Z")
> [@Badger](#): > > I thought you meant replacing the csv output with a file input plus a json\_lines codec. The input will stay as a csv file like this: user\_name,text,size Mike,Hello,11.5 Nicolas,Test Test,0.25 Sandy,Test text,1.25 and the output would be a json file (because I want to push it to elasticsearch to do aggregations on it), the json schema will something like this (auto row detection and put it in the output): ``` [ { "column_name": "user_name", "column_value_string": "mike", "column_value_float": null, "row_number": 1 }, { "column_name": "text", "column_value_string": "hello", "column_value_float": null, "row_number": 1 }, { "column_name": "size", "column_value_string": "", "column_value_float": 11.5, "row_number": 1 }, { "column_name": "user_name", "column_value_string": "Nicolas", "column_value_float": null, "row_number": 2 }, { "column_name": "text", "column_value_string": "Test Test", "column_value_float": null, "row_number": 2 }, { "column_name": "size", "column_value_string": "", "column_value_float": 0.25, "row_number": 2 }, { "column_name": "user_name", "column_value_string": "Sandy", "column_value_float": null, "row_number": 3 }, { "column_name": "text", "column_value_string": "Test Text", "column_value_float": null, "row_number": 3 }, { "column_name": "size", "column_value_string": "", "column_value_float": 1.25, "row_number": 3 } ] ``` ---
**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\ **Post date:** [August 16, 2019, 10:12pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/21 "2019-08-16T22:12:31Z")
@Badger Thanks for the help you offered, I created a new topic that covers different output for the same input, I'm lost a little bit. [Next page](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270.md?page=2)