不断收到导入错误“字符串数据，正确的截断"；使用BCP [英] Keep getting import error "String data, right truncation" using BCP

问题描述

我遇到了一个问题.我正在尝试使用BCP将大约700,000条记录导入到工作表中.

I've hit a problem. I'm trying to use BCP to import about 700,000 records into a working table.

这是我用来建立工作表的SQL:

This is the SQL I use to build my working table:

If Exists (Select * From sys.tables Where [name] = 'InactiveIDs')
    Begin 
        Drop Table [dbo].[InactiveIDs]
    End

Create Table [dbo].[InactiveIDs] (
    ContactId UniqueIdentifier Not Null,
    ID Varchar(50),
    EmailAddress VarChar(255) Not Null
)

Create Index IX_ContactIdEmailAddress
    On [dbo].[InactiveIDs] (ContactId, EmailAddress)

这是格式文件文件:

13.0
3
1       SQLUNIQUEID         1       36      ","        1     ContactId                    ""
2       SQLCHAR             2       50      ","        2     ID                           SQL_Latin1_General_CP1_CI_AS
3       SQLCHAR             2       255     "\r\n"     3     EmailAddress                 SQL_Latin1_General_CP1_CI_AS

以下是一些示例数据:

CBD60121-C5E1-E511-B2B4-005056820129,199e6799e3c64b06a87e86a5047e5f41,[e-mail]
3D22A4C2-507B-E411-99C7-005056820126,76410ce5beab4a7da943b95b3de3b0c1,[e-mail]
AE5B9335-B126-E611-ABF2-005056820020,e7d181abdf154f79b5dcaa4d64fec7f7,[e-mail]
93F94F65-FA2A-E311-87A7-005056B5025F,3e2fae28cace4f068fa670879d7807e3,[e-mail]
0A41305C-C087-E411-A37D-00505682001E,55b0162742b04a369c1c57d8d917d45c,[e-mail]

这是我的命令行:

bcp.exe dbo.InactiveIDs in InactiveIDs.csv -f InactiveIDs.bcp.fmt -T -S SqlServerName -d DatabaseName

以下是输出:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC Driver 13 for SQL Server]String data, right truncation

BCP copy in failed

推荐答案

Microsoft官方文档解释了

The official Microsoft doc explains what a Non-XML Format File consist of. I see 2 possible problems with yours, both with the first field:
1. Column 4 (Host file data length) should be 37, if you open the page that explains each data type and you go to uniqueidentifier, it says 37 - now I copy the values from your sample data till the first comma to Notepad and they are 37 characters long.
2. Maybe the type SQLUNIQUEID is also wrong and should be SQLCHAR. The doc says for columns 2 & 3 that these should be the same as the ones used to do the extract. I don't know if the extract was done with native data type but it seems to me that the first field is also text. So if point 1. modification still fails I would try SQLCHAR with a Prefix length of zero for all fields.

这篇关于不断收到导入错误“字符串数据，正确的截断"；使用BCP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！