Hi!

I want to import a tab separated textfile to a SQL 2000
table. The row terminator in the textfile is newline.
Funlayer.txt:

Value1 003
Value2 4,7625
value3 7,0000
*************************************

Commandline:
bcp TEST..FunLayer in FunLayer.txt -Slocalhost -T -r\n -
fbcp.fmt
***********************************

bcp.fmt file:
8.0
3
1 SQLINT 0
0 "" 1
ID ""
2 SQLCHAR 0
50 "\t" 2 MNummer
Finnish_Swedish_CI_AS
3 SQLCHAR 0
50 "\t" 3 InnerDiam
Finnish_Swedish_CI_AS
**********************************************

--Db table
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[FunLayer]') and OBJECTPROPERTY(id,
N'IsUserTable') = 1)
drop table [dbo].[FunLayer]
GO

CREATE TABLE [dbo].[FunLayer] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[MNummer] [varchar] (50) COLLATE
Finnish_Swedish_CI_AS NULL ,
[InnerDiam] [decimal](18, 3) NULL
) ON [PRIMARY]
GO
****************************************
Errormessage:
Starting copy...
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid
character value for cast specification
SQLState = 22005, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Invalid
character value for cast specification
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF
encountered in BCP data-file

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

How can I make it work...?

Thanks!

I haven't tested but give this a try :-

bcp TEST..FunLayer in FunLayer.txt -Slocalhost -T -c -t\t -fbcp.fmt

alternitavly consider loading the data with DTS


--
HTH
Ryan Waight, MCDBA, MCSE

"Rulle" <anonymous (AT) discussions (DOT) microsoft.com> wrote

you don't need the format file since its default tab and line feed and file
type is plain text

bcp test..funlayer in funlayer.txt -T -Slocalhost -c
"Rulle" <anonymous (AT) discussions (DOT) microsoft.com> wrote

I've tried the your suggested command. It seems that it
can not interpret the newline character correctly. Any
ideas?

bcp TEST..FunLayer in FunLayer.txt -Slocalhost -T -c

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

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


/Rulle

I've found the problem, in the textfile the decimal
numbers are delimited with a decimal (,) but the SQL-
server expects a dot (.). I guess I have to convert the
textfile. Or is there any configuration in SQL server that
could resolve the problem?

Thanks!