Convert YAML data to CSV using shell script

Question

I have a text file containing a YAML object with repeated keys that I have generated by accessing remote servers.

---
response:
  data:
    configElement:
      elementType: steering-pool
      attribute:
        name: ip-address
        value: 10.10.10.1
      attribute:
        name: start-port
        value: 100
      attribute:
        name: end-port
        value: 9000
      attribute:
        name: realm-id
        value: TSTore
      attribute:
        name: network-interface
        value: M10:150
      attribute:
        name: last-modified-by
        value: admin@10.10.10.10
      attribute:
        name: last-modified-date
        value: 2018-01-23 10:11
    configElement:
      elementType: steering-pool
      attribute:
        name: ip-address
        value: 10.10.10.2
      attribute:
        name: start-port
        value: 1000
      attribute:
        name: end-port
        value: 6535
      attribute:
        name: realm-id
        value: TSTLAN
      attribute:
        name: network-interface
        value: M10:278
      attribute:
        name: last-modified-by
        value: admin@10.10.10.10
      attribute:
        name: last-modified-date
        value: 2010-01-07 11:28
    configElement:
      elementType: steering-pool
      attribute:
        name: ip-address
        value: 10.10.10.3
      attribute:
        name: start-port
        value: 8000
      attribute:
        name: end-port
        value: 9535
      attribute:
        name: realm-id
        value: TestLab2
      attribute:
        name: network-interface
        value: M10:332
      attribute:
        name: last-modified-by
        value: admin@10.10.10.10
      attribute:
        name: last-modified-date
        value: 2009-02-10 12:14
    configElement:
      elementType: steering-pool
      attribute:
        name: ip-address
        value: 10.10.10.5
      attribute:
        name: start-port
        value: 100
      attribute:
        name: end-port
        value: 435
      attribute:
        name: realm-id
        value: NEWLA15
      attribute:
        name: network-interface
        value: M10:253
      attribute:
        name: last-modified-by
        value: admin@10.10.10.10
      attribute:
        name: last-modified-date
        value: 2016-02-22 17:32
    configElement:
      elementType: steering-pool
      attribute:
        name: ip-address
        value: 10.10.10.4
      attribute:
        name: start-port
        value: 100
      attribute:
        name: end-port
        value: 655
      attribute:
        name: realm-id
        value: LAN325
      attribute:
        name: network-interface
        value: M10:2153
      attribute:
        name: last-modified-by
        value: admin@10.10.10.10
      attribute:
        name: last-modified-date
        value: 2019-04-20 13:12
  messages:
  links:

I am struggling to put the data into lines based on its structure. It has multiple elements under a tag configElement.

Using shell script, I want to write data into lines and each line contains data of configElement.

I have already tried solutio proposed on https://stackoverflow.com/a/21189044/8763301, but it didn't fit the format used in my text data.

The expected output is like below, putting all values together from configElement:

attribute: 10.10.10.1,100,9000,TSTore,M10:150,admin@10.10.10.10,"2018-01-23 10:11"
.
.
.
attribute: 10.10.10.2,1000,6535,TSTLAN,M10:278,admin@10.10.10.10,"2010-01-07 11:28"

Please help.

Thanks in advance.

If it is not really `YAML` don't call it `YAML` at all. I guess it would be more appropriate saying you have a structured text file that you want to convert to a `CSV` format. — accdias, Mar 01 '20 at 13:10
If you _"have generated by accessing remote servers"_ why don't you generate it using the expected format already? — accdias, Mar 01 '20 at 13:20
*"I am struggling to put the data..."*, yes, please update your Q to show your struggles. We're glad to help you improve your understanding of scripting, but we don't want to do it for you. If you can reduce the size of your sample input (what does it really matter if it is 10 attributes or only 2?) and show your best attempt to solve your problem, you'll get plenty of help. Good luck. — shellter, Mar 01 '20 at 14:19
YAML objects, the same as JSON objects, can have duplicate keys; typically, but not necessarily, objects are decoded to data structures which require unique keys. I've edited the question to reflect this. — chepner, Mar 01 '20 at 18:14
Shell languages, though, are *not* the correct languages to use for parsing YAML data. Use a language that provides a YAML library. — chepner, Mar 01 '20 at 18:17
The content of a mapping node is an unordered set of key: value node pairs, with the restriction that each of the keys is unique. Your file then seems to me not valid — aborruso, Mar 01 '20 at 20:06

score 0 · Accepted Answer · answered Mar 01 '20 at 19:33

If you run

<input grep -E '(name:|value:)' | \
sed -r 's/^ +//g;s/name: ip-address/~\nname: ip-address/g;s/(name: |value: )//g' | \
perl -pe 's/\n/,/g;s/~,/\n/g;' | \
perl -pe 's/,$//g' | \
grep -vE '^$' | \
mlr --ocsv -N altkv >output.csv

You will have

+------------+------------+----------+----------+-------------------+-------------------+--------------------+
| ip-address | start-port | end-port | realm-id | network-interface | last-modified-by  | last-modified-date |
+------------+------------+----------+----------+-------------------+-------------------+--------------------+
| 10.10.10.1 | 100        | 9000     | TSTore   | M10:150           | admin@10.10.10.10 | 2018-01-23 10:11   |
| 10.10.10.2 | 1000       | 6535     | TSTLAN   | M10:278           | admin@10.10.10.10 | 2010-01-07 11:28   |
| 10.10.10.3 | 8000       | 9535     | TestLab2 | M10:332           | admin@10.10.10.10 | 2009-02-10 12:14   |
| 10.10.10.5 | 100        | 435      | NEWLA15  | M10:253           | admin@10.10.10.10 | 2016-02-22 17:32   |
| 10.10.10.4 | 100        | 655      | LAN325   | M10:2153          | admin@10.10.10.10 | 2019-04-20 13:12   |
+------------+------------+----------+----------+-------------------+-------------------+--------------------+

The last utility - mlr - is Miller (https://github.com/johnkerl/miller)

Thanks a lot aborruso.. Output that resulted in lines before using 'mlr' helped me, and this is what exactly i am looking for...forming lines into structured way using mlr is an enhancement and good learning for me. — Sheldon, Mar 02 '20 at 09:35
@SSK you are welcome, but please note, your input is not a valid YAML file :) — aborruso, Mar 02 '20 at 10:18
Yes.. I got it... Thanks i will work on it.. to get server output in any other best format other than YAML alike shown above. — Sheldon, Mar 02 '20 at 11:02

Convert YAML data to CSV using shell script

1 Answers1