3

I have a file with delimiter ~, and was not able to load into database:

Input file:

24~1~2~2~unsubscribed~wap~7~2015-10-23 20:03:00~2015-10-24 20:04:00~2015-10-25 02:53:26~Testing duplicate msisdn campaing row rount 29 ^M\
total - 58~58

Expected Output:

24~1~2~2~unsubscribed~wap~7~2015-10-23 20:03:00~2015-10-24 20:04:00~2015-10-25 02:53:26~Testing duplicate msisdn campaing row rount 29 total - 58~58

If you notice in input file has (^M\). I want to replace this value with a space. I tried with sed (sed 's/^M\//g' filename). I was not getting as expected. Searching for ^M in vi gives me 'pattern not found', too.

  • 2
    The details of what you need to do will depend on what exactly you have in that file. We need to see one of the problematic lines. So, if for example, line 10 of your file is the one shown above, please edit your question and post the output of awk 'NR==10' | od -c. Change the 10 to whatever line number has this issue. – terdon Oct 26 '15 at 17:42
4

First of all, that ^M is a carriage return (\r), not a newline (\n). The \ is something else again, it is there to escape the newline so csv-parsing programs won't treat it as the end of the record.

What you actually have there is a file that was created on Windows where the end of a line is defined by \r\n as opposed to just \n as you have on *nix. I can't be sure unless you give us an excerpt of your actual file, but if you see ^M\ in vim, followed by a \ and then a newline, you probably have \r\\n in the file. So, try this:

perl -i -pe 's/\r\\\n/ /'g file
  • 1
    I join you in breathless anticipation of the output of od -c.  Until we get that, why do you believe that there's a space after the \? – G-Man Oct 26 '15 at 17:54
  • @G-Man because I was being silly. Never mind, fixed now. – terdon Oct 26 '15 at 18:04
1

There is a carriage return before the new line.
You can s,.$,, in vim to remove the last character at any line.
Or you can s,^M$,,. To get the real "^M" character and not a regular expression of the ^,M,$ sequence, press CtrlV then press CtrlM.

-1
 sed 's/\^M\\//g' filename

Escaping meta character.

  • Or just sed 's,\r,,g', for instance. – Nemo Aug 9 '18 at 10:31

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.