1

I have a large CSV file. One of the fields contains an error. This error appears as a new line in the file.

Since now i've been using notepad++ with this command to correct the problem :

\r";" => ";"

How can I do the same with sed ?

I've already tried

sed -i 's/\r";"/";"/g' /path/file.csv
sed -i 's/^";"/";"/g' /path/file.csv

no success, someone here know probably the right command

  • 1
    It'll be better if you show part of original text with some lines before and after error line – Costas Nov 30 '14 at 11:55
  • Is it the CR character (0x12) or the two characters backslash and r? – Stéphane Chazelas Nov 30 '14 at 12:07
  • 3
    For me sed -i 's/\r";"/";"/g' works (GNU sed 4.2.2). You should prepare a file with a short test line and give us the exact file content with od -t c -t x1 file. – Hauke Laging Nov 30 '14 at 12:08
  • tr -d "\r" will delete the carriage returns. – bsd Nov 30 '14 at 13:10
  • `---this should be only in one line --- "1289665","first name","JSKTRADES ","2014-02-24 06:44:56","0","JSK International Trading Company","" --------- at the end of the third row content i got a carriage return – erave Nov 30 '14 at 17:54
3

It is important to understand that sed works on a line by line basis. What sed does is basically : read a line into its buffer without the newline, execute your commands on the buffer, print the buffer (provided you haven't specified the -n flag), read the next line into its buffer, etc. So to merge two lines with sed requires that you explicitly force sed to treat more than a single line at a time. To do that, the N, P and D commands are your friend.

Now for your specific problem, to give you a specific and tested answer would require you to put a specific type of input, but here are some examples of what can be done :

This will merge every two lines together :

sed $'N;s/[\\n\r]//g'

or if you are sure to always have \r\n line endings :

sed 'N;s/.\n//'

For a more tailored approach to what I understood of your question, although not the best solution, this should do the work provided you use bash or another shell that supports C escape via the $'str' construct :

sed $':l;N;/\r\\n";"/{;s/\r\\n";"/";"/g;n;};bl'

or without the C-style escape construct and with \r\n line endings (non-negotiable) :

sed ':l;N;/\n";"/{;s/.\n";"/";"/g;n;};bl'

What it does is basically append the next line to its buffer (N) and test for the string you want (/\r\\n";"/). The script loops (bl --> branch to label :l defined at the beginning) as long as it doesn't find a match. When a match is found, it executes the sed script between the curly braces : replace all occurrences of \r\\n";" by ";" (s/\r\\n";"/";"/g) and flush the buffer and input the next line (n).

Of course, if the file is big and the "errors" are infrequent, this could run for a long time and take a lot of memory. If this is the case, another algorithm could be used, but I would need to have a better example of what you are up against to be sure that I understood your problem correctly.

Also, if you would like to learn a little more about sed, I strongly recommend this site which might not have the best background color, but is the best tutorial of sed out there IMO.

0

If you can live with a perl solution:

perl -pe 's/\r";"/";"/g' foo.csv >foo_r.csv

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.