Importing Big Text fields with <CR><LF> in them

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 ***
Don't just participate in USENET...get rewarded for it!"Paul Scotchford" <paul.scotchford-eds@.eds.com> wrote in message
news:4091da3f$0$206$75868355@.news.frii.net...
> 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 ***
> Don't just participate in USENET...get rewarded for it!

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

Importing Access database into Sql Server Express

[Note: originally posted this on microsoft.public.sqlserver.programming
but had no luck there...sorry if you're seeing this twice]

Some facts:

1) I have an Access database (.mdb file) sitting on my harddrive.
2) I have Visual Studio 2005, Sql Server Express, and Sql Server
Management Studio Express.
3) I do *not* have Microsoft Access.

What I'm trying to do:

I simply want to import the Access database into Sql Server Express. In
other words, I want to end up with a Sql Server Express database that
has all the same tables, keys, and relationships as the Access database
as well as all the data from it. I can live without the queries stored
in the Access database, but those would be nice too.

What I've tried so far:

I'm able to connect to the Access database using the "Linked Servers"
features in Management Studio Express. From there, I was able to write
some simple Transact-SQL queries to find out what tables are in the
Access database and copy them, one at a time, into a Sql Server Express
database.

This is definitely a good start, but it doesn't take care of the
primary keys or foreign keys. There appear to be procedures for those
as well (sp_primarykeys, sp_foreignkeys), but I keep thinking there
must be an easier way.

Which brings me to...

Questions:

Without having to buy additional software/tools, can I import this
Access database without a lot of programming? If so, how?

Thanks in advance,

-DanTTBOMK you can use the DTS wizard in MS-Sql 2000 to do what you want,
and you can save your specifications in a file which can be run from
code. Is the DTS wizard available in SQL Express.? Dunno!

The self-code? What's a lot of programming? I haven't done this but I'm
thinking it would take more then one hour, possibly two, if you know
your way around JET, Ms-SQL and ADO or similar technology. If you
don't, it might take a long along time.