Importing textfiles in to text fields how ?

Hello,
I have text files.
(Less than a hundred files, sizes between 3 K and 150 K)
I would like to import these files into the database, first
in a table called : Long_text_table.
Each text file goes into only one field.
The id and label fields will be assigned by hand with the
correct values, so that the long text can be moved to
the correct field in the destination table with an sql statement.
How can I get the texts in to this Long_text_table ?
(Using the standard SQL-server tools).
ben brugman
The import table will be something like :
CREATE TABLE [dbo].[Long_text_table] (
[id1] [int] IDENTITY (1, 1) NOT NULL ,
[id2] [int] NULL ,
[id3] [int] NULL ,
[label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOYou can do it with DTS as outlined in
http://www.sqldts.com/?246
Set the field and row delimiter of the Import text file to a combination of
characters that you are sure do not appear in the text file and the whole
file will be treated as one field.
You can just type over the list with delimiters in the DTS designer, you are
not limited to {CR}{LF} etc.
--
Jacco Schalkwijk
SQL Server MVP
"ben brugman" <ben@.niethier.nl> wrote in message
news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have text files.
> (Less than a hundred files, sizes between 3 K and 150 K)
> I would like to import these files into the database, first
> in a table called : Long_text_table.
> Each text file goes into only one field.
> The id and label fields will be assigned by hand with the
> correct values, so that the long text can be moved to
> the correct field in the destination table with an sql statement.
> How can I get the texts in to this Long_text_table ?
> (Using the standard SQL-server tools).
> ben brugman
>
> The import table will be something like :
> CREATE TABLE [dbo].[Long_text_table] (
> [id1] [int] IDENTITY (1, 1) NOT NULL ,
> [id2] [int] NULL ,
> [id3] [int] NULL ,
> [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> GO
>|||Thanks for your attention,
I'll have a look into this. (On the first glance this is not familiar to
me).
For importing files in a single field I have found
the DTS readfile transformation. I tried this with one file
and it works.
Next week I'll try to integrate both methods.
(If I understand the example then).
Otherwise I have to type the filenames by hand one at the
time. (Not a huge problem).
thanks again
ben
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:ubR5bS8pEHA.2456@.TK2MSFTNGP10.phx.gbl...
> You can do it with DTS as outlined in
> http://www.sqldts.com/?246
> Set the field and row delimiter of the Import text file to a combination
of
> characters that you are sure do not appear in the text file and the whole
> file will be treated as one field.
> You can just type over the list with delimiters in the DTS designer, you
are
> not limited to {CR}{LF} etc.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "ben brugman" <ben@.niethier.nl> wrote in message
> news:eCvSwC8pEHA.3252@.TK2MSFTNGP14.phx.gbl...
> > Hello,
> >
> > I have text files.
> > (Less than a hundred files, sizes between 3 K and 150 K)
> >
> > I would like to import these files into the database, first
> > in a table called : Long_text_table.
> >
> > Each text file goes into only one field.
> > The id and label fields will be assigned by hand with the
> > correct values, so that the long text can be moved to
> > the correct field in the destination table with an sql statement.
> >
> > How can I get the texts in to this Long_text_table ?
> > (Using the standard SQL-server tools).
> >
> > ben brugman
> >
> >
> > The import table will be something like :
> >
> > CREATE TABLE [dbo].[Long_text_table] (
> > [id1] [int] IDENTITY (1, 1) NOT NULL ,
> > [id2] [int] NULL ,
> > [id3] [int] NULL ,
> > [label1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [label2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> > [longtext] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> > ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
> > GO
> >
> >
>