add characters at end of each record in a csv file that contains return characters in fields

Question

I have a csv file with 5 columns in it of varying length. The delimiter between columns is "," and the last column ends with ". The row delimiter is a newline. The latest file has newlines that I have to keep in the text of the very last column.

I was importing this file using the newline at the end of each row as the record delimiter, but now the importer bugs out due to the newlines in the text of the last column. It's not smart enough to recognize the newlines are in one of the fields. Thus, in order to import it correctly I have to create a new end-of-row delimiter and insert it at the end of each row using shell commands, so that the db script I am using to import it can tell when the row is finished.

There are no gnu features in any of the usual commands, which makes it more difficult. I'm on AIX using kornshell. I cannot install other software.

example:

"id1","column2 with text","3","4/4/2044","the 
column
that messes everything up"
"id2",""column2 with text","42","9/9/2099","oh no,
not
again
!!"

Using shell commands (awk, sed, perl, tr, etc) and |@| as the delimiter how do I insert a new delimiter at the end of each row so that in the above example row 1 would terminate with:

that messes everything up"|@|

and row 2 would terminate with:

!!"|@|

I don't need to strip out the newline that is at the end of each row, the importer will ignore it if it is not part of the end-of-row delimiter.

Answer 1

If we can safely assume that all records will end with a " followed by a newline, and that the only time we see a " followed by a newline is at the end of a records, this is relatively simple:

$  sed 's/(.*")$/\1|@|/' file 
"id1","column2 with text","3","4/4/2044","the 
column
that messes everything up"|@|
"id2",""column2 with text","42","9/9/2099","oh no,
not
again
!!"|@|

Or, to allow for some whitespace after the ":

sed 's/"[[:blank:]]*$/"|@|/' file

Of course, this isn't robust. You may very well have double quotes within fields as this is permitted in CSV files, which means you might have them within fields and just before a newline. If this can happen, then my simplistic approach will fail and you really will need to write a proper parser or use a tool like perl or python that has proper csv parsers.