Removing Embedded Newline from Delimited File

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

Not very clear.
Could you paste a sample input record and expected output record for the same.

Sure.

Input:

PDK12415|11/11/2008|PDK-01724|RandomBlah|Yes|Yes|Yes|No|No|No|09/12/2006|09/04/2008|"Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
"||
PDK28124|11/12/2008|PDK-02315|RandomBlarg [...]

Desired Results:

PDK12415|11/11/2008|PDK-01724|RandomBlah|Yes|Yes|Yes|No|No|No|09/12/2006|09/04/2008|"Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah."||
PDK28124|11/12/2008|PDK-02315|RandomBlarg [...]

Obviously I'm not loading "blah" into the database, but you get the idea.

Try

grep -v ^$ infile| sed -e :a -e 'N; s/\n//; ta'| sed 's/||P/||\nP/g'

grep -v ^$ infile| sed -e :a -e 'N; s/\n//; ta'| sed 's/||P/||\nP/g'

Thanks, but if the first character of a new line won't always start with P, would I change the P to a [A-z]?

Also, I just realized something about the sample record that I included. It assumes that the last column won't have any data in it. For the file that I have now, that is true, but it's not a guarantee. So, let's adjust the input and desired results.

Input:

 
PDK12415|11/11/2008|PDK-01724|RandomBlah|Yes|Yes|Yes|No|No|No|09/12/2006|09/04/2008|"Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
 
Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah.
"|stuff|
QWB28124|11/12/2008|PDK-02315|RandomBlarg [...]

Desired results:

 
PDK12415|11/11/2008|PDK-01724|RandomBlah|Yes|Yes|Yes|No|No|No|09/12/2006|09/04/2008|"Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. Blah blah blah blah blah blah blah blah blah blah. "Blah blah", or "blah blah blah blah", blah blahs the blah."|stuff|
QWB28124|11/12/2008|PDK-02315|RandomBlarg [...]

Something like this?

perl -0777 -pe'
  tr/\n//d;
  s/((?:.*?\|){14})/$1\n/g
  ' infile

Perfect!

Now, it's become apparent that I have to learn how to craft (or at the very least read and understand) regular expressions, so that I can do similar things without any issue.

I get the "translate all newlines to null" part of the perl script, and I can read that the second line is going to replace ... something... with itself and a newline through the entire script. Now it's just a point of translating ((?:.*?\|){14}). I should be able to handle that though.

Thanks again for the help,
Brandon

Yes,
first delete all newlines, than insert one after every fourteen pipes.