Reformat CSV file using awk

Question

I have a file like this -

 1,[test1] Joe,OK
 2,[test2] Jack,OK
 3,[test3] Tom,FAIL

I am printing the file like this -

cat file | awk -F"," '{ print "||"  $1 "||" $2 "||" $3 "||" }'

I would like the output file like this -

|| 1 || Joe  || OK ||
|| 2 || Jack || OK ||
|| 3 || Tom  || FAIL ||

i.e. [test1] Joe is modified to Joe how can we do something like this on each column.

echo "[test1] Joe" | sed 's/\[.*\]\s//g'

Which gives me only Joe how can I combined this with the other columns?

Does the input file really contain a leading space? I have a suspicion it's doesn't.. — Chris Seymour
– Chris Seymour, Commented Mar 1, 2013 at 10:20

Chris Seymour · Accepted Answer · 2013-03-01 10:02:02Z

3

In awk with the correct spacing:

$ awk -F, '{split($2,a," ");printf "||%s || %-4s || %s ||\n",$1,a[2],$3}' file
|| 1 || Joe  || OK ||
|| 2 || Jack || OK ||
|| 3 || Tom  || FAIL ||

You was almost there with your attempt, you should take a look at the printf and split functions.

edited Mar 1, 2013 at 10:02

answered Mar 1, 2013 at 9:51

Chris Seymour

86.4k32 gold badges166 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

perreal · Accepted Answer · 2013-03-01 10:01:29Z

2

With sed:

   sed 's/\[[^]]*\]//;s/^/|| /;s/$/ ||/;s/,/ || /g' input

Output

|| 1 ||  Joe || OK ||
|| 2 ||  Jack || OK ||
|| 3 ||  Tom || FAIL ||

To do in-place start with sed -i

edited Mar 1, 2013 at 10:01

answered Mar 1, 2013 at 9:10

perreal

98.7k23 gold badges159 silver badges187 bronze badges

2 Comments

Chris Seymour Over a year ago

This doesn't add the field separator || to the start and end of each line.

Chris Seymour Over a year ago

Well it's as close Guru's and sarathi I guess. sed probably isn't the best tool for this.

Guru · Accepted Answer · 2013-03-01 09:08:39Z

0

One way:

awk -F, '{$1=OFS""$1;$NF=$NF""OFS;sub(/.* /,"",$2);}1' OFS=" || " file

On running the above:

 ||  1 || Joe || OK ||
 ||  2 || Jack || OK ||
 ||  3 || Tom || FAIL ||

answered Mar 1, 2013 at 9:08

Guru

17.1k2 gold badges37 silver badges47 bronze badges

Comments

Vijay · Accepted Answer · 2013-03-01 12:11:48Z

0

awk -F"," '{gsub(/,\[.*?\]|,|^|$/," || ",$0) }1' your_file

or

perl -plne 's/^|,/ || /g;s/\[.*?\]//g' your_file

tested below:

> cat temp
1,[test1] Joe,OK
2,[test2] Jack,OK
3,[test3] Tom,FAIL
> perl -plne 's/^|,|$/ || /g;s/\[.*?\]//g' temp
 || 1 ||  Joe || OK || 
 || 2 ||  Jack || OK || 
 || 3 ||  Tom || FAIL ||
> awk -F"," '{gsub(/,\[.*?\]|,|^|$/," || ",$0) }1' temp
 || 1 ||  Joe || OK || 
 || 2 ||  Jack || OK || 
 || 3 ||  Tom || FAIL || 
>

If you want to replace the file inplace use:

perl -i -plne 's/^|,|$/ || /g;s/\[.*?\]//g' your_file

edited Mar 1, 2013 at 12:11

answered Mar 1, 2013 at 9:22

Vijay

67.7k94 gold badges238 silver badges327 bronze badges

4 Comments

Chris Seymour Over a year ago

Missing the closing separator || and spacing is off.

Vijay Over a year ago

Modified according to the comment.

Chris Seymour Over a year ago

Your attempted regex hacks still don't fix the spacing issue!

Vijay Over a year ago

Ok @Sudo...you are the best.i am upvoting you.

Collectives™ on Stack Overflow

Reformat CSV file using awk

4 Answers 4

Comments

2 Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

2 Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related