1

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.

6
  • CSV is an awful format, which is why there are dedicated parsers for it. Can you use perl or python? Can you install perl or python modules? If not, what can we assume about the file? Can you have " within the same field? Can we safely assume that all records will end with a " followed by a newline?
    – terdon
    Commented Oct 8, 2020 at 22:42
  • @terdon It seems not even to be valid CSV. The second field in the row starting id2 is quoted, but has an undoubled embedded quote at the start. Commented Oct 8, 2020 at 22:48
  • @Paul_Pedant are you sure that's invalid for a csv? I know you can have quotes within quoted fields, do they have to be paired? I just opened the OP's file with libreoffice, and it dealt with it correctly: the second field of the second record was "column2 with text, including the extra quote.
    – terdon
    Commented Oct 8, 2020 at 22:52
  • @terdon My reference is RFC 4180, page 2, para (7), and the grammar after it. Also de-facto from extensive Excel experience. The intent is that a quoted field will always have an even number of quotes leading up to the comma or (CR)?LF so there is no ambiguity. However, I am prepared to believe that LibreOffice will be lenient if it can. Commented Oct 8, 2020 at 23:12
  • yes, it's hacky csv at best, but unfortunately this is how I get the files. It actually worked pretty well for the import process before the newlines in the field became a thing. @terdon perl is available. Commented Oct 8, 2020 at 23:30

1 Answer 1

3

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.

3
  • There won't be double quotes inside a field, but I've already seen an incident where one of the fields started with someone hitting enter before typing. Commented Oct 8, 2020 at 23:26
  • @OrdinarySchmo that's easy to deal with. Just use sed 's/\(.*"\)$/`|@|/' file to only match if there is at least one other character before the ".
    – terdon
    Commented Oct 9, 2020 at 8:15
  • Yes! that was close enough: sed 's/(.*")$/\1|@|/' file .. can you update your answer and then I'll mark it the solution. Thanks! Commented Oct 9, 2020 at 17:16

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .