bulk insert 大批量导入二三十万条数据出错

消息 4864，级别 16，状态 1，第 1 行
第 1 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 2 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 3 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 4 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 5 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 6 行、第 18 列(调节后ftp利息)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 7 行、第 20 列(调节后ftp利润)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 8 行、第 18 列(调节后ftp利息)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 9 行、第 18 列(调节后ftp利息)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 10 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4864，级别 16，状态 1，第 1 行
第 11 行、第 18 列(调节后ftp利息)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。
消息 4865，级别 16，状态 1，第 1 行
由于超过了最大错误数(10)，无法进行大容量加载。
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "BULK" 报错。提供程序未给出有关错误的任何信息。
消息 7330，级别 16，状态 2，第 1 行
无法从链接服务器 "(null)" 的 OLE DB 访问接口"BULK"提取行。

36 个解决方案

#1

Create Table Account
(
  产品码 varchar(20) NOT NULL ,
  产品名称 varchar(50) NOT NULL ,  
  客户经理编号 varchar(10)  ,  
  客户经理名称 varchar(20)  , 
  部门编号 varchar(50) NOT NULL ,
  部门名称 varchar(50) ,  
  客户号 varchar(30) NOT NULL ,
  客户名称 varchar(50) NOT NULL ,
  账户 varchar(30) NOT NULL ,
  起息日 varchar(30)  ,
  到期日 varchar(20)  ,
  当前余额 decimal(18 ,2) ,
  月日均 decimal(18 ,2) ,
  对客利率实际 decimal(18 ,2) ,
  对客利率反算 decimal(18 ,2) ,
  对客利息 decimal(18 ,2) ,
  调节后ftp利率 decimal(18 ,2) ,
  调节后ftp利息 decimal(18 ,2) ,
  调节后ftp利差 decimal(18 ,2) ,
  调节后ftp利润 decimal(18 ,2) ,
  数据日期 varchar(20) NOT NULL 
 )
bulk insert Account from 'D:\test2\App_Data\Account.txt' 
  with (fieldterminator=',',rowterminator='
')

#2

你导前 100 行试试，看看还有没有这个提示？

#3

导数据一点不规范，这类导入最好指定格式化文件导入
不指定时，有时会造成列顺序乱

--查看一下显示格式有没有问题
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\test2\App_Data\','select * from Account.txt') 

#4

引用 2 楼 wmxcn2000 的回复:

你导前 100 行试试，看看还有没有这个提示？

可以导入的，前面差不多十几万条数据都可以

#5

你这个可以用错误日志来定位错误的位置，估计是数据问题引起的，但是也应该像版主说的那样用格式化文件来控制导入的数据。

#6

用#3 语句查看方法，生成临时表，把临时表拆成表结构再验证列值和类型是否匹配

看提示应该是某一行数据转换类型时出列

#7

引用 3 楼 roy_88 的回复:

导数据一点不规范，这类导入最好指定格式化文件导入
不指定时，有时会造成列顺序乱

--查看一下显示格式有没有问题
select * from OpenRowset('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=D:\test2\App_Data\','select * from Account.txt') 

显示：
消息 15281，级别 16，状态 1，第 1 行
SQL Server 阻止了对组件 'Ad Hoc Distributed Queries' 的 STATEMENT'OpenRowset/OpenDatasource' 的访问，因为此组件已作为此服务器安全配置的一部分而被关闭。系统管理员可以通过使用 sp_configure 启用 'Ad Hoc Distributed Queries'。有关启用 'Ad Hoc Distributed Queries' 的详细信息，请参阅 SQL Server 联机丛书中的 "外围应用配置器"。 

#8

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

这样设置启用即席分布式查询就行了

#9

引用 8 楼 roy_88 的回复:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

这样设置启用即席分布式查询就行了

设置成功。查询结果：
消息 492，级别 16，状态 1，第 1 行
在通过 OPENQUERY 和 OPENROWSET 获得的结果集中不允许有重复的列名。列名 "540000" 是重复的。

#10

引用 8 楼 roy_88 的回复:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

这样设置启用即席分布式查询就行了

这是什么原因呢?列名 "540000" ,没有这个列名啊。
另奇怪的是我把这个txt文件里的数据分成两部分都可以导入了。但是需要整个文件一次性导入啊

#11

查询都报错？
应该会生成一列会把第1行当作列名

方法1：
这样用
你bulk insert数据导入到临时表(定义所有列都为nvarchar(max))
导入成功后再查数据

看你的提示应该是某一行数据类型有问题或超出类型范围值

--如果你分开两个文件可以成功，你是否调整过文件数据

方法2：用表生成格式化文件，把格式化文件导入试试，问题估计出在类型默认转换上

#12

引用 11 楼 roy_88 的回复:

查询都报错？
应该会生成一列会把第1行当作列名

方法1：
这样用
你bulk insert数据导入到临时表(定义所有列都为nvarchar(max))
导入成功后再查数据

看你的提示应该是某一行数据类型有问题或超出类型范围值

--如果你分开两个文件可以成功，你是否调整过文件数据

方法2：用表生成格式化文件，把格式化文件导入试试，问题估计出在类型默认转换上

该txt文件数据第一行没有列名，直接就是数据

临时表导入出错：
消息 4832，级别 16，状态 1，第 1 行
大容量加载: 在数据文件中遇到意外的文件结尾。
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "BULK" 报错。提供程序未给出有关错误的任何信息。
消息 7330，级别 16，状态 2，第 1 行
无法从链接服务器 "(null)" 的 OLE DB 访问接口"BULK"提取行。

没有调整过数据，直接取一部分复制粘贴的


格式化文件，额。。。求第一步，那个文件该怎么建立

bcp Author..Account in D:\test2\App_Data\Account.txt -f C:\account.Xml -T

这样报错：
消息 102，级别 15，状态 1，第 1 行
'.' 附近有语法错误。

#13

--创建格式 
EXEC master.dbo.xp_cmdshell 'bcp Author.dbo.Account format nul -c -x -f "D:\Account.xml" -t, -T'

#14

内容里有 多余的分隔符吧

#15

引用 13 楼 roy_88 的回复:

--创建格式 
EXEC master.dbo.xp_cmdshell 'bcp Author.dbo.Account format nul -c -x -f "D:\Account.xml" -t, -T'

bulk insert Account from 'D:\test2\App_Data\Account.txt' 
  with (fieldterminator=',', formatfile = 'D:\Account.xml',   rowterminator='\r\n')
用了格式文件还报错（所有列都设为nvarchar(max)情况下）：
消息 4864，级别 16，状态 1，第 1 行
第 1 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。

#16

D:\Account.xml

<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
 <RECORD>
  <FIELD ID="1" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="3" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="10" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="50" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="100" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="10" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="30" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="11" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
  <FIELD ID="12" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="13" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="14" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="15" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="16" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="18" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="20" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH="41"/>
  <FIELD ID="21" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="20" COLLATION="Chinese_PRC_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="产品码" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="产品名称" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="客户经理编号" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="客户经理名称" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="部门编号" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="部门名称" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="7" NAME="客户号" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="8" NAME="客户名称" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="9" NAME="账户" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="10" NAME="起息日" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="11" NAME="到期日" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="12" NAME="当前余额" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="13" NAME="月日均" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="14" NAME="对客利率实际" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="15" NAME="对客利率反算" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="16" NAME="对客利息" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="17" NAME="调节后ftp利率" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="18" NAME="调节后ftp利息" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="19" NAME="调节后ftp利差" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="20" NAME="调节后ftp利润" xsi:type="SQLDECIMAL" PRECISION="18" SCALE="2"/>
  <COLUMN SOURCE="21" NAME="数据日期" xsi:type="SQLVARYCHAR"/>
 </ROW>
</BCPFORMAT>

#17

引用 14 楼 yooq_csdn 的回复:

内容里有 多余的分隔符吧

有什么办法能处理掉呢，让数据能顺利地按照
fieldterminator=',', rowterminator='\r\n'  导入

#18

看错误提示 是不是有特殊字符，比如二进制当字符串在文件里之类的

#19

查找一下文件有没有0x内容

#20

引用 19 楼 roy_88 的回复:

查找一下文件有没有0x内容

错误提示就只有这个：
消息 4864，级别 16，状态 1，第 1 行
第 1 行、第 19 列(调节后ftp利差)出现大容量加载数据转换错误(类型不匹配或者字符对于指定的代码页无效)。

查找过了 文件中没有0x内容

#21

调节后ftp利差--这一列数据类型转换有问题

<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH=" 1000"/>
<COLUMN SOURCE="19" NAME="调节后ftp利差" xsi:type=" SQLVARYCHAR"/>

这2段改改,用以上方法检查这列数据

 SELECT  调节后ftp利差
      FROM  OPENROWSET(BULK  'D:\test2\App_Data\Account.txt',
      FORMATFILE='D:\Account.xm'  ,FIRSTROW=2
       ) AS t1;

#22

引用 21 楼 roy_88 的回复:

调节后ftp利差--这一列数据类型转换有问题

<FIELD ID="19" xsi:type="CharTerm" TERMINATOR="," MAX_LENGTH=" 1000"/>
<COLUMN SOURCE="19" NAME="调节后ftp利差" xsi:type=" SQLVARYCHAR"/>

这2段改改,用以上方法检查这列数据

 SELECT  调节后ftp利差
      FROM  OPENROWSET(BULK  'D:\test2\App_Data\Account.txt',
      FORMATFILE='D:\Account.xm'  ,FIRSTROW=2
       ) AS t1;

又产生新问题了：
消息 4866，级别 16，状态 8，第 1 行
大容量加载失败。数据文件中第 1 行的第 21 列太长。请验证是否正确指定了字段终止符和行终止符。
消息 7301，级别 16，状态 2，第 1 行
无法从链接服务器 "(null)" 的 OLE DB 访问接口 "BULK" 获取所需的接口("IID_IColumnsInfo")。
截取部分数据：9.09,9.09,.35,0,0,6.643579,.013424,6.737844,.013424,2016-05-08
21列结尾是黑色方块，应该是回车的意思
TERMINATOR="
"   表现出来是这样
直接从数据里复制粘贴过来的，数据少的时候很正常，完整的一个txt导入就出问题了

#23

如果TXT文件格式不固定不好处理，最好先导入到临时表，把异常数据处理完再导入正式表

把临时表把类型都加大，最好都用字符串，然后验证类型

#24

引用 23 楼 roy_88 的回复:

如果TXT文件格式不固定不好处理，最好先导入到临时表，把异常数据处理完再导入正式表

把临时表把类型都加大，最好都用字符串，然后验证类型

Create Table Account2
(
  产品码 nvarchar(max)  ,
  产品名称 nvarchar(max) ,  
  客户经理编号 nvarchar(max)  ,  
  客户经理名称 nvarchar(max)  , 
  部门编号 nvarchar(max)  ,
  部门名称 nvarchar(max) ,  
  客户号 nvarchar(max) ,
  客户名称 nvarchar(max)  ,
  账户 nvarchar(max)  ,
  起息日 nvarchar(max)  ,
  到期日 nvarchar(max)  ,
  当前余额 nvarchar(max) ,
  月日均 nvarchar(max) ,
  对客利率实际 nvarchar(max) ,
  对客利率反算 nvarchar(max) ,
  对客利息 nvarchar(max) ,
  调节后ftp利率 nvarchar(max) ,
  调节后ftp利息 nvarchar(max) ,
  调节后ftp利差 nvarchar(max) ,
  调节后ftp利润 nvarchar(max) ,
  数据日期 nvarchar(max)  
 )
EXEC master.dbo.xp_cmdshell 'bcp Author.dbo.Account2 format nul -c -x -f "D:\Account2.xml" -t, -T'

bulk insert Account2 from 'D:\test2\App_Data\account1.txt' 
  with (fieldterminator=',', formatfile = 'D:\Account2.xml',   rowterminator='
')

出来的是14行乱码，奔溃

#25

格式化时 把-n改为 -w 生成格式文件加

NCharTerm--生成的类型为，可解决问号问题

#26

楼主你把数据脱敏后发出来，各位大神分分钟给你搞定。

#27

引用 25 楼 roy_88 的回复:

格式化时 把-n改为 -w 生成格式文件加

NCharTerm--生成的类型为，可解决问号问题

找到原因，乱码是因为行终止符错了，应该是“\n”的。不过用格式化文件导入，又出现了部分数据能导入，整个txt文件导入不了。
错误原因（表：产品码 varchar(max)，xml：取消了MAX_LENGTH）：
消息 4863，级别 16，状态 1，第 1 行
第 1 行、第 1 列(产品码)出现大容量加载数据转换错误(截断)。
消息 7399，级别 16，状态 1，第 1 行
链接服务器 "(null)" 的 OLE DB 访问接口 "BULK" 报错。提供程序未给出有关错误的任何信息。
消息 7330，级别 16，状态 2，第 1 行
无法从链接服务器 "(null)" 的 OLE DB 访问接口"BULK"提取行。

#28

自己想办法把TXT文件格式化，别总想把不规范的数据当规范格式导入，解决源头

以上回复了分析/解决问题方法，总结一下自己的问题按以上方法去解决

反反复复都是类型和格式引发的问题，问再多都没用，方法都在上面，不明时看一下联机

#29

引用 26 楼 yooq_csdn 的回复:

楼主你把数据脱敏后发出来，各位大神分分钟给你搞定。

8201010101,活期,00022A,AAA,33030100,33030100XXXXXXX,1003590XXX,XXX,XXXXXXXXXXXX,2016-05-08,2016-05-09,.48,.48,.35,0,0,6.643579,.000712,6.767708,.000712,2016-05-08
8201010101,活期,,,33030100,33030100XXXXXXX,102231XXXX,XXX,BBBBBBBBBBB,2016-05-08,2016-05-09,.45,.45,.35,0,0,6.643579,.000664,6.732222,.000664,2016-05-08
8201010101,活期,00064C,CC,33030100,33030100XXXXXXX,1005370XXX,DDD,GGGGGGGGGGG,2016-05-08,2016-05-09,282.99,282.99,.35,.32245,.02,6.643579,.417792,6.413393,.397792,2016-05-08

#30

引用 28 楼 roy_88 的回复:

自己想办法把TXT文件格式化，别总想把不规范的数据当规范格式导入，解决源头

以上回复了分析/解决问题方法，总结一下自己的问题按以上方法去解决

反反复复都是类型和格式引发的问题，问再多都没用，方法都在上面，不明时看一下联机

因为每天的数据都要导入啊，总不能改那么多txt吧，搞定一天的数据建个作业就好了。还是这个数据格式不行，要重新拿一份

#31

建议看下是不是数据格式不对  可以使用UE的批量替换来修改原文件使之符合要求

#32

楼主搞定吗？这样的情况可能是 换行符的问题。换行符不是 /r/n
试试我这句

DECLARE @cmd varchar(8000);
SET @cmd = 'BULK INSERT TTTT FROM ''c:\XXXXXX.txt'' WITH (FIELDTERMINATOR=''|'',ROWTERMINATOR ='''+CHAR(10)+''')'; 
PRINT @cmd;
EXEC(@cmd);

#33

或者 TERMINATOR="\r\n" 改成  TERMINATOR="\n" 或 TERMINATOR="\r" 试试

#34

引用 33 楼 yooq_csdn 的回复:

或者 TERMINATOR="\r\n" 改成  TERMINATOR="\n" 或 TERMINATOR="\r" 试试

换行符是这个：ROWTERMINATOR ='0x0A'

#35

引用 28 楼 roy_88 的回复:

自己想办法把TXT文件格式化，别总想把不规范的数据当规范格式导入，解决源头

以上回复了分析/解决问题方法，总结一下自己的问题按以上方法去解决

反反复复都是类型和格式引发的问题，问再多都没用，方法都在上面，不明时看一下联机

乱码是因为文本是UTF-8编码的，加上 CODEPAGE = 65001 就好了

#36

谢谢各位啦，散分了~~