I follow the instructions to import text data from the link below:
http://support.microsoft.com/default...b;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?
Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/default...b;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
David Gugick
Imceda Software
www.imceda.com
|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP10.phx.gbl>...
> Tascien wrote:
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
sql
Showing posts with label text. Show all posts
Showing posts with label text. Show all posts
Wednesday, March 28, 2012
importing trace data into table...
I follow the instructions to import text data from the link below:
http://support.microsoft.com/defaul...kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/defaul...kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP
10.phx.gbl>...
> Tascien wrote:
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
http://support.microsoft.com/defaul...kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/defaul...kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP
10.phx.gbl>...
> Tascien wrote:
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
importing trace data into table...
I follow the instructions to import text data from the link below:
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
--
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP10.phx.gbl>...
> Tascien wrote:
> > I follow the instructions to import text data from the link below:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> >
> > but when i look at the trace_table, in the columb textdata, it's
> > NULL... where can i find the content?
> >
> > Infact, the only columns that have data, is SPID, servername,
> > eventclass all other columns are NULL...
> >
> > any help?
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
but when i look at the trace_table, in the columb textdata, it's
NULL... where can i find the content?
Infact, the only columns that have data, is SPID, servername,
eventclass all other columns are NULL...
any help?Tascien wrote:
> I follow the instructions to import text data from the link below:
> http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> but when i look at the trace_table, in the columb textdata, it's
> NULL... where can i find the content?
> Infact, the only columns that have data, is SPID, servername,
> eventclass all other columns are NULL...
> any help?
Did you include the textdata item in the trace? Was this a server-side
trace or are you saving a trace from Profiler?
--
David Gugick
Imceda Software
www.imceda.com|||D'uh!
I missed that. yes, i did not include TextData in the columns...
thanks.
"David Gugick" <davidg-nospam@.imceda.com> wrote in message news:<eyBlPXd1EHA.1408@.TK2MSFTNGP10.phx.gbl>...
> Tascien wrote:
> > I follow the instructions to import text data from the link below:
> >
> > http://support.microsoft.com/default.aspx?scid=kb;en-us;270599
> >
> > but when i look at the trace_table, in the columb textdata, it's
> > NULL... where can i find the content?
> >
> > Infact, the only columns that have data, is SPID, servername,
> > eventclass all other columns are NULL...
> >
> > any help?
> Did you include the textdata item in the trace? Was this a server-side
> trace or are you saving a trace from Profiler?
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]
GO
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
>
|||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...
>
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
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
>
|||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...
>
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
> >
> >
>
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
> >
> >
>
importing text in SQL server
I need to import a large textfile into sql server 2005.
Some of the text fields contain special characters...like:
?le-de-France
when I import a text like this into SQL Server I get:
??le-de-France
Some of the text fields contain special characters...like:
when I import a text like this into SQL Server I get:
??le-de-France
What can I do to prevent this from happening? (other fieldtype?)Using Unicode is a good choice...That maps to NTEXT data type in SQL Server|||Using Unicode is a good choice...That maps to NTEXT/NVARCHAR data type in SQL Server|||Could you please describe the steps I need to take to make this work?
I cant get it done..:$...sql
Importing text from a flat text file.
I have a DOS scripts that echo's some PC data to a local log file but instea
d
of writing directly to this log file i'd like the output of this script to
populate an SQL 2000 database. How would i go about redirecting the output
from a flat text file to a SQL database.
Regards
John>> How would i go about redirecting the output from a flat text file to a
Some options include Bulk Insert, BCP IN or DTS. Simply search the index in
SQL Server Books Online & you'll find all the information you need to use
them. Also, for DTS, check out www.sqldts.com
Anith|||"ValleyBoy" <ValleyBoy@.discussions.microsoft.com> wrote in message
news:D54A35D3-BE24-458A-B371-27B0A239B043@.microsoft.com...
> I have a DOS scripts that echo's some PC data to a local log file but
instead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Take a look at bcp, BULK INSERT and osql. I'm not sure which approach will
work with your given situation, or if it's even possible to have it
redirected. You may have to dump it to the flat file and then bcp or BULK
INSERT the data in to SQL Server as part of another batch.
Another possible option is to have DTS run the DOS jobs and do something
with the input. I'm not a DTS expert, so I can't say whether or not this
approach is feasible either.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||for this you might check DTS available in BOL
alternatively check for osql command in BOL
eg:
osql /U alma /P /i stores.qry
or u can use bulk Insert
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ValleyBoy" wrote:
> I have a DOS scripts that echo's some PC data to a local log file but inst
ead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
d
of writing directly to this log file i'd like the output of this script to
populate an SQL 2000 database. How would i go about redirecting the output
from a flat text file to a SQL database.
Regards
John>> How would i go about redirecting the output from a flat text file to a
Some options include Bulk Insert, BCP IN or DTS. Simply search the index in
SQL Server Books Online & you'll find all the information you need to use
them. Also, for DTS, check out www.sqldts.com
Anith|||"ValleyBoy" <ValleyBoy@.discussions.microsoft.com> wrote in message
news:D54A35D3-BE24-458A-B371-27B0A239B043@.microsoft.com...
> I have a DOS scripts that echo's some PC data to a local log file but
instead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Take a look at bcp, BULK INSERT and osql. I'm not sure which approach will
work with your given situation, or if it's even possible to have it
redirected. You may have to dump it to the flat file and then bcp or BULK
INSERT the data in to SQL Server as part of another batch.
Another possible option is to have DTS run the DOS jobs and do something
with the input. I'm not a DTS expert, so I can't say whether or not this
approach is feasible either.
HTH
Rick Sawtell
MCT, MCSD, MCDBA|||for this you might check DTS available in BOL
alternatively check for osql command in BOL
eg:
osql /U alma /P /i stores.qry
or u can use bulk Insert
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"ValleyBoy" wrote:
> I have a DOS scripts that echo's some PC data to a local log file but inst
ead
> of writing directly to this log file i'd like the output of this script to
> populate an SQL 2000 database. How would i go about redirecting the output
> from a flat text file to a SQL database.
> Regards
> John
Importing text files to Sql Server using Asp.net/Vb.net
Hi,
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Can anyone help? Need to upload a text file to a sql database but keep getting errors.
I'm creating a page that will allow users to to bulk import and update to a MsSql database. The users provide a text file every so often with new/update information. So i want to use a DTS package to transform the infomation, and create a table in the database, then check against existing/non existing records, if the record exist, update it, if not insert it. I'm using Visual Studio.Net, ASP.Net and coding in VB.Net.
Anyone know where i can find documentation/code regarding the above?
I will be greatful for any help.Hi There,
I looked into using DTS packages to upload data from a spreadsheet about a year and a half ago, but decided against it due to the various problems associated. I opted for the calling of a stored procedure with a bulk insert statement to load the information from a CSV file. Here is c# code for the calling of a DTS package that was used, should be easy enough to convert to vb.net.
BTW you are using DTS; (add a reference to Interop.DTS, i think it is Microsoft DTSPackage Object Library)
All the best, John
string serverName = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerName");
string serverPassword = System.Configuration.ConfigurationSettings.AppSettings.Get("ServerPassword");
string userName = System.Configuration.ConfigurationSettings.AppSettings.Get("UserName");
bool bSuccessful = true;
int pErrorCode;
int lHelpContext;
string sHelpFile;
string sInterfaceError;
string sErrSource;
string sErrDescription;
int DTSStepExecResult_Failure = 1;
Package2Class dtsPackage = new Package2Class();
object varPersistStgOfHost = null;
dtsPackage.LoadFromSQLServer(serverName, userName, serverPassword, DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, null, null, null, DTSPackages.PackageName, ref varPersistStgOfHost);
dtsPackage.Execute();
// Check each step for failure
for (int stepCount=1; stepCount <= dtsPackage.Steps.Count; stepCount++)
{
if ((int)dtsPackage.Steps.Item(stepCount).ExecutionResult == DTSStepExecResult_Failure)
{
dtsPackage.Steps.Item(stepCount).GetExecutionErrorInfo(out pErrorCode, out sErrSource, out sErrDescription, out sHelpFile, out lHelpContext, out sInterfaceError);
bSuccessful = false;
}
}
dtsPackage.UnInitialize();|||hi John,
Thanks for the help, i do appreciate it! will try the code and see if i have any luck!
take care..
jen|||I do not see any part in the code that references the csv file.|||As the above code calls a DTS package that in turn loads the CSV file.
Importing text files into SQL Server - some problems
Hi all,
Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.
My problem is two fold.
Scenario:
I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)
I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.
Running the files through my DTS produced errors on all files. The error was :
Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.
Basically i need to look at these text files and find Rows that are "incomplete"
the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.
any ideas?
thanks,
mcm
Sometimes you have to use file editors so that you can view the ASCII codes in the file and make sure any file clean ups don't remove any non-printable codes that would throw off the file format. There are a lot of file editors available. I've used VEdit for these types of tasks before:
http://www.vedit.com/
-Sue
|||I've gotten the same error before and resolved it by doing the following:
Import the text files into MS Access first. MS Access pinpointed anywhere I had and extra tab in my data. I then would either fix that line in my text file or pull it out completely from my file. I reloaded the text file in the database and the "Too many columns found..." error went away.
Tim
Importing text files into SQL Server - some problems
Hi all,
Sorry if this is in the wrong forum but i didn't know if i should put it in the Data Mining forum.
My problem is two fold.
Scenario:
I have 11 text files with standard data format. They range in size from 20megs to 300+ megs (containing 100's of thousands of lines of data)
I build a simple DTS Package to automate the importing of the text files into a database table with the same format as the text files.
Running the files through my DTS produced errors on all files. The error was :
Too many columns found in the current row; non-whitespace characters were found after the last defined column's data.
The error also gave an approximate position of the problem row. So i opened up a text editor that can handle large files and took out about 5000 records before and after the problem area. So i now had a new test file that had 5000 records and contained what "should" be the problem row. I ran this file and it imported correctly. It did not produce the error that i got previously. However it did import things incorrectly. Going through only one of the files i found several rows (over two dozen) that had half the data on one row, then the other half on the other row. Obviouslly there is a problem here with the way the data was exported. I wanted to know if anyone knows of any programs i can get that will analyze these text files for inconsistencies. I have considered writing one in C# but want to see if there is something already out there that can help me.
Basically i need to look at these text files and find Rows that are "incomplete"
the files come from a unix based system, use LF for row delims and Chr(29) for Column delimiters.
any ideas?
thanks,
mcm
Sometimes you have to use file editors so that you can view the ASCII codes in the file and make sure any file clean ups don't remove any non-printable codes that would throw off the file format. There are a lot of file editors available. I've used VEdit for these types of tasks before:
http://www.vedit.com/
-Sue
|||I've gotten the same error before and resolved it by doing the following:
Import the text files into MS Access first. MS Access pinpointed anywhere I had and extra tab in my data. I then would either fix that line in my text file or pull it out completely from my file. I reloaded the text file in the database and the "Too many columns found..." error went away.
Tim
importing text files and converting datatypes
Hi ya... About to tear my hair out.
I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.
I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)
I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.
Please help! :mad:
ThanksI'm not totaly clear on this: so the data is inside the table 'Pol_Dump073104' and a convert(money, benefit) fails with "Error converting datatype char to money"?|||Yup. This particular DTS package works with most of my text files, and it used to work with this particular text file, but now all of a sudden it won't convert imported varchars (or chars, or nchars, or nvarchars) to floats, or monies. And to be clear, I tried importing the file as all varchars, and then in a later step in my dts packaged, tried to cast those particular fields as floats. And that's when I get that error.
It's frustrating!!!
Am I making any sense?|||I think the varchar column may have values that sql cannot convert to money/float. You may have to check the varchar column prior conversion with functions as ISNUMERIC or a where statement that checks (not like '%[^0-9]%'). You may need to consider this in your package, possibly have it send a notification. The rows that do not fit, what are the options?|||Thanks so much kaiowas. I found the bad rows. Now, how can I tell SQL to send me an exception report with the missing rows?|||Kaiowas, your help saved me. I figured everything out, and it's working beautifully... exception report and all. Just needed to be pointed towards the right track. Many thanks.sql
I thought i fixed this problem, as it WORKED about two days ago, but now I'm getting errors.
I have a series of text files, some are delimited, some are fixed width. I previously was able to import these files thru a dts package by creating the table in a stored procedure. Exple:
CREATE TABLE [Pol_Dump073104]
(
[Product_Type] varchar (12) NULL,
[Benefit] float NULL,
[Base_Premium] varchar (12) NULL,
[Rider_Premium] float NULL,
[Contract_Value] float NULL,
)
I then import the text file into the table and then cast the float datatypes as money in a select into statement to do aggregate functions on the money fields. this worked previously, but now I'm receiving this: 'Error converting datatype char to money.' I tried to convert to float and received the same error, only with float where the word money was.
Please help! :mad:
ThanksI'm not totaly clear on this: so the data is inside the table 'Pol_Dump073104' and a convert(money, benefit) fails with "Error converting datatype char to money"?|||Yup. This particular DTS package works with most of my text files, and it used to work with this particular text file, but now all of a sudden it won't convert imported varchars (or chars, or nchars, or nvarchars) to floats, or monies. And to be clear, I tried importing the file as all varchars, and then in a later step in my dts packaged, tried to cast those particular fields as floats. And that's when I get that error.
It's frustrating!!!
Am I making any sense?|||I think the varchar column may have values that sql cannot convert to money/float. You may have to check the varchar column prior conversion with functions as ISNUMERIC or a where statement that checks (not like '%[^0-9]%'). You may need to consider this in your package, possibly have it send a notification. The rows that do not fit, what are the options?|||Thanks so much kaiowas. I found the bad rows. Now, how can I tell SQL to send me an exception report with the missing rows?|||Kaiowas, your help saved me. I figured everything out, and it's working beautifully... exception report and all. Just needed to be pointed towards the right track. Many thanks.sql
Importing text files - DTS help needed
Hi all
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
news:SYG1h.947$m54.525@.newssvr14.news.prodigy.com. ..
>
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
>
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.
Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.
File is >1m rows and >200 columns and is defined by a proprietory (i.e. not
bcp produced) format specification of the form
Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53
and so on.
Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so I
don't have to click 200 times (with all the ensuing errors I could make).
Any help greatly appreciated.
Cheers
Robbut surely there's a way to get DTS to read from a format file so I don't
Quote:
Originally Posted by
have to click 200 times (with all the ensuing errors I could make).
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JCC" <gofyself@.wrong.address.comwrote in message
news:fdE1h.31731$w07.17227@.newsfe6-win.ntli.net...
Quote:
Originally Posted by
Hi all
>
Could someone help me with the following problem? Hours of googling
yesterday couldn't get me the answer. I'm using SQL 2000 and DTS and
trying to import a huge fixed width text file.
>
File is >1m rows and >200 columns and is defined by a proprietory (i.e.
not bcp produced) format specification of the form
>
Name Start Length
Fld1 0 20
Fld2 19 5
Fld3 24 53
>
and so on.
>
Tbe only way I've found to define the columns so that DTS can import the
file properly is to go through the wizard and click on the starts of each
column. I don't want to use bcp if possible (I did enough of that on
SQL7) - but surely there's a way to get DTS to read from a format file so
I don't have to click 200 times (with all the ensuing errors I could
make).
>
Any help greatly appreciated.
>
Cheers
Rob
>
news:SYG1h.947$m54.525@.newssvr14.news.prodigy.com. ..
Quote:
Originally Posted by
Quote:
Originally Posted by
>but surely there's a way to get DTS to read from a format file so I don't
>have to click 200 times (with all the ensuing errors I could make).
>
Have you looked into the DTS Bulk Insert task or Transact-SQL BULK INSERT?
Both can read a standard BCP format file.
>
>
--
Hope this helps.
>
Dan Guzman
SQL Server MVP
I have looked into this - but as I said, I don't want to mess around with
bcp if I can avoid it - I had no end of problems with bcp a while back.
It looks like I can't avoid it though!
Cheers
Rob
Importing Text Files
hi friends ,
Thank you for the help and support you have given me. Now i am confronted with a new problem. I have to import some textfiles to SQL Server Tables .The columns in textfile is seperated with pipe"|" . I f any body knows this please help me .
Thanks $ Regards
Nevin
You can use BULK INSERT
Code Snippet
BULK INSERT [DatabaseName].dbo.[Table Name]FROM '<Your CSV File>'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
) |||
You have to use Like this
CREATE TABLE F(A VARCHAR(MAX))
BULK INSERT [learner].dbo.[F]
FROM 'E:\SHARE\TE.TXT'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
SELECT * FROM F
|||thank you very much Krupa Sankar . very useful to me it works fine
|||Thank you very much .It works fine
Importing Text File: How to dynamically change the row delimiter
Hi,
I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?
i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.
Any help would be appreciated
I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched
Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).
Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function
I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:
type inputfile.txt>newfile.txt
before importing. Type may convert LF to CRLF.
I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.
If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.
I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?
i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.
Any help would be appreciated
I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched
Regards JustinThe solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).
Public Function gIdentifyEOLCharacter(strFileName As String, _
lngNumOfCharactersToCheck As Long) As String
' identifies the end of line character
Dim fsoSysObj As Scripting.FileSystemObject
Dim tStream As Scripting.TextStream
Dim strText As String, strEOLCharacter As String
On Error GoTo ErrorHere
Set fsoSysObj = New Scripting.FileSystemObject
Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
strText = tStream.Read(lngNumOfCharactersToCheck)
If InStr(strText, Chr$(13)) Then _
strEOLCharacter = strEOLCharacter & "{CR}"
If InStr(strText, Chr$(10)) Then _
strEOLCharacter = strEOLCharacter & "{LF}"
gIdentifyEOLCharacter = strEOLCharacter
ExitHere:
Set fsoSysObj = Nothing
Set tStream = Nothing
Exit Function
ErrorHere:
MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
& "Procedure: gIdentifyEOLCharacter" & _
vbCrLf & "Error Code: " & Err.Number & _
vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
gIdentifyEOLCharacter = vbNullString
Resume ExitHere
End Function
I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful|||I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.|||This is just a wild guess but you might run a cmdshell and do:
type inputfile.txt>newfile.txt
before importing. Type may convert LF to CRLF.
I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.
If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.
Importing Text File With DTS
Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.
Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.
|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.
Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.
|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>
Importing Text File With DTS
Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It´s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can´t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn´t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> It´s a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I can´t define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldn´t be so happy with that)
> thanks in advance.|||I´ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i´ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> > Hello,
> > i'm trying to import a text file using DTS... I check the 'delimited'
> > checkbox to do it because the guy that sent me this file didn't padronized
> > very well the file.
> > It´s a large file, it has about to 70 columns to be imported, and
> > thousands
> > of rows.
> > The problem is that the file has been sent to me like this:
> > column_name1 column_name2 column_name3]
> > data1 data2 data3
> >
> > I can´t define a blankspace using the 'other' checkbox, because some of
> > the
> > columns have more than one blankspace in between.
> > So, can I delimit the columns using some kind of wildcards or do I have to
> > manually padronize all the files?(I wouldn´t be so happy with that)
> > thanks in advance.
>
>sql
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It´s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can´t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn´t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> It´s a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I can´t define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldn´t be so happy with that)
> thanks in advance.|||I´ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i´ll try to translate it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> > Hello,
> > i'm trying to import a text file using DTS... I check the 'delimited'
> > checkbox to do it because the guy that sent me this file didn't padronized
> > very well the file.
> > It´s a large file, it has about to 70 columns to be imported, and
> > thousands
> > of rows.
> > The problem is that the file has been sent to me like this:
> > column_name1 column_name2 column_name3]
> > data1 data2 data3
> >
> > I can´t define a blankspace using the 'other' checkbox, because some of
> > the
> > columns have more than one blankspace in between.
> > So, can I delimit the columns using some kind of wildcards or do I have to
> > manually padronize all the files?(I wouldn´t be so happy with that)
> > thanks in advance.
>
>sql
Importing Text File With DTS
Hello,
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.t
xt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate
it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*
.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>
i'm trying to import a text file using DTS... I check the 'delimited'
checkbox to do it because the guy that sent me this file didn't padronized
very well the file.
It′s a large file, it has about to 70 columns to be imported, and thousands
of rows.
The problem is that the file has been sent to me like this:
column_name1 column_name2 column_name3]
data1 data2 data3
I can′t define a blankspace using the 'other' checkbox, because some of the
columns have more than one blankspace in between.
So, can I delimit the columns using some kind of wildcards or do I have to
manually padronize all the files?(I wouldn′t be so happy with that)
thanks in advance.Hi
What did you get after running the following query?
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.t
xt;
*.csv)};
DefaultDir=c:\FolderName;','select * from MytextFile.txt')
"b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
> Hello,
> i'm trying to import a text file using DTS... I check the 'delimited'
> checkbox to do it because the guy that sent me this file didn't padronized
> very well the file.
> Its a large file, it has about to 70 columns to be imported, and
> thousands
> of rows.
> The problem is that the file has been sent to me like this:
> column_name1 column_name2 column_name3]
> data1 data2 data3
> I cant define a blankspace using the 'other' checkbox, because some of
> the
> columns have more than one blankspace in between.
> So, can I delimit the columns using some kind of wildcards or do I have to
> manually padronize all the files?(I wouldnt be so happy with that)
> thanks in advance.|||I′ve got an error:
Server: Msg 7399, Level 16, State 1, Line 1
(The following text appears for me in Portuguease so i′ll try to translate
it)
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manag
er] Source
data name not found and none default driver specified]
Any clues?
"Uri Dimant" wrote:
> Hi
> What did you get after running the following query?
> select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*
.txt;
> *.csv)};
> DefaultDir=c:\FolderName;','select * from MytextFile.txt')
>
> "b_bussoloti" <bbussoloti@.discussions.microsoft.com> wrote in message
> news:BCED83CA-A12A-4DE5-80B5-6EFACC63C2E2@.microsoft.com...
>
>
Importing Text File to SQL
HI Guys,
I am doing the following to read the data in a text file and inserting it into SQL.
1) Open db connection
2) Open Text File
3) loop through text file all along inserting each row into the db
4) close the text file
5) close the db connection
However, the text file has over 400 rows/lines of data that need to be inserted into the db. Each line in the text file is a row in the db. At anyrate, the above script times out. Is there a better, faster way to do this? I can't use Bulk Insert due to permissions previlages.
Thanks in Advance!DTS would do it. If you can get a DTS pakage set up and a procedure to run it, you can do it by uploading the file to a place the database can see it, and then run the proc that runs the DTS job.
400 rows isn't much. I do a similar thing with up to 100,000 rows. Not an ideal thing, but it was right for the situation. I had to set the timeouts longer, which is what you can do also.
You need to set a longer timeout in three places.
1) server.scripttimeout
2) connection timeout
3) command timeout
Google for examples. 400 is not a lot, so it's not a bad way to do it. If you don't expect that to grow much, just set the timeouts longer and be done with it.|||Hi, I'm new in the programming. I am developing an application that uses text file as input for the data. Therefore, i need to import the text file to SQL server before I could use it. Do you mind if you could share the sample of your code for importing the text file and convert it to sql.
Importing text file removes decimal separator
Hi,
I'm trying to import a semi-comma separated text file into a SQL db. I have a field in the text file that contains decimal number. As a decimal separator it's used a comma (15,35). When i use a DTS package to create a destination table and import all rows, the field is created as a float field. In this field the decimal comma is removed so the number in SQL becomes 1535. If I change the decimal separator to (.) i works OK. But I need to get it work with comma as decimal separator. In the DTS package the field form the text file is recognised as varchar (8000). Any ideas?
Ingar
You have to choose appropriate locale which threats comma as a decimal delimiter. There is the drop-down control for choosing locales on the first page of the Flat File Connection Manager UI.
Thanks.
|||Hi, thanks for your answer. I can see that I didn't specify which SQL version i was using. I use SQL 2000 not 2005. I know that the theme in this forum is SSIS but it also states "transforms/data flow" and I thought DTS packages from SQL 2000 was included here. I appologize if I have misunderstood this but english is not my first language.
So as far as I know the Flat file connection manager is not available in SQL 2000.
But thanks anyway.
Ingar
Importing text file problem using Import and Export Wizard
Hi,
I'm trying to import text file (generated by UNIX - collation ISO LATIN 2) into the database using SQL SERVER 2005 Import and Export Wizard. I have got a problem with importing a decimal number, because in that column are not only decimal numbers (that's OK), but there are also spaces (not null, the column is filled by spaces and it looks like | |). When I'm trying import that file, then will occur the problem of truncation and import stops.
I can import that data by BULK INSERT, but I would like to import it by Import and Export Wizard at once without using subsequent conversions.
I *think* you'll have to do this in two steps. One to load that field as a varchar field. Two to convert that data (excluding spaces) to decimals.|||You could use the package generated by the import/export wizard, and the edit it with BIDS to add the logic necessary to convert/replace those column before the insertion...
Subscribe to:
Posts (Atom)