awk - how to replace semicolon in string in csv file?

awk, bash, csv

Solution

Replacing the fifth and later `;` with `,`

$ awk -F\; '{for (i=1;i<=NF;i++) printf "%s%s",$i,(i==NF?ORS:(i<=4?";":","))}' myfile.csv 
Sender;Recipient;Operation;Answer;Error,Servername
bla@bla.com;rockit@sohard.com;RCPT TO;450;+4.2.0+<rockit@sohard.com>:+Recipient+address+rejected:+Policy+restrictions,+try+later,M0641

How it works:

`-F\;`

This sets the field separator for input to `;`.

`for (i=1;i<=NF;i++) printf "%s%s",$i,(i==NF?ORS:(i<=4?";":","))`

This loops over every field and prints the field followed by (a) ORS if we are on the last field, or (b) `,` if were are on field 5 or later, or (c) `;` if we are on one of the first four fields.

Replacing all `;` with `,`

Try:

$ awk -F\; '{$1=$1} 1' OFS=, myfile.csv
Sender,Recipient,Operation,Answer,Error,Servername
bla@bla.com,rockit@sohard.com,RCPT TO,450,+4.2.0+<rockit@sohard.com>:+Recipient+address+rejected:+Policy+restrictions,+try+later,M0641

How it works:

`-F\;`

This sets the field separator on input to a semicolon.

`$1=$1`

This causes awk to think the the line has been changed so that awk will update the output line to use the new field separator.

`1`

This tells awk to print the line.

`OFS=,`

This sets the field separator on output to a comma.

Alternative #1

$ awk '{gsub(/;/, ",")} 1' myfile.csv
Sender,Recipient,Operation,Answer,Error,Servername
bla@bla.com,rockit@sohard.com,RCPT TO,450,+4.2.0+<rockit@sohard.com>:+Recipient+address+rejected:+Policy+restrictions,+try+later,M0641

Alternative #2

$ sed 's/;/,/g'  myfile.csv
Sender,Recipient,Operation,Answer,Error,Servername
bla@bla.com,rockit@sohard.com,RCPT TO,450,+4.2.0+<rockit@sohard.com>:+Recipient+address+rejected:+Policy+restrictions,+try+later,M0641

Problem

I need to manage smtp logfile handling in my company. These logfiles need to be imported to MSSQL, so it is my job to provide this data. I got strange undelivery message with a ";" in the string, I need to replace this with a comma. So what I got: ``` Sender;Recipient;Operation;Answer;Error;Servername bla@bla.com;rockit@sohard.com;RCPT TO;450;+4.2.0+<rockit@sohard.com>:+Recipient+address+rejected:+Policy+restrictions;+try+later;M0641 ``` Mention the ";" in the Answer field after "restrictions", dunno why the mail server sends semicolons, maybe to annoy me :P I tried following with awk after I did a lot of research: ``` awk 'BEGIN{FS=OFS=";"} {for (i=5;i<=NF;i++) gsub (";",",",$i)} 1' myfile.csv ``` This command actually works but it seems it does nothing with my file, the ";" in the error field remains. What I am missing here ?

Original source