Mdthbs

I've got a CSV weird file with quotes within quotes and newlines and what not in one single column. Now I need to identify that column with "newlines" as one column and replace newlines with some delimiter.

I have 3 columns, the 3rd column will have some HTML text with all double quotes and each and every special character. But the double quotes are escaped with double quotes, like "<This ""is"" string>".

Input:

ID, Name, text

"1","abc","Line 1"

"2","def","Line2

""line2"",line2"

"3","ghi","line3"

Output:

ID, Name, text

"1","abc","Line 1"

"2","def","Line2 ""line2"",line2"

"3","ghi","line3"

There's no real issue with your file. It has embedded newlines and double quotes. A CSV parser would be able to handle it properly. Escaping double quotes with " (while double quoting the field) is the proper way to escape embedded double quotes in a CSV file.

To replace the embedded newlines in your CSV file with a @ character, you could do this:

$ csvformat -M '@' file.csv | tr 'n@' '@n'

1,abc,Line 1

2,def,"Line2@""line2"",line2"

3,ghi,line3

This uses csvformat from the csvkit toolbox. It's a proper CSV parser that is able to reformat CSV files.

The command pipeline above first replaces all newlines that are not embedded with the @ character. Then I use tr to swap the remaining newlines and the @ characters with each other, ending up with a CSV file whose embedded newlines are @.

This relies on the fact that the original data in the file contains no @ characters.

If you then want to have spaces instead of a marker of where the newlines originally were, then use tr 'n@' ' n' instead of the tr shown above:

$ csvformat -M '@' file.csv | tr 'n@' ' n'

1,abc,Line 1

2,def,"Line2 ""line2"",line2"

3,ghi,line3

Note that this would make it extremely difficult to re-insert the original newlines if there are other spaces in the data (as there is in the third field on the first line).

Would you prefer that csvformat did not remove all unnecessary double quotes, then use it with -U 1:

$ csvformat -U 1 -M '@' file.csv | tr 'n@' ' n'

"1","abc","Line 1"

"2","def","Line2 ""line2"",line2"

"3","ghi","line3"

You can try with sed :

sed '

  :A

  2,$ {

    /[^"]"$/! {

      N

      bA

    }

    s/n//g

  }

' infile

Catch on each line from 2 to end if last char is "

If not, get a newline and restart the loop.

At the end of loop, remove each "n".

You can do this with the GNU version of sed, making use of the extended regex support, as shown:

Command-line:

$ sed -Ee '

   1b

   /^("[^"]*"[^"]*)*$/!{

      N;s/n/ /;s/^/n/;D

   }

' input.csv

Results:

ID,Name,Text

"1","abc","Line 1"

"2","def","Line2 ""line2"",line2"

"3","ghi","line3"

Explanation:

• 
  -E turns on the extended regex mode.


• 
  1b will take the header to stdout as it is.


• 
  /^("[^"]*"[^"]*)*$/ will match a line that is fully balanced w.r.t double quotes.


• Hence when we negate it we get our unbalanced lines, IOW, we need to seek their closing double quotes in the succeeding line(s).


• We read in the next line and append to pattern space, N, and remove the newline.


• We repeat this process till the pattern space is balanced.

With POSIX sed you would need to change the above somewhat:

$ sed -e '

   1b

   /^("[^"]*"[^"]*)*$/b

   N;s/n/ /;H;s/.*//;x;D

' input.csv