--将某个目录上的Excel表,导入到数据库中
--将所有的Excel文件放到一个目录中,假设为c:/test/,然后用下面的方法来做
create table #t(fname varchar(260),depth int,isf bit)
insert into #t exec master..xp_dirtree 'c:/test',1,1
declare tb cursor for select fn='c:/test'+fname from #t
where isf=1 and fname like '%.xls' --取.xls文件(EXCEL)
declare @fn varchar(8000)
open tb
fetch next from tb into @fn
while @@fetch_status=0
begin
--下面是查询语句,需要根据你的情况改为插入语句
--插入已有的表用:insert into 表 selct * from ...
--创建表用:select * into 表 from ...
set @fn='select * from
OPENROWSET(''MICROSOFT.JET.OLEDB.4.0'',''Excel 5.0;HDR=YES;DATABASE='+@fn+''',全部客户$)'
exec(@fn)
fetch next from tb into @fn
end
close tb
deallocate tb
drop table #t
访问excel
1)、inert into A SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0','Data Source="E:/联系方式.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')... [Sheet1$]
但此时查询结果得顺序是按照列名得顺序排列的,而不是按照电子表格中原有的顺序。这是 SQL Server 2000 行集函数 OpenRowSet 和 OpenDataSource 本身的问题,与访问接口引擎无关,也与 Excel 版本无关。SQL Server 2005 的 OpenRowSet 和 OpenDataSource 不存在这个问题。
2)、inert into A select * from OpenRowSet('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=Yes;Database=E:/联系方式.xls', 'select * from [Sheet1$]')
此种方法可以解决opendatasurce得问题,即查询结果列得顺序与电子表格中原有顺序一致
3)、使用链接服务器:
exec sp_addlinkedserver @server='xlsserver',@srvproduct='jet4.0',@provider='microsoft.jet.oledb.4.0',@datasrc='E:/联系方式.xls',@provstr='excel 8.0'
inert into A select * from xlsserver...[Sheet1$]
此时查询结果中列得顺序是按照列名得顺序排列的。
有500多个csv文件,想导入SQL数据库内,数据库字段与文件字段是一样的,
如何写批量导入语句。
DECLARE @dir sysname,@cmd nvarchar(4000);
SET @dir = 'C:/';
CREATE TABLE #tmp(filename nvarchar(1024));
SET @cmd = N'dir "' + @dir + '*.csv" /B'
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd;
DELETE #tmp WHERE filename IS NULL;
IF EXISTS(SELECT * FROM #tmp WHERE filename LIKE '%找不到文件%')
BEGIN
RAISERROR('找不到文件',16,1)
END
ELSE
BEGIN
DECLARE @SQL nvarchar(MAX);
SET @SQL = '';
SELECT @SQL = @SQL + 'EXEC xp_cmdshell N''bcp 数据库.dbo.表 in "' + @dir + filename
+ '" -w -T -t, -r/n'';' + CHAR(13) + CHAR(10)
FROM #tmp;
EXEC(@SQL);
END
DROP TABLE #tmp;
用动态sql
--以下是别人写的,
--第一步,先获取某一个文件夹下的所有txt 文件
create table tmp_filelist (cdrfile varchar(64))
go
exec master.dbo.xp_cmdshell 'dir D:/data.db/4006/400. <YYYYMM>/*.txt /s/b > D:/data.db/4006/400. <YYYYMM>/list'
-- -s:表示该文件夹下所有文本文件,包括子文件夹
-- -b:当前文件夹所有文本文件
-- >:表示把当前文件列表存入指定的文件中
go
bulk insert tmp_filelist from N'D:/data.db/4006/400. <YYYYMM>/list'
--用药bulk insert 命令把文件列表的文件名存入 数据表中
go
--第二步,利用游标把每一文件导入数据库中
--他这里是用bulk insert ,也可以用
--select * from opendatasource('microsoft.jet.oledb.4.0','text;hdr=no;database=路径')...[文件名#txt]
truncate table tmp_cdr4006csv
declare @cdrfile varchar(64)
declare @sql nvarchar(256)
declare @cmd varchar(512)
declare cur_fl cursor for select * from tmp_filelist
open cur_fl
fetch next from cur_fl into @cdrfile
while @@fetch_status!=-1 begin
set @sql ='BULK INSERT tmp_cdr4006csv FROM N'''+@cdrfile+''' WITH ( FIELDTERMINATOR = '','')' exec sp_executesql @sql
fetch next from cur_fl into @cdrfile
end
close cur_fl
deallocate cur_fl
if exists(select 1 from sysobjects where name='file2table' and objectproperty(id,'isprocedure')=1)
drop procedure file2table
go
create procedure file2table
@servername varchar(200) --服务器名称
,@username varchar(200) --用户名,如果用nt验证方式,则为空''
,@password varchar(200) --密码
,@tbname varchar(500) --数据库
,@filename varchar(1000) --导入/导出路径/文件名,如果@tbname参数指明是导出整个数据库,则这个参数是文件存放路径,文件名自动用表名.txt
,@isout bit --1为导出,0为导入
as
declare @sql varchar(8000)
------如果@tbname参数为ksoa类型,则默认导出整个数据库中的数据,如果@tbname参数为ksoa..spkfk则默认导出某个表的数据
begin --导出整个数据库,定义游标,取出所有的用户表
declare @m_tbname varchar(250)
if right(@filename,1)<>'/' set @filename=@filename+'/'--如果不是文件夹根目录,设置为根目录
set @m_tbname='declare #tb cursor for select name from '+@tbname+'.dbo.sysobjects where xtype=''u''
and (name not like ''wms_%'' and name not like ''HR_%'' and name not like ''tmp_%'')'
exec(@m_tbname)
open #tb
fetch next from #tb into @m_tbname
while @@fetch_status=0
begin
set @sql='bcp '+@tbname+'.'+'dbo.'+@m_tbname+case when @isout=1 then ' out ' else ' in ' end
+ @filename+@m_tbname+'.csv -w' -- 注意
+' /S '+@servername +case when isnull(@username,'')='' then '' else ' /U '+@username end
+' /P '+isnull(@password,'')
exec master.dbo.xp_cmdshell @sql
fetch next from #tb into @m_tbname
end
close #tb
deallocate #tb
end
go