Welcome to dbForumz.com!
FAQFAQ    SearchSearch      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

Loading a text file into SQL Server 2005 field using BCP

 
   Database Forums (Home) -> Server RSS
Next:  In-Line upgrade from SQL 2k x32 to SQL 05 x64  
Author Message
Andy

External


Since: Nov 29, 2007
Posts: 7



(Msg. 1) Posted: Thu Feb 14, 2008 3:42 pm
Post subject: Loading a text file into SQL Server 2005 field using BCP
Archived from groups: microsoft>public>sqlserver>server (more info?)

I need to load an entire multi-line text file into a text field on a
single row in a SQL Server 2005 table. As tabs and carriage returns
are used in the text file to denote paragraphs, and in SQL Server to
terminate fields and rows, how do I use SQL Server's BCP.exe utility
to do this so that I don't end up with text broken across several
records?

Looking at BCP, it appears that it also uses the newline character as
a row terminator and the tab as a field terminator. Do I need to
append a unique character, such as ASCII 255 to the end of my text
file, and then set the BCP row terminator operator -r to 255 to force
the field to accept all the carriage returns as is?

Some posts reccomend using textcopy.exe instead. But, this utility
doesn't ship with SQL Server 2005.

Andy

 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
Andy

External


Since: Nov 29, 2007
Posts: 7



(Msg. 2) Posted: Thu Feb 14, 2008 6:44 pm
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

It turns out you can't specify a unique non-printable ascii value as a
delimiter. But, you can specify a unique phrase for up to 10
characters to replace the default end-of-field and end-of-row
delimiters using the -t and -r BCP operators.

I tried running BCP to import my text:

The BCP command I used is:

bcp myDatabaseName.dbo.AGR in agreement.txt -SmyServer\myInstance -
Ulogin -Ppassword -t ***EOL*** -r ***EOF*** -T -c


I am using the administrator account on the SQL server machine which
has ownership of all databases.

The table being written into is: AGR
with the following layout: AGR_AgreementType Int (pk)
AGR_EffectiveDate
datetime(pk)
AGR_AgreementText
text
AGR_Enabled bit
AGR_AgreementID int



The text being written into the database record comes from
agreement.txt.

It contains:
1***EOL***02/14/2008***EOL***This is line one of text
This is line two of text
This is line three of text
This is last line of text***EOL***1***EOL***1***EOL******EOF***


BCP reported the following:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
cast specification

0 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.): total 1

Could this be because of the AGR_Enabled bit field because I'm passing
in 1?

Andy

 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
John Bell5

External


Since: Aug 23, 2004
Posts: 193



(Msg. 3) Posted: Fri Feb 15, 2008 6:22 am
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi Andy

It is better to specify your table structure as DDL such as:

CREATE TABLE AGR ( AGR_AgreementType Int,
AGR_EffectiveDate datetime,
AGR_AgreementText text,
AGR_Enabled bit,
AGR_AgreementID int,
CONSTRAINT PK_AGR PRIMARY KEY ( AGR_AgreementType, AGR_EffectiveDate )
)

With BCP specifying -T and the -U and -P parameters is not a combination to
use.

Your problem with using

bcp myDatabaseName..AGR FORMAT a.txt -SmyServer\myInstance T -t***EOL***
-r***EOF*** -c

is that your data has line ending ***EOL******EOF*** which implies and
extra column in your table.

You could add this column, or use a format file such as (this may wrap!
colations should be at the end of the line!!)

8.0
6
1 SQLCHAR 0 12 "***EOL***" 1 AGR_AgreementType ""
2 SQLCHAR 0 24 "***EOL***" 2 AGR_EffectiveDate ""
3 SQLCHAR 0 0 "***EOL***" 3 AGR_AgreementText SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 3 "***EOL***" 4 AGR_Enabled ""
5 SQLCHAR 0 12 "***EOL***" 5 AGR_AgreementID ""
6 SQLCHAR 0 0 "***EOF***" 0 dummy SQL_Latin1_General_CP1_CI_AS

if the format file is called agreement.fmt then to run this use the command
(this may also wraps!)

bcp myDatabaseName..AGR IN agreement.txt -SmyServer\myInstance -T
-fagreement.fmt


John

"Andy" wrote:

> It turns out you can't specify a unique non-printable ascii value as a
> delimiter. But, you can specify a unique phrase for up to 10
> characters to replace the default end-of-field and end-of-row
> delimiters using the -t and -r BCP operators.
>
> I tried running BCP to import my text:
>
> The BCP command I used is:
>
> bcp myDatabaseName.dbo.AGR in agreement.txt -SmyServer\myInstance -
> Ulogin -Ppassword -t ***EOL*** -r ***EOF*** -T -c
>
>
> I am using the administrator account on the SQL server machine which
> has ownership of all databases.
>
> The table being written into is: AGR
> with the following layout: AGR_AgreementType Int (pk)
> AGR_EffectiveDate
> datetime(pk)
> AGR_AgreementText
> text
> AGR_Enabled bit
> AGR_AgreementID int
>
>
>
> The text being written into the database record comes from
> agreement.txt.
>
> It contains:
> 1***EOL***02/14/2008***EOL***This is line one of text
> This is line two of text
> This is line three of text
> This is last line of text***EOL***1***EOL***1***EOL******EOF***
>
>
> BCP reported the following:
> Starting copy...
> SQLState = 22005, NativeError = 0
> Error = [Microsoft][ODBC SQL Server Driver]Invalid character value for
> cast specification
>
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
> Could this be because of the AGR_Enabled bit field because I'm passing
> in 1?
>
> Andy
>
>
>
>
>
>
 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
Andy

External


Since: Nov 29, 2007
Posts: 7



(Msg. 4) Posted: Fri Feb 22, 2008 6:01 pm
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hey John, thanks for the help, it worked!

I didn't use a format file as the agreement could be of varying sizes

I dropped the ***EOL*** from ***EOL******EOF*** and issued the
following command:

bcp myDatabaseName.dbo.myTableName in c:/myTextToLoad.txt -SMyServer
\MyInstance -UMyLogin -PMyPassword -t ***EOL*** -r ***EOF*** -T -c

When I took the -T out, it wouldn't run, so I put it back in.

I also wrote a Perl script that inserted into the myTextToLoad.txt
file the column and record dilimeter literals ***EOL*** and ***EOF***

The result was that I had the entire text file cited above loaded into
the agreementText field with carriage returns and line feeds included
(and without the delimeters)!
 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
Andy

External


Since: Nov 29, 2007
Posts: 7



(Msg. 5) Posted: Fri Feb 22, 2008 6:35 pm
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

B.T.W.

For others looking for how to load text using BCP and related
problems...

I found that after I loaded the text file into the field that I
couldn't delete the record from the database using SQL Server
Management Studio; it kept returning an error that text would get
truncated.

I did, however, find that you can still delete the record using an SQL
statement in a new Query window and executing the query:

use [yourDatabaseName]

Delete from yourTable where RecKey=YourID
 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 496



(Msg. 6) Posted: Sat Feb 23, 2008 3:01 am
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi

Sounds like you have worked it out!

For large files altering the files to add delimiters may take a while in
which case if you can get the file generated with the require delimiters
that would help.

You may want to try removing the -U and -P parameters then!

John
"Andy" wrote in message

> Hey John, thanks for the help, it worked!
>
> I didn't use a format file as the agreement could be of varying sizes
>
> I dropped the ***EOL*** from ***EOL******EOF*** and issued the
> following command:
>
> bcp myDatabaseName.dbo.myTableName in c:/myTextToLoad.txt -SMyServer
> \MyInstance -UMyLogin -PMyPassword -t ***EOL*** -r ***EOF*** -T -c
>
> When I took the -T out, it wouldn't run, so I put it back in.
>
> I also wrote a Perl script that inserted into the myTextToLoad.txt
> file the column and record dilimeter literals ***EOL*** and ***EOF***
>
> The result was that I had the entire text file cited above loaded into
> the agreementText field with carriage returns and line feeds included
> (and without the delimeters)!
>
>
 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
John Bell

External


Since: Jun 01, 2004
Posts: 496



(Msg. 7) Posted: Sat Feb 23, 2008 1:01 pm
Post subject: Re: Loading a text file into SQL Server 2005 field using BCP [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Hi

At a guess it is building up a command string that it proving to be too
large. It looks like RecKey is a primary key, is this declared as one?

It usually proves easier in the long run to use a query window if you know
the SQL!

John

"Andy" wrote in message

> B.T.W.
>
> For others looking for how to load text using BCP and related
> problems...
>
> I found that after I loaded the text file into the field that I
> couldn't delete the record from the database using SQL Server
> Management Studio; it kept returning an error that text would get
> truncated.
>
> I did, however, find that you can still delete the record using an SQL
> statement in a new Query window and executing the query:
>
> use [yourDatabaseName]
>
> Delete from yourTable where RecKey=YourID
>
>
 >> Stay informed about: Loading a text file into SQL Server 2005 field using BCP 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
mdf is not a primary database file (SQL Server 2005 Express) - Hi, I want to recover data from one database (*.mdf) file: 1- One app were using one sqlserver 2005 express database file. 2- I dont have any backup file from that database 3- The desktop disk stop working 4- One firm recover the disk and the files..

attaching a ms access project file to SQL Server 2005 Expr.. - I have some sample files from the Access Inside OUt 2003 book I want to attach to my server. I can't figure out how to be able to connect to them from the Server? I can't connect to them from the SQL Server at all So I am trying to attach them with..

Import fails on text field - help - Hi, I am trying to import an xls file into SQL Server 2005. It usually works fine, except for when I try to import the "Notes" column. The notes column is just a really long text field. The table I'm attempting to import to has Notes defined a...

BULK INSERT in 2005 does not understand old BCP format fil.. - I am not able to BULK INSERT data files in SQL 2005 because it is complaining about the format of the format description file (.fmt) (it says something about invalid xml character ...) I am aware of the new XML format definition and obviously by default....

Sql Server 2005 - Hi When i take back up database I got below error. System.Data.SqlClient.SqlError: The backup of full-text catalog 'Help_Topics' is not permitted because it is not online. Check errorlog file for the reason that full-text catalog became..
   Database Forums (Home) -> Server All times are: Pacific Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum

Warning: fopen(/home/adsense_reject.txt) [function.fopen]: failed to open stream: Permission denied in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1142

Warning: fwrite(): supplied argument is not a valid stream resource in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1143

Warning: fclose(): supplied argument is not a valid stream resource in /home/autoforu/public_html/Giga/GigaFunctions.php on line 1144



[ Contact us | Terms of Service/Privacy Policy ]