SQL Server:批量加载失败.数据文件中该列对于第1行，第1列过长 [英] SQL Server: The bulk load failed. The column is too long in the data file for row 1, column 1
问题描述
有人请在这里帮助我.现在已经看了几个小时，却一无所获.
Someone please help me here. Been looking at this for a couple of hours now but leading to nowhere.
我使用以下脚本在SQL Express 2008 R2中创建了一个表:
I created a table in SQL Express 2008 R2 using the following script:
CREATE TABLE Features
(
ID int not null identity(1,1 ),
StopID varchar(10),
Code int,
Name varchar(100),
Summary varchar(200),
Lat real,
Lon real,
street varchar(100),
city varchar(50),
region varchar(50),
postcode varchar(10),
country varchar(20),
zone_id varchar(20),
the_geom geography
CONSTRAINT [PK_Features] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
然后，我使用bcp工具针对数据库表创建了以下格式文件:
Then I created the following format file created against my database table using the bcp tool:
10.0
12
1 SQLCHAR 2 100 "," 2 StopID Latin1_General_CI_AS
2 SQLINT 1 4 "," 3 Code ""
3 SQLCHAR 2 100 "," 4 Name Latin1_General_CI_AS
4 SQLCHAR 2 200 "," 5 Summary Latin1_General_CI_AS
5 SQLFLT4 1 4 "," 6 Lat ""
6 SQLFLT4 1 4 "," 7 Lon ""
7 SQLCHAR 2 100 "," 8 street Latin1_General_CI_AS
8 SQLCHAR 2 50 "," 9 city Latin1_General_CI_AS
9 SQLCHAR 2 50 "," 10 region Latin1_General_CI_AS
10 SQLCHAR 2 10 "," 11 postcode Latin1_General_CI_AS
11 SQLCHAR 2 20 "," 12 country Latin1_General_CI_AS
12 SQLCHAR 2 20 "\r\n" 13 zone_id Latin1_General_CI_AS
此文件已被修改为删除ID和the_geom字段，因为它们不在我的数据文件中.
This file has been modified to remove the ID and the_geom fields as these are not in my data file.
然后我尝试批量插入包含以下内容的1行csv:
Then I tried to bulk insert a 1 line csv with the following content:
a,8,S,,45.439869,-75.695839,,,,,,
我得到的一切:
Msg 4866, Level 16, State 7, Line 35
The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 35
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 35
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
任何指针都将在这里有所帮助，因为我无法弄清楚这一点.
Any pointers will help here as I cannot figure this one out.
推荐答案
问题是由我的格式文件中的默认前缀长度设置引起的.我导入的数据文件不是使用bcp创建的，因此我必须将所有字段的前缀长度设置为0，如下所示:
Problem was caused by the default prefix length settings in my format file. The data file I am importing from was not created using bcp so I had to set the prefix length of all the fields to 0 as follows:
0.0
12
1 SQLCHAR 0 100 "," 2 StopID Latin1_General_CI_AS
2 SQLINT 0 4 "," 3 Code ""
3 SQLCHAR 0 100 "," 4 Name Latin1_General_CI_AS
4 SQLCHAR 0 200 "," 5 Summary Latin1_General_CI_AS
5 SQLFLT4 0 4 "," 6 Lat ""
6 SQLFLT4 0 4 "," 7 Lon ""
7 SQLCHAR 0 100 "," 8 street Latin1_General_CI_AS
8 SQLCHAR 0 50 "," 9 city Latin1_General_CI_AS
9 SQLCHAR 0 50 "," 10 region Latin1_General_CI_AS
10 SQLCHAR 0 10 "," 11 postcode Latin1_General_CI_AS
11 SQLCHAR 0 20 "," 12 country Latin1_General_CI_AS
12 SQLCHAR 0 20 "\r\n" 13 zone_id Latin1_General_CI_AS
通过此更改，导入成功.
With this change, the import was succesful.
这篇关于SQL Server:批量加载失败.数据文件中该列对于第1行，第1列过长的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
