I have a problem with a SED command I need to replace the @ character with a string and append a string to a column in a csv file



Would become


I’m using the following sed command

sed -i .bak -E 's/([^,\n\“]*)@([^,\n\“]*)/\1_X_\2@newdomain.com/' Test.csv

But I have a new line break somewhere in the command like so


Can anyone advise where the error in my SED command is?

  • You might get something like this if the CSV file is in Windows (CR/LF) format. If this is the case add \r to the not-to-match characters. – AFH Oct 20 '17 at 20:00
  • Yes Im using a Mac with a Windows file saved in the CSV format – B Farrell Oct 20 '17 at 20:43
  • 1
    Have tried sed -i .bak -E 's/([^,\r\n\“]*)@([^,\r\n\“]*)/\1_X_\2@newdomain.com/' Test.csv? That should solve it. Let me know it does, and I'll make it an answer. – AFH Oct 20 '17 at 21:15
  • Thanks AFH - tried that but still the same as previously – B Farrell Oct 20 '17 at 21:22
  • I tried it by creating a file with foo@example.com followed by CR/LF, and it works OK, while without the \r in the search fields it retained the return character before the @. Your other option is to use the dos2unix utility on the file before passing it to sed, but unlike the first method this loses the Windows file format. I'm using Ubuntu Linux, but it's possible that, although Linux-based, your Mac OS uses a different end-line sequence. – AFH Oct 20 '17 at 22:03

It appears that your original file test.csv is Windows-formatted, with return (0x0d or \r) and line feed (0x0a or\n) for each new-line. Your sed search pattern does not exclude \r so this will be retained in the second matched field and output before the appended text.

So your original command:

sed -i .bak -E 's/([^,\n\“]*)@([^,\n\“]*)/\1_X_\2@newdomain.com/' Test.csv

gives the output file (using od -c to see the control characters):

0000000   f   o   o   _   X   _   e   x   a   m   p   l   e   .   c   o
0000020   m  \r   @   n   e   w   d   o   m   a   i   n   .   c   o   m
0000040  \n

If you add \r to the excluded characters:

sed -i .bak -E 's/([^,\r\n\“]*)@([^,\r\n\“]*)/\1_X_\2@newdomain.com/' Test.csv

then the original new-line sequence is maintained:

0000000   f   o   o   _   X   _   e   x   a   m   p   l   e   .   c   o
0000020   m   @   n   e   w   d   o   m   a   i   n   .   c   o   m  \r
0000040  \n

Note that you don't need \r or \n in the first matching field, as this form of sed command will match only within single lines and not through new-lines.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.