批量导入ＣＳＶ文件，出错
set nocount on
declare @sql varchar(2000),@id1 int,@id2 int
create table #T (id int identity(1,1),col varchar(500))
insert into #T exec master..xp_cmdshell 'dir e:\1004\*.csv '
delete from #T where col not like '%.csv% ' or col is null or substring(col,37,2) <> '91 '
update #T set col=replace(col,left(col,36), ' ')
select * from #T
select @id1=min(id),@id2=max(id) from #T
while @id1 <[email protected]
begin
select @sql= 'bcp sxl..zcfzb in e:\1004\ '+col+ ' -c -q -S "SXL100 " -U "sa " -P " " '
from #T where [email protected]
set @[email protected]+1
exec master..xp_cmdshell @sql
end
提示:
NULL
开始复制...
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]在 BCP 数据文件中遇到的意外的 EOF
NULL
已复制了 0 行。
数据包的大小（字节）: 4096
时钟时间（毫秒）: 共 1
NULL
------解决方案--------------------
bcp:
select @sql= 'bcp sxl..zcfzb in e:\1004\ '+col+ ' -c -q -S "SXL100 " -U "sa " -P " " '
这句好像有问题,[email protected]?
bulk
exec master..xp_cmdshell 'BULK INSERT zcfzb FROM e:\1004\ '+col+ ' WITH(FIELDTERMINATOR = ', ') '
应该为
exec master..xp_cmdshell 'BULK INSERT zcfzb FROM e:\1004\ '+col+ ' WITH(FIELDTERMINATOR = ' ', ' ') ',col同上