I have some data, each record (line) ends with a line feed (\n). Each field is pipe (|) delimited.

Code:

  1|short desc|long text|2001-01-01 01:01
  2|short desc| long
  text |2002-02-02 02:02
  3|short desc|  long  text  | 2003-03-03 03:03
  4|short desc
  |  long  text    | 2004-04-04 04:04

Note that ID #2 and #4 have an extra line feed between the field delimiters. I know that awk can read multi-line data. But the examples I found are for very strictly structured multi-line data, such as addresses. In this case it is only a few rows out of a hundred thousand that are bad. The data source somehow allows for line feeds in some of the text columns. But for my purposes, I don't want/need them.

I need to clean this up before I can load it into a database. The process I use to load into the database will trim any leading and trailing spaces, so they are not an issue for this clean up here. Unfortunately I can't get that to recognize that some of the text columns might also have a \n.

Any ideas? Is there a way to tell awk that I have x number of fields and that it should keep reading until it has that many, ignoring any line feeds until the actual end of the record data?

Thanks
Eric