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.
"
within the same field? Can we safely assume that all records will end with a"
followed by a newline?id2
is quoted, but has an undoubled embedded quote at the start."column2 with text
, including the extra quote.(CR)?LF
so there is no ambiguity. However, I am prepared to believe that LibreOffice will be lenient if it can.