# Process - Reshape a CSV file using logstash

**URL:** <https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270>\
**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:** 11\
**Page:** 2

<div class="post-metadata">

**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 17, 2019, 7:35pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/22 "2019-08-17T19:35:28Z")

</div>

> [@baselai](#):
>
> keeping and populating the row's number in the output even if I don't have it in the input

I just realized what you said here. You could change the ruby filter to add

```
init => '@row_number = 0'

```

and in the code option of the filter replace

```
h["row_number"] = rn

```

with

```
@row_number += 1
h["row_number"] = @row_number

```

This requires that you set --pipeline.workers 1

---

<div class="post-metadata">

**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\
**Post date:** [August 18, 2019, 4:18pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/23 "2019-08-18T16:18:06Z")

</div>

@Badger Unfortunately, I'm nit getting the output yet, I played and messed around with it but it didn't work yet.  
The problem is that I should increase the row\_number after the process of each row, but now it is being incremented after each cell.

Here is the output I'm getting, and the json is not well formatted:

> (refer to the output i need to this post [https://discuss.elastic.co/t/turning-a-csv-file-into-json-file-w-adding-new-fields/195571](https://discuss.elastic.co/t/turning-a-csv-file-into-json-file-w-adding-new-fields/195571))

```auto
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.616Z,
             "row_number" => 1,
                   "host" => "c4f20eb0029f",
            "column_name" => "user_name",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "1,Mike,Hello,11.5\r",
    "column_value_string" => "Mike"
}
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.616Z,
             "row_number" => 2,
                   "host" => "c4f20eb0029f",
            "column_name" => "text",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "1,Mike,Hello,11.5\r",
    "column_value_string" => "Hello"
}
{
     "column_value_float" => "11.5",
             "@timestamp" => 2019-08-18T16:09:29.616Z,
             "row_number" => 3,
                   "host" => "c4f20eb0029f",
            "column_name" => "size",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "1,Mike,Hello,11.5\r",
    "column_value_string" => ""
}
{
     "column_value_float" => "1",
             "@timestamp" => 2019-08-18T16:09:29.616Z,
             "row_number" => 4,
                   "host" => "c4f20eb0029f",
            "column_name" => "row_number",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "1,Mike,Hello,11.5\r",
    "column_value_string" => ""
}
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 5,
                   "host" => "c4f20eb0029f",
            "column_name" => "user_name",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "2,Nicolas,Test Test,0.25\r",
    "column_value_string" => "Nicolas"
}
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 6,
                   "host" => "c4f20eb0029f",
            "column_name" => "text",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "2,Nicolas,Test Test,0.25\r",
    "column_value_string" => "Test Test"
}
{
     "column_value_float" => "0.25",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 7,
                   "host" => "c4f20eb0029f",
            "column_name" => "size",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "2,Nicolas,Test Test,0.25\r",
    "column_value_string" => ""
}
{
     "column_value_float" => "2",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 8,
                   "host" => "c4f20eb0029f",
            "column_name" => "row_number",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "2,Nicolas,Test Test,0.25\r",
    "column_value_string" => ""
}
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 9,
                   "host" => "c4f20eb0029f",
            "column_name" => "user_name",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "3,Sandy,Test text,1.25\r",
    "column_value_string" => "Sandy"
}
{
     "column_value_float" => "",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 10,
                   "host" => "c4f20eb0029f",
            "column_name" => "text",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "3,Sandy,Test text,1.25\r",
    "column_value_string" => "Test text"
}
{
     "column_value_float" => "1.25",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 11,
                   "host" => "c4f20eb0029f",
            "column_name" => "size",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "3,Sandy,Test text,1.25\r",
    "column_value_string" => ""
}
{
     "column_value_float" => "3",
             "@timestamp" => 2019-08-18T16:09:29.617Z,
             "row_number" => 12,
                   "host" => "c4f20eb0029f",
            "column_name" => "row_number",
               "@version" => "1",
                   "path" => "/usr/share/logstash/data/sample.csv",
                "message" => "3,Sandy,Test text,1.25\r",
    "column_value_string" => ""
}

```

and here is the code after the changes you suggested + some tweaks to it

```auto
input {
  file {
    path => "/usr/share/logstash/data/sample.csv"
    start_position => beginning
    sincedb_path => "/dev/null"
  }
}

filter {
    csv { 
        source => "message" 
        target => "[@metadata][row]" 
        autodetect_column_names => true
    }
    
    ruby {
        init => '@row_number = 0'
        code => '
            r = event.get("[@metadata][row]")
            rn = 1
            if rn
                a = []
                r.each { |k, v|
                    h = {}
                    @row_number += 1
                    h["row_number"] = @row_number
                    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")
        '
    }
}

output {
    stdout {
        codec => json{}
    }
    file {
        path => "/usr/share/output/output.json"
        codec => "json_lines"
    }
}

```

Thanks in advance for your endless help and support.

---

<div class="post-metadata">

**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 18, 2019, 4:22pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/24 "2019-08-18T16:22:10Z")

</div>

> [@baselai](#):
>
> ```
> rn = 1 
> if rn 
> a = [] 
> r.each { |k, v| 
> h = {}
> @row_number += 1
> 
> ```

OK, we no longer need rn, and you should increment the row number once per row, as you said, so change this to

```
    @row_number += 1
    a = [] 
    r.each { |k, v| 
        h = {}

```

and remove the end after event.set

---

<div class="post-metadata">

**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\
**Post date:** [August 18, 2019, 6:38pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/25 "2019-08-18T18:38:59Z")

</div>

This is amazing! Finally I got it worked!  
Last thing, the output is not formatted as a single json file, it's outputted as a separate json roots  
like this:

```auto
{"column_value_string":"Mike","column_name":"user_name","row_number":1,"column_value_float":""}
{"column_value_string":"","column_name":"size","row_number":1,"column_value_float":"11.5"}
{"column_value_string":"12A","column_name":"output","row_number":1,"column_value_float":""}
{"column_value_string":"Hello","column_name":"text","row_number":1,"column_value_float":""}
{"column_value_string":"Nicolas","column_name":"user_name","row_number":2,"column_value_float":""}
{"column_value_string":"","column_name":"size","row_number":2,"column_value_float":"0.25"}
{"column_value_string":"13B","column_name":"output","row_number":2,"column_value_float":""}
{"column_value_string":"Test Test","column_name":"text","row_number":2,"column_value_float":""}
{"column_value_string":"Sandy","column_name":"user_name","row_number":3,"column_value_float":""}
{"column_value_string":"","column_name":"size","row_number":3,"column_value_float":"1.25"}
{"column_value_string":"f45","column_name":"output","row_number":3,"column_value_float":""}
{"column_value_string":"Test text","column_name":"text","row_number":3,"column_value_float":""}

```

How can I make them as single file like this, and add a new field to the json so I can use this field as an index in Elasticsearch:

```auto
{
    "resourceId": "ABC123",
    "data":  
    [
            {
            "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
            }
    ]
}

```

Here is the code I'm using in the output:

```auto
output {
    stdout {
        codec => json{}
    }
    file {
        path => "/usr/share/output/output.json"
        codec => "json_lines"
    }
}

```

Is there anyway to achieve that? @Badger

---

<div class="post-metadata">

**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\
**Post date:** [August 19, 2019, 2:44pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/26 "2019-08-19T14:44:47Z")

</div>

Update:

I used this json filter:

```auto
json {
        source => "message"
        target => "message"
    }

```

but it didn't help on achieving what I mentioned in my previous reply @Badger

---

<div class="post-metadata">

**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 19, 2019, 3:03pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/27 "2019-08-19T15:03:14Z")

</div>

> [@baselai](#):
>
> Is there anyway to achieve that?

I cannot think of a way to do that using logstash.

---

<div class="post-metadata">

**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\
**Post date:** [August 19, 2019, 3:08pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/28 "2019-08-19T15:08:04Z")

</div>

@Badger Not even using ruby? I mean building one json file instead of having it as separate json objects

---

<div class="post-metadata">

**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 19, 2019, 3:27pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/29 "2019-08-19T15:27:40Z")

</div>

> [@baselai](#):
>
> Not even using ruby?

If you write enough ruby code you can do pretty much anything, but logstash is not designed to process a set of lines and then merge them together.

---

<div class="post-metadata">

**Author:** ![baselai](https://avatars.discourse-cdn.com/v4/letter/b/ea666f/32.png) [@baselai](https://discuss.elastic.co/u/baselai)\
**Post date:** [August 20, 2019, 8:32pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/30 "2019-08-20T20:32:42Z")

</div>

@Badger  
There is an update on the code, I don't know why I'm getting the output columns mixed!  
The values are going to the column\_name and the column\_name values are being set as values!

Here is the code:

```auto
input {
  file {
    path => "/usr/share/input/**/*.*"
    start_position => beginning
    sincedb_path => "/dev/null"
    discover_interval => 2
    stat_interval => "1 s"
  }
}

filter {
    csv { 
        source => "message" 
        target => "[@metadata][row]" 
        autodetect_column_names => true
    }
    
    ruby {
        init => '@row_number = 0'
        code => '
            r = event.get("[@metadata][row]")
            @row_number += 1
            a = []
            r.each { |k, v|
                h = {}
                h["column_name"] = k
                h["row_number"] = @row_number
                if v =~ /^\s*[+-]?((\d+_?)*\d+(\.(\d+_?)*\d+)?|\.(\d+_?)*\d+)(\s*|([eE][+-]?(\d+_?)*\d+)\s*)$/
                    h["column_value_float"] = v.to_f
                    h["column_value_string"] = nil
                else
                    h["column_value_float"] = nil
                    h["column_value_string"] = v
                end
                a << h
            }
            event.set("foo", a)
        '
    }
    split { field => "foo" }
    ruby {
        code => '
            event.get("foo").each { |k, v|
                event.set(k,v)
            }
            event.remove("foo")
        '
    }
    
    mutate {
        add_field => { "blockId" => "12def45" }
    }
}

output {
    stdout { codec => rubydebug }
    file {
        path => "/usr/share/output/output.json"
        codec => "json_lines"
    }
    elasticsearch {
        index => "%{blockId}"
        hosts => ["${Hosts}"]
    }
}

```

Here is the csv file:

```auto
user_name,text,size,output,newfield
Mike,Hello,11.5,12A,1
Nicolas,Test Test,0.25,13B,2
Sandy,Test text,1.25,f45,3

```

Here is the output I'm getting! this is really weird !

```auto
{"column_value_float":null,"column_value_string":"Test Test","blockId":"inference-basel","message":"Nicolas,Test Test,0.25,13B,2\r","row_number":3,"column_name":"Test text"}
{"column_value_float":0.25,"column_value_string":null,"blockId":"inference-basel","message":"Nicolas,Test Test,0.25,13B,2\r","row_number":3,"column_name":"1.25"}
{"column_value_float":2.0,"column_value_string":null,"blockId":"inference-basel","message":"Nicolas,Test Test,0.25,13B,2\r","row_number":3,"column_name":"3"}
{"column_value_float":null,"column_value_string":"13B","blockId":"inference-basel","message":"Nicolas,Test Test,0.25,13B,2\r","row_number":3,"column_name":"f45"}
{"column_value_float":null,"column_value_string":"Nicolas","blockId":"inference-basel","message":"Nicolas,Test Test,0.25,13B,2\r","row_number":3,"column_name":"Sandy"}
{"column_value_float":null,"column_value_string":"text","blockId":"inference-basel","message":"user_name,text,size,output,newfield\r","row_number":2,"column_name":"Test text"}
{"column_value_float":null,"column_value_string":"size","blockId":"inference-basel","message":"user_name,text,size,output,newfield\r","row_number":2,"column_name":"1.25"}
{"column_value_float":null,"column_value_string":"newfield","blockId":"inference-basel","message":"user_name,text,size,output,newfield\r","row_number":2,"column_name":"3"}
{"column_value_float":null,"column_value_string":"output","blockId":"inference-basel","message":"user_name,text,size,output,newfield\r","row_number":2,"column_name":"f45"}
{"column_value_float":null,"column_value_string":"user_name","blockId":"inference-basel","message":"user_name,text,size,output,newfield\r","row_number":2,"column_name":"Sandy"}
{"column_value_float":null,"column_value_string":"Hello","blockId":"inference-basel","message":"Mike,Hello,11.5,12A,1\r","row_number":1,"column_name":"Test text"}
{"column_value_float":11.5,"column_value_string":null,"blockId":"inference-basel","message":"Mike,Hello,11.5,12A,1\r","row_number":2,"column_name":"1.25"}
{"column_value_float":1.0,"column_value_string":null,"blockId":"inference-basel","message":"Mike,Hello,11.5,12A,1\r","row_number":3,"column_name":"3"}
{"column_value_float":null,"column_value_string":"12A","blockId":"inference-basel","message":"Mike,Hello,11.5,12A,1\r","row_number":3,"column_name":"f45"}
{"column_value_float":null,"column_value_string":"Mike","blockId":"inference-basel","message":"Mike,Hello,11.5,12A,1\r","row_number":3,"column_name":"Sandy"}

```

and also when I'm adding two files at the same time the @row\_number is not being reset! and it continues where it stopped counting/processing the previous csv file!

---

<div class="post-metadata">

**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 20, 2019, 8:42pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/31 "2019-08-20T20:42:42Z")

</div>

> [@baselai](#):
>
> The values are going to the column\_name and the column\_name values are being set as values!

Make sure you set '--pipeline.workers 1' and if running a recent version of logstash set pipeline.java\_execution: false in logstash.yml

To handle multiple file you would need more ruby code so that instead of having a single @row\_number variable you would have a hash that tracked the row number for each value of path. You are really stretching the boundaries of what it makes sense to do using logstash.

---

<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:** [September 17, 2019, 8:42pm UTC](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270/32 "2019-09-17T20:42:47Z")

</div>

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

[Previous page](https://discuss.elastic.co/t/process-reshape-a-csv-file-using-logstash/195270.md?page=1)