Hey there - a bit of background on what I'm trying to accomplish, first off. I am trying to load the data from a pipe delimited file into a database. The loading tool that I use cannot handle embedded newline characters within a field, so I need to scrub them out.

Solutions that I have tried so far:

1) From a thread here in 2005:
{record = record $0
if (gsub(/"/,"&", record) % 2 )
{ record = record " "
next
}
}
{
print record
record = ""
}

Problems - This worked beautifully on the test data, Then, it was working just fine on the main data... until I received the following error:
"The result [...] of the gsub function cannot be longer than 3000 bytes.

Did I mention that the field with embedded newline characters is going to be loaded as a character large object into the database? Granted, it's only going to be about 6k at max, but that's still more than gsub can handle.

Another note - the test data didn't have any embedded double-quotes. I doubt that this would cause a problem, but in the interest of full disclosure, I should state it.

2) Monster regex:

sed -n '
H
g
s/\n//g
h
/^"\(\(""\)*[^"]*\)*"\(;"\(\(""\)*[^"]*\)*"\)*$/{p;s/.*//g;h;d;}
$p
' filename

Problem - this removes EVERY newline from the script, not just the in-line ones. Definitely can't use this to load the data. Plus, it take a pretty substantial chunk of CPU to run through it.

Other issues with the data:
The "good" newlines at the end of each record are in the same format as the embedded newlines. The FTP client that they use must auto-apply dos2unix when it detects a "text" filetype.

Any help with this would be appreciated. If you need me to clear anything up, let me know.

-Brandon