1

I have a csv file that has line breaks inside fields like this : 

"abc" , "adef
efw", "abc"
"abc","awe","abc"

Which should be : 

"abc", "adefefw","abc"
"abc","awe","abc"

Does anyone know some tools or script to fix this ? I prefer using Vim.

Marc B
  • 356,200
  • 43
  • 426
  • 500
Hải Nguyễn Hoàng
  • 83
  • 1
  • 5

3 Answers3

5

If all your fields are quoted with double quotes as in your example, you can simply remove all newline characters that follow a non-doublequote:

%s/\([^"]\)\n/\1/

For those looking for a non-interactive solution, sed is hard to work with when it comes to replacing newlines. Using perl, however, you can use the same expression as above:

perl -p -e 's/([^"])\n/\1/'
barbaz
  • 1,642
  • 2
  • 17
  • 27
  • 1
    I was writing the exact same answer using sed, but you beat me to it. :-) FTR, on OSX, this works also: sed -e ':a' -e 'N' -e '$!ba' -e 's/\([^"]\)\n/\1 /g' my_file.csv > fixed_file.csv – Lukas Vermeer Aug 24 '15 at 21:02
  • Unfortunately, I get an error on OS X (Catalina): sed: 1: "s/([^"])\n/\1 /g ": \1 not defined in the RE Still searching for a command line version. – Bjinse Nov 14 '19 at 15:15
  • @Bjinse I added a non-interactive solution – barbaz Nov 15 '19 at 17:15
0

You have a carriage return or line break formatted in. Show all character in your text editor (I use notepad++) and you should see:

\n or \r

\n is a line feed and \r is a carriage return, different systems use either one or both.

Simply move them where they should be. Differences of \n and \r

Community
  • 1
  • 1
Silverandrew
  • 96
  • 4
-3

You may be able to use preg_replace() function with PHP but you would need to parse the CSV.

Another option might be to user the str_getcsv() function in PHP.

Example from page..

    <?php

    $csv = array_map('str_getcsv', file('data.csv'));

    ?>

Once you parse it into an array you can then output it and reformat it how you would like.

NocturnalCoder
  • 15
  • 5
  • going down the scripting route in this case is a little over kill as the person can do this in thir text editor. Vio can do this in a few key strokes. – Josh Beauregard Nov 15 '19 at 17:24