bcp imports data as base 256?
When I import the following lines from a datafile:
DATAFILE1:
1,2,3,4,5,6.789
10,11,12,13,14,15.6789
into SQL Server 2008 R2, The data comes in as though it is interpreting the data as ascii values base 256 (ie. 12337 = (48 * 256) + 49 where '0' = 48 and '1' = 49)
FIELD1 FIELD2 FIELD3 FIELD4 FIELD5 FIELD6 FIELD7
49 50 51 52 53 0.00000000 NULL
12337 12593 12849 13105 13361 0.00000000 NULL
The command I'm using
bcp TABLE1 in DATAFILE.csv -S SERVER1 -d DB1 -U sa -f bcp.fmt
Everything returns successfully without errors, but when I look in the table, everythings all wrong. Here's the bcp.fmt file (non-xml)
10.0
6
1 SQLINT 0 0 "," 1 FIELD1 ""
2 开发者_JAVA技巧 SQLSMALLINT 0 0 "," 2 FIELD2 ""
3 SQLBIGINT 0 0 "," 3 FIELD3 ""
4 SQLBIGINT 0 0 "," 4 FIELD4 ""
5 SQLBIGINT 0 0 "," 5 FIELD5 ""
6 SQLDECIMAL 0 0 "\n" 6 FIELD6 ""
The DDL for the table:
CREATE TABLE [dbo].[TABLE1](
[FIELD1] [int] NOT NULL,
[FIELD2] [smallint] NOT NULL,
[FIELD3] [bigint] NOT NULL,
[FIELD4] [bigint] NULL,
[FIELD5] [bigint] NULL,
[FIELD6] [decimal](18, 8) NOT NULL,
[FIELD7] [decimal](18, 8) NULL
)
Finally found the answer here: http://www.codeproject.com/Messages/3531341/bcp-and-int-ASCII-code-conversion-modified.aspx
The problem was in the bcp.fmt file. The data types should be specified. To quote one of the responses "Just mark the damn field as SQLCHAR, and let db do conversion and all good."
精彩评论