Importing Big Text fields with <CR><LF> in them -- sql server (alternate)

Paul Scotchford
4/30/2004 4:46:55 AM

Env : SLQSERVER2000 - DTS

I want to import a text file with 3 fields in it ...

F1: Ident char(3)
F2: Note (Text) char(32,000) yeah big eh!
F3: Date

Field F2: has <CR><LF> thru it, this means the DTS import thinks its a
multiple records to deal with as each row is also delimited by <CR><LF>,
of course this causes problems when loading to the receiving table. The
import creates new records.

Hence is there a way to filter these formatting characters out easily
without disrupting the row delimiter. Using a VbScript, if anyone has
done this I'd be grateful of a copy of the script or utility.

The source of the file (IBM Iseries base app) cannot change the output
format.

Thanks in advance

Paul

*** Sent via Developersdex http://www.developersdex.com ***

Simon Hayes
5/3/2004 9:17:10 PM

[quoted text, click to view]

If the input file has fixed-width columns, then DTS should still work,
although you may need to set up the text file connection definition from a
script rather than with the interface. Alternatively, you could run the
input file through a script written in Perl, VB, C# etc. to replace the
delimiters with something else before loading it with DTS.

Simon

sql server (alternate) : Importing Big Text fields with <CR><LF> in them