shell script: convert raw output (from dict lines into csv)

user_11999777 0
Viewed 106

Can anyone help me to convert below raw output to csv or a key value pair.

i have tried awk, sed methods but the problem is... data{} contains 2 key-value pairs, sometimes it has only one key-value pair like shown in 3rd row. (myID is missing in 3rd row)

"data":{"myID":"111" "Country":"United Kingdom"
"data":{"myID":"222" "Country":"Germany"
"data":{"Country":"United Kingdom" "data":{"myID":"444"
"Country":"United Kingdom" "data":{"myID":"555"
"Country":"United Kingdom" "data":{"Country":"United Kingdom"

trying to get below output.

111,United Kingdom
222,Germany
,United Kingdom
444,United Kingdom
555,United Kingdom
,United Kingdom

please help.. thanks in advance.

sed awk shell linux
2 Answers

Whenever you have tag-value pairs in your data it's best to build an array mapping those tags (aka names or keys) to their values and then you can just print them by name. If you find yourself with data in future that has more tag-value pairs just add the tag names to the string in the first arg to split() and the rest of the script will work as-is:

$ cat tst.awk
BEGIN {
    numTags = split("myID Country",tagList)
    for (i in tagList) {
        tags[tagList[i]]
    }
    FS = "\""
    OFS = ","
}
{
    delete tag2val
    for (i=2; i<NF; i++) {
        if ( $i in tags ) {
            tag2val[$i] = $(i+2)
        }
    }
    for (tagNr=1; tagNr<=numTags; tagNr++) {
        tag = tagList[tagNr]
        val = tag2val[tag]
        printf "%s%s", val, (tagNr<numTags ? OFS : ORS)
    }
}


$ awk -f tst.awk file
111,United Kingdom
222,Germany
444,United Kingdom
555,United Kingdom
,United Kingdom
user_17450010

This might work for you (GNU sed):

sed 'N;s/\n"Country":/,/;s/ "data":{/\n/;s/"data":{\|"myID"://g;s/ *"Country":/,/g;s/"//g;P;D' file

From the data provided, this sed solution provides the stated format but some how I think that there may be edge cases not accounted for.

user_9674920
Related