bcp -- easy for you, hard for me

[Jesse]

I'm trying to use bcp to load a textfile of data into a db table. With my
real data, I always get "Unexpected EOF encountered in BCP data-file". So I
created a very simple test -- now I don't get the error, but I don't get any
results, either. Am I missing something obvious?

Here's the simple case:

1. Create a table

CREATE TABLE test (
foo int
);

2. Create a text file of data in xemacs, test.txt. Contents look like this:

1
2
3
4
5

3. Load it up

C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe

Enter the file storage type of field foo [int-null]:
Enter prefix-length of field foo [1]:
Enter field terminator [none]:

Do you want to save this format information in a file? [Y/n] n

Starting copy...

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

What happened? Why were no rows copied? I tried again, this time with field
terminator = \n. And again with \r\n. No change.

TIA!

[SBAUER]

Try using the data formats listed in BOL
Data format bcp utility switch BULK INSERT clause
Native -n DATAFILETYPE = 'native'
Character -c DATAFILETYPE = 'char'
Unicode character -w DATAFILETYPE = 'widechar'
Unicode native -N DATAFILETYPE = 'widenative'

"Jesse" wrote:

Try using the

[Erland Sommarskog]

Jesse (nos...@nospam.com) writes:
> C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe
>
> Enter the file storage type of field foo [int-null]:
> Enter prefix-length of field foo [1]:
> Enter field terminator [none]:
>
> Do you want to save this format information in a file? [Y/n] n
>
> Starting copy...
>
> 0 rows copied.
> Network packet size (bytes): 4096
> Clock Time (ms.): total 1
>
>
> What happened? Why were no rows copied? I tried again, this time with
> field terminator = \n. And again with \r\n. No change.

I've never tried this interactive form of BCP, but int-null is a
binary type. I don't really know what you are suppsed to answer there.
Likewise, prefix-length should be 0; prefix formats is for binary
formats.

The normal way to use BCP is to specify a format on the command line,
or use a format file. Formats on the command line makes use of -c
to specify character format, and you can combine this with -t and -r
to specify field and row terminators. Or you can use -n to specify
native formats. There are also Unicode variations.

--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Jesse]

Thanks for the replies. I'd assumed the interactive form would do the right
thing automatically -- wishful thinking, I guess!

I took your suggestions, and tried using the -c, -w, and -n flags, but
still, no difference. Then I created a format file -- still, no difference.
This is getting a bit frustrating.

Here's what's in the textfile:

C:\>more c:\bcp\test.txt

1
2
3
4
5

And here's the format file:

C:\bcp>more test.fmt
8.0
1
1 SQLCHAR 0 4 "\r\n" 1
foo ""

Here's what I get with the -c flag:

C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe -c

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 180

Oddly enough, if I add in the -t and -r flags, the error goes away -- but I
still get no rows inserted:

C:\>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe -c -t -r

Starting copy...

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

Add in the format file and... nothing:

C:\bcp>bcp test in c:\bcp\test.txt -S db.serverhost.com -U joe -P joe -f
c:\bcp\test.fmt

[Jesse]

At last, it worked. Turned out there were three problems:

1. Using "bcp myTable format" generated a format file for binary data, not
character data. I manually edited the format file to change the datatypes to
SQLCHAR and to set the prefixes back to zero.

2. Ken Tegels suggested I go through the datafile with a hex editor. I found
that though one of the columns was expecting nvarchar, the text was stored
as single-byte characters. Changed the datatype in the format file to fix
this.

3. The lines were terminated with bare linefeeds, not crlf.

Fixed these things, and everything worked fine. Sure would have helped if
bcp could have given some more informative error messages, but at least it's
working now. Thanks for all the help!

[Erland Sommarskog]

Jesse (nos...@nospam.com) writes:
> Fixed these things, and everything worked fine. Sure would have helped if
> bcp could have given some more informative error messages,

Hehe! That tool is very squared in several regards.

In may cases you feed it a text file - or so you think. But BCP really
only reads a stream of bytes which is interpreted by the format
specification. One of the most amazing things is that if you write a
format file for a Unicode file and use, say, ';' as field terminator
and \r\n as row terminator, you must specify them ";\0" and "\r\0\n\0".