bcp easy for you, hard for me

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!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:

> 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 a
ny
> 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 fiel
d
> terminator = \n. And again with \r\n. No change.
>
> TIA!
>
>
Try using the|||Jesse (nospam@.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, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx