不断收到导入错误“字符串数据,正确的截断";使用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,someone@verizon.com
3D22A4C2-507B-E411-99C7-005056820126,76410ce5beab4a7da943b95b3de3b0c1,someone@gmail.com
AE5B9335-B126-E611-ABF2-005056820020,e7d181abdf154f79b5dcaa4d64fec7f7,someone@yahoo.com
93F94F65-FA2A-E311-87A7-005056B5025F,3e2fae28cace4f068fa670879d7807e3,someone@juno.com
0A41305C-C087-E411-A37D-00505682001E,55b0162742b04a369c1c57d8d917d45c,someoneelse@yahoo.com
这是我的命令行:
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
推荐答案
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屋!