Using SQL2000 we have a text file we import which has 16 columns of data, delimited with a ^ and uses a CRLF to signal end of row.
Recently this file has started to come with CRLF's in one of the columns where the description is which is causing our DTS import job to fail.
Is there a way to clean these extra CRLF's out without losing the ending CRLF? It seems the 12th column (Stock_Note) is the one that has these issues so being able to do a find a replace in this column only and make any CRLF's just a space would be ideal.
Just noticed too that whenever one of these rows has the extra CRLF it will also have an extra CRLF after the end of the row which needs to be remove.
I think we just need something that can look at the incoming data and only allow a CRLF after the 15th column.
This would then need to be exported back out to the original txt file format so our normal import job can run.