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'
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
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'
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
$ csvformat -U 1 -M '@' file.csv | tr '\n@' ' \n'