how to use sed or awk to change from line delimiter to CSV

Question

Is it possible to have the the following data set:

| asn-query: 
| BGP: 8.8.8.0/24 | Country: US
|   Origin AS: 15169 - GOOGLE - Google Inc., US
|    Peer AS: 1103 1239 2381 3257 6453

to be converted into this structure, using sed\awk

asn-query, BGP: 8.8.8.0/24,  Country: US, Origin AS: 15169 - GOOGLE - Google Inc., US, Peer AS: 1103 1239 2381 3257 6453

Yes it is certainly possible... it looks like you're just trying to outsource the work to someone else. — wojtow, May 16 '16 at 14:35
the 3 hours i've invested in trying to get this to work doesn't feel like outsource the work to someone else, i just learned to quit and ask for help. — user2512450, May 16 '16 at 23:28

score 1 · Accepted Answer · answered May 16 '16 at 15:57

If your file have multiple blocks of the above text, for example, if your file contains:

| asn-query: 
| BGP: 8.8.8.0/24 | Country: US
|   Origin AS: 15169 - GOOGLE - Google Inc., US
|    Peer AS: 1103 1239 2381 3257 6453
| asn-query: 
| BGP: 2.2.2.0/24 | Country: XX
|   Origin AS: 11111 - XXXXX - YYYYY Inc., US
|    Peer AS: 1212 1313 1414 1515 1616

you could use the paste utility to join every 4 lines to one line, e.g.

paste - - - - < the_above_file.txt

will produce two lines:

| asn-query:    | BGP: 8.8.8.0/24 | Country: US |   Origin AS: 15169 - GOOGLE - Google Inc., US |    Peer AS: 1103 1239 2381 3257 6453
| asn-query:    | BGP: 2.2.2.0/24 | Country: XX |   Origin AS: 11111 - XXXXX - YYYYY Inc., US   |    Peer AS: 1212 1313 1414 1515 1616

and could modify each line as you wish, for example:

paste  - - - - < asn.txt | sed 's/\|[[:blank:]]*//;s/://;s/[[:blank:]]*\|[[:blank:]]*/,/g'

will produce:

asn-query,BGP: 8.8.8.0/24,Country: US,Origin AS: 15169 - GOOGLE - Google Inc., US,Peer AS: 1103 1239 2381 3257 6453
asn-query,BGP: 2.2.2.0/24,Country: XX,Origin AS: 11111 - XXXXX - YYYYY Inc., US,Peer AS: 1212 1313 1414 1515 1616

same using perl (and explained)

paste  - - - - < asn  |  perl -plE 's/\|\s*//; s/://; s/\s*\|\s*/,/g'
                                    ^^^^^^^^^  ^^^^^  ^^^^^^^^^^^^^^
                                       ^         ^        ^
replace the 1st |+spaces with nothing--+         |        |
                                                 |        |
replace the 1st : with nothing ------------------+        |
                                                          |
replace all spaces + | + spaces with , -------------------+

score 0 · Answer 2 · edited May 23 '17 at 12:23

This should work:

sed -e ':a' -e 'N' -e '$!ba' -e 's/\n//g' -e 's/|[[:blank:]]//g' file

For explanation, see this answer.

Or you can simplify it (a lot) using tr:

sed -e 's/|[[:blank:]]//g' file |tr -d '\n'

In this second method, you won't be able to use seds' -i flag to perform in-place edit. You can use this:

echo "$(sed -e 's/|[[:blank:]]//g' file |tr -d '\n')" > file

as an alternative of in-place edit, or use a different file:

sed -e 's/|[[:blank:]]//g' file |tr -d '\n' > newfile

score 0 · Answer 3 · answered May 16 '16 at 14:37

0

cat file | xargs echo -n | sed -e 's/ |/,/g' -e 's/:,/,/g' -e 's/| //g'

answered May 16 '16 at 14:37

Reuben L.

2,806
2
29
45

cat file | xargs -L | sed ' s/|/,/g' |sed 's/,//' This can be done – P.... May 16 '16 at 15:13

score 0 · Answer 4 · answered May 16 '16 at 15:22

$ awk -v RS='^$' -v ORS= -F'\\s*\\|\\s*' -v OFS=', ' '{sub(FS,"");$1=$1}1' file
asn-query:, BGP: 8.8.8.0/24, Country: US, Origin AS: 15169 - GOOGLE - Google Inc., US, Peer AS: 1103 1239 2381 3257 6453

The above is gawk-specific due to multi-char RS and is explained by reading the book Effective Awk Programming, 4th Edition, by Arnold Robbins.

score 0 · Answer 5 · answered May 16 '16 at 15:28

0

You can use this gnu awk command:

awk -v RS='(: *)?\n*\\|[[:blank:]]*' -v ORS=', ' 'NR>1 && RT; END{printf "%s", $0}' file

asn-query, BGP: 8.8.8.0/24 , Country: US, Origin AS: 15169 - GOOGLE - Google Inc., US, Peer AS: 1103 1239 2381 3257 6453

answered May 16 '16 at 15:28

anubhava

761,203
64
569
643

how to use sed or awk to change from line delimiter to CSV

5 Answers5