# CSV Ingest Column 1 = Time, Row 1 = Device

**URL:** <https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180>\
**Category:** Logstash\
**Created:** [November 20, 2020, 6:58pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180 "2020-11-20T18:58:16Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![stevezemlicka](https://avatars.discourse-cdn.com/v4/letter/s/41988e/32.png) [@stevezemlicka](https://discuss.elastic.co/u/stevezemlicka)\
**Post date:** [November 20, 2020, 6:58pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/1 "2020-11-20T18:58:17Z")

</div>

I have a CSV to import that has been formatted for use in Excel. As a result, and necessary for it to open in Excel, it has not only been chopped into several files (which I can handle) but it has also been formatted such that the first column is time and the first row is the device identifier. For example:  
title,device1,device2,device3,device4  
timestamp1,data.dev1.ts1,data.dev2.ts1,data.dev3.ts1,data.dev4.ts1  
timestamp2,data.dev1.ts2,data.dev2.ts2,data.dev3.ts2,data.dev4.ts2  
timestamp3,data.dev1.ts3,data.dev2.ts3,data.dev3.ts3,data.dev4.ts3

A couple years ago I did a transpose similar using perl which I suppose is an option here (though i'm, by no means proficient with perl). I was wondering if anyone else has come across this and/or has any clever strategies for this kind of data sorting?

---

<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:** [November 20, 2020, 7:43pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/2 "2020-11-20T19:43:06Z")

</div>

What are you looking for as the output? Do you want a bunch of events like these?

```
{ "device": "device1", "@timestamp": "parsed value of timestamp1", "data": "data.dev1.ts1" }
{ "device": "device2", "@timestamp": "parsed value of timestamp1", "data": "data.dev2.ts1" }
{ "device": "device3", "@timestamp": "parsed value of timestamp1", "data": "data.dev3.ts1" }
{ "device": "device4", "@timestamp": "parsed value of timestamp1", "data": "data.dev4.ts1" }
{ "device": "device1", "@timestamp": "parsed value of timestamp2", "data": "data.dev1.ts2" }
...
```

---

<div class="post-metadata">

**Author:** ![stevezemlicka](https://avatars.discourse-cdn.com/v4/letter/s/41988e/32.png) [@stevezemlicka](https://discuss.elastic.co/u/stevezemlicka)\
**Post date:** [November 20, 2020, 8:02pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/3 "2020-11-20T20:02:29Z")

</div>

Yes, that would be fantastic! I would probably do some additional tweaking but if know of a good strategy or you can point me in the direction of doing that, it would be a huge help. My previously used perl code is way off

> perl -F, -lane '$s=shift @F;print "$s,$\_" for @F'

---

<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:** [November 20, 2020, 8:50pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/4 "2020-11-20T20:50:03Z")

</div>

I am not saying this is a good use case for logstash, but it can be done.

```
    ruby {
        code => '
            if ! @deviceList
                @deviceList = event.get("message").split(",")
                @deviceList.shift(1)
            else
                data = event.get("message").split(",")
                timestamp = data.shift(1)[0]

                data.each_index { |x|
                    newEvent = LogStash::Event.new
                    newEvent.set("timestamp", timestamp)
                    newEvent.set("device", @deviceList[x])
                    newEvent.set("data", data[x])
                    new_event_block.call(newEvent)
                }
            end
            event.cancel # Drop data from file
        '
    }

```

You must set pipeline.workers to 1 for this to work, and make sure pipeline.ordered has the value you want (true) (or auto in 7.x but not 8.x).

You can use a date filter to parse your [timestamp] field into [@timestamp]

---

<div class="post-metadata">

**Author:** ![stevezemlicka](https://avatars.discourse-cdn.com/v4/letter/s/41988e/32.png) [@stevezemlicka](https://discuss.elastic.co/u/stevezemlicka)\
**Post date:** [November 23, 2020, 3:06pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/5 "2020-11-23T15:06:51Z")

</div>

Thank you for that. I haven't used ruby in quite some time but that definitely seems feasible...though it makes sense that logstash may not be the best tool for this. Especially since it's a manual CSV ingest, I think I'll work with perl to shoehorn the data into a more logstash friendly alignment.

---

<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:** [December 21, 2020, 3:07pm UTC](https://discuss.elastic.co/t/csv-ingest-column-1-time-row-1-device/256180/6 "2020-12-21T15:07:00Z")

</div>

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