0

I have a data file in the following format

abcd:
 x:123
 y:2345
pqrs:
 x:456
 y:720
mnop:
 x:234
 y:4567
:
:

How can I translate this to a CSV of the format in awk:

abcd,123,2345,<userstring1>,<userstring2>
pqrs,456,720,<userstring1>,<userstring2>
mnop,234,4567<userstring1>,<userstring2>
2
  • Is that your actual data or is the real input a YAML file (the current sample lacks some spaces after the keys, e.g. x: 123)? If it is YAML, then there are tools that makes processing of the input easy. What is "userstring1" and "userstring2"?
    – Kusalananda
    Oct 14, 2022 at 6:07
  • Do you REALLY have 2 lines containing just : at the end of your input? If not, then remove them from your example.
    – Ed Morton
    Oct 15, 2022 at 12:46

2 Answers 2

1

Assuming that the input is actually properly formatted YAML (note the added spaces after the x and y keys):

abcd:
 x: 123
 y: 2345
pqrs:
 x: 456
 y: 720
mnop:
 x: 234
 y: 4567

(Assuming your example is representative of your actual data, you can get your data into this format by simple adding a space after the first : on each line with sed 's/:/: /'.)

We may turn this into a CSV-formatted data set with additional fields at the end containing static strings like this:

yq -r 'to_entries|map([.key, .value.x, .value.y, $ARGS.positional[] ] | @csv)[]' file --args "userstring1" "userstring2"

This uses Andrey Kislyuk's yq from https://kislyuk.github.io/yq/ to create quoted CSV records of the top-level key of each entry in the original document along with the x and y values. Each record also has the strings from the end of the command line added as separate fields.

The resulting output will be the header-less CSV file

"abcd",123,2345,"userstring1","userstring2"
"pqrs",456,720,"userstring1","userstring2"
"mnop",234,4567,"userstring1","userstring2"

If you are stuck with Mike Farah's implementation of yq (which is not a wrapper around the versatile jq processor like Andrey's yq is), you may use

yq 'to_entries|map([.key, .value.x, .value.y, "userstring1", "userstring2"]) | @csv' file

I'm not sure how you may avoid adding the additional user strings without injecting them like this into the yq expression when using Mike's yq.

0

Using any awk:

awk -F':' -v OFS=',' -v strs='<userstring1>,<userstring2>' '
    /^[^ ]/ { if (NR>1) print rec, strs; rec=$1; next }
    { rec = rec OFS $2 }
    END { print rec, strs }
' file
abcd,123,2345,<userstring1>,<userstring2>
pqrs,456,720,<userstring1>,<userstring2>
mnop,234,4567,<userstring1>,<userstring2>

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .