开发者

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."

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜