CODEWITHSUNDEEP
linuxbashawksed
user2512450
user2512450

Reputation: 41

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

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

Upvotes: 1

Views: 143

Answers (5)

clt60
clt60

Reputation: 63922

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 , -------------------+

Upvotes: 1

anubhava
anubhava

Reputation: 785196

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

Upvotes: 0

Ed Morton
Ed Morton

Reputation: 203625

$ 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.

Upvotes: 0

Jahid
Jahid

Reputation: 22428

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

Upvotes: 0

Reuben L.
Reuben L.

Reputation: 2859

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

Upvotes: 0

Related Questions