Trying to import the following line using bcp
1;1;Tiger Woods;64;25.7;296;1,152
with the format file
7.0
7
1 SQLINT 0 4 ";" 1 THISWEEK
2 SQLINT 0 4 ";" 2 LASTWEEK
3 SQLCHAR 0 100 ";" 3 PLAYER
4 SQLINT 0 4 ";" 4 ROUNDS
5 SQLFLT8 0 8 ";" 5 PERCENTAGE
6 SQLINT 0 4 ";" 6 BIRDIES_EAGLES
7 SQLINT 0 4 "" 7 HOLES_PLAYED

but it messes around with the integers so that it looks like it converts it
to ascii

THISWEEK LASTWEEK PLAYER ROUNDS PERCENTAGE BIRDIES_EAGLES HOLES_PLAYED
----------- ----------- ----------------------------------------------------
------------------------------------------------ ----------- ---------------
-------------------------------------- -------------- ------------
49 49 Tiger Woods 13366 4.5739369936478948E-315 3553586 892415025

Using the command

c:\mssql7\binn\bcp pga..PGA_PARBREAK in d:\parcheck.txt -S c
larcombe -U sa -P -f PGAPARBREAK.fmt -t ;

Anyone know how to get around this at all ?
TIA


Sorry if I am using the wrong newsgroup but couldnt find any alternative

Change the SQLINT to SQLCHAR and it should work ok.

This is the datatype of the data in the file rather than
the table.

I had tried this

7.0
7
1 SQLCHAR 0 4 ";" 1
THISWEEK
2 SQLCHAR 0 4 ";" 2
LASTWEEK
3 SQLCHAR 0 100 ";" 3
PLAYER
4 SQLCHAR 0 4 ";" 4
ROUNDS
5 SQLCHAR 0 8 ";" 5
PERCENTAGE
6 SQLCHAR 0 4 ";" 6
BIRDIES_EAGLES
7 SQLCHAR 0 4 "" 7
HOLES_PLAYED

but I now get this error

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

start with a simple example that works and build on that.

This works
bcp file
1;1
2;2

format file
7.0
2
1 SQLCHAR 0 4 ";" 1 a
2 SQLCHAR 0 4 "\r\n" 2 b

drop table ##a
go
create table ##a
(
a int ,
b int
)

exec master..xp_cmdshell 'bcp ##a in c:\bcp.txt -S(local) -Usa -Ppwd
-fc:\bcpfmt.txt'

this also works
bcp file
1;1;hello
2;2;hello

format file
7.0
3
1 SQLCHAR 0 4 ";" 1 a
2 SQLCHAR 0 4 ";" 2 b
3 SQLCHAR 0 100 "\r\n" 3 c

drop table ##a
go
create table ##a
(
a int ,
b int ,
c varchar(100)
)

looking at yours you have "" as the terminator on the last field
trying that it gives Unexpected EOF encountered in BCP data-file

This will give you a method of finding the problem


Nigel Rivett
www.nigelrivett.net

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!