Clean CRLF from text file being imported into SQL2000

art_r used Ask the Experts™
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.


Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Hello art_r,

It would be useful to see some sample data and/or a sample file.  EE now allows you to directly upload files
to your question.

Please be advised that once you upload a file, it can be publicly accessed, and that it may not be possible
to fully and permanently delete it.  Therefore, be very careful about posting proprietary, confidential, or
other sensitive information.  If necessary, use "fake" and/or obfuscated data in your sample.

Please note that at present EE restricts uploads to certain file types.  If your file type does not match
those in the list, you can use instead, which is not officially an EE site, but is run
by people connected to EE.


Top Expert 2012

You can accomplish this directly in your DTS Transformation task or you can either execute an UPDATE statement on the staging tables with REPLACE prior to inserting into your Production tables or use REPLACE in your INSERT statement to your Production tables.


Good idea Patrick, attached is an example of the file

First row is column descriptions.
Row 2 is ok
Row 3 has extra CRLF, causing overflow to row 4 and then causing an extra CRLF in row 5
Row 6 is ok
Row 7-9 has issue as above
Rows 10-12 are ok
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2012

So how do you want to solve it?  I gave you 3 workarounds.
Top Expert 2010

Following acperkins's suggestion of an ActiveX task within your DTS package, you could put a code snippet like
this into your procedure:

Dim fso, tsIn, tsOut, TheLine, arr

Set fso = CreateObject("Scripting.FileSystemObject")
Set tsIn = fso.OpenTextFile("c:\EE-Sample.txt")
Set tsOut = fso.CreateTextFile("c:\Corrected.txt", True)

Do Until tsIn.AtEndOfStream
      TheLine = tsIn.ReadLine
      arr = Split(TheLine, "^")
      If TheLine <> "" Then
            If UBound(arr) <> 15 Then TheLine = TheLine & " " & tsIn.ReadLine
            tsOut.WriteLine TheLine
      End If


Set tsIn = Nothing
Set tsOut = Nothing
Set fso = Nothing

That appeared to work for me when run as a *.vbs file.



acperkins, sorry thought that was a suggestion rather than a question. I'll take better notice next time.

matthewspatrick, that works a treat!
Is it possible to add something to that to strip the last CRLF too as that still stops our import from completing as it goes to next row and has no data?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial