22,294
社区成员
发帖
与我相关
我的任务
分享向 SQLServer2000动态导入Excel的存储过程如何实现 100分！
create procedure import
(
@tablename varchar(100),
@filepath varchar(100)
)
as
INSERT INTO @tablename SELECT * FROM OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','Excel 8.0; HDR=YES; Database ='+@filepath,sheet1$)
查询分析器总是提示需要声明变量@tablename，‘+’附近有错误，请各位大侠指教
(
@tablename varchar(100),
@filepath varchar(100)
)
as
INSERT INTO @tablename SELECT * FROM OPENROWSET
('MICROSOFT.JET.OLEDB.4.0','Excel 8.0; HDR=YES; Database ='+@filepath,sheet1$)
查询分析器总是提示需要声明变量@tablename，‘+’附近有错误，请各位大侠指教
...全文
请发表友善的回复…
发表回复
diffmaker 2009-05-11
- 打赏
- 举报
学习了
長胸為富 2009-05-07
- 打赏
- 举报
--测试通过
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * FROM
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'')...sheet1$'
) --sheet1$ 要换成你要到的工作表名称, $符号也要
exec import 'abc','D:\abc.xls'
a207988 2009-05-07
- 打赏
- 举报
很好啊，学会用代码导入了
zrpxxx 2009-05-07
- 打赏
- 举报
顶！！！
qizilong 2009-05-07
- 打赏
- 举报
[Quote=引用 57 楼 duanze82 的回复:]
SQL code
--测试通过
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * FROM
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'')...sheet1$'
) --sheet1$ 要换成你要到的工作表名称, $符号也要
exec import 'abc','D:\abc.…
[/Quote]
我试了一下，只是sheet1$不要改动，可以正确执行，谢谢楼上各位大侠
SQL code
--测试通过
Create procedure import
@tablename varchar(100),
@filepath varchar(100)
as
EXEC( 'INSERT INTO ' + @tablename +
' SELECT * FROM
OpenDataSource( ''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @filepath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'')...sheet1$'
) --sheet1$ 要换成你要到的工作表名称, $符号也要
exec import 'abc','D:\abc.…
[/Quote]
我试了一下，只是sheet1$不要改动，可以正确执行，谢谢楼上各位大侠
qizilong 2009-05-06
- 打赏
- 举报
[Quote=引用 23 楼 Tomzzu 的回复:]
LZ忽略了两个问题
未闭合的引号是因为你的每二对双引号(")从"Excel8.0 开始之后就没有结束
从Excel读文件首先要在Sheet里对要读的数据选中命名一个空间名, 然后通过MSSQL访问时写明, Excel文件名, 所属Sheet, 所属名空间NameSpace
[/Quote]
不是双引号，是两个单引号。SQL语句里本身有单引号引起来的，现在算是两层引用，外面一层是单引号引起来的，里面一层也是单引号引起来的，我不知道单引号该怎么嵌套，楼上各位大侠用的都是两个连续的单引号。
LZ忽略了两个问题
未闭合的引号是因为你的每二对双引号(")从"Excel8.0 开始之后就没有结束
从Excel读文件首先要在Sheet里对要读的数据选中命名一个空间名, 然后通过MSSQL访问时写明, Excel文件名, 所属Sheet, 所属名空间NameSpace
[/Quote]
不是双引号，是两个单引号。SQL语句里本身有单引号引起来的，现在算是两层引用，外面一层是单引号引起来的，里面一层也是单引号引起来的，我不知道单引号该怎么嵌套，楼上各位大侠用的都是两个连续的单引号。
deepsky725 2009-05-06
- 打赏
- 举报
牛人真多！
jjjiaml 2009-05-06
- 打赏
- 举报
学习
Munko 2009-05-06
- 打赏
- 举报
学习
lovezx1028 2009-05-06
- 打赏
- 举报
学习来啦。。
gentlyxu 2009-05-06
- 打赏
- 举报
学习中～～
邓芳 2009-05-06
- 打赏
- 举报
呵呵，搞定了。
过程是这样的。
首先要在库中建立一个表，这个表要和excel的字段名对应。
我们在操作的时候是不用建立表的，但是在写存储过程的时候需要建立表，这点是比较麻烦。
然后写存储过程，4楼的牛人已经写的很清楚了，我这里再写一遍哈哈。
CREATE PROCEDURE spExcelOutIn
@strOptions varchar(200) = NULL,
@strWhere nvarchar(4000) = NULL,--导入/导出查询条件(包括Where 关键字)
@strExcelPath nvarchar(1000) = NULL,--Excel的绝对路径
@strExcelSheetName nvarchar(50) = NULL,--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$
@strExcelFiled nvarchar(1000) = NULL,--Excel工作表的字段
@strSqlTableName nvarchar(100) = NULL,--Sql数据库导入/导出表名
@strSqlFiled nvarchar(1000) = NULL--Sql表的字段
AS
DECLARE @strSql nvarchar(4000)
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp
/* 导入 */
IF @strOptions='In' AND NOT @strExcelPath IS NULL BEGIN
SET @strExcelPath = '''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @strExcelPath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'''
--将数据存放到临时表(一)
SET @strSql =
'SELECT ' + @strExcelFiled +
' INTO ##Temp
FROM
OpenDataSource('+ @strExcelPath +')...'+ @strExcelSheetName +'$ '
+@strWhere
--PRINT(@strSql)
EXEC(@strSql)
--将数据从临时表导入到sql数据库表(二,分两步可以做更多的处理)
SET @strSql = 'INSERT INTO ' + @strSqlTableName + '('+ @strSqlFiled +') SELECT * FROM ##Temp'
--PRINT(@strSql)
EXEC(@strSql)
--SELECT * FROM ##Temp
DROP TABLE ##Temp
END
/* 导出 */
IF @strOptions='Out' AND NOT @strExcelPath IS NULL BEGIN
SET @strExcelPath = ''
--导成类似（不是真正的Excel，是文本格式）Excel的文件,这里是固定的，真正用时需要修改相关参数
--EXEC master..xp_cmdshell 'bcp "SELECT * FROM wy_福州分公司.dbo.TExcelOutIn" queryout C:\test.xls -c -S"(local)" -U"sa" -P"123"'
END
GO
下一步是调用。
SET ANSI_WARNINGS ON--返回警告
GO
SET ANSI_NULLS ON-- 指定在对空值使用等于 (=) 和不等于 (<>) 比较运算符时，这些运算符的 SQL-92 遵从行为。
GO
--将Excel数据导入到sql数据库
--版权 : 启程 letwego.cn
--调用实例
EXEC spExcelOutIn
@strOptions = 'In',
@strWhere = 'WHERE sid>0',--导入/导出查询条件(包括Where 关键字)，这里是excel表中的字段也是sql表中的字段。
@strExcelPath = 'd:\a.xls',--Excel的绝对路径
@strExcelSheetName = 'Sheet1',--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$
@strExcelFiled = 'sid,sno,sname',--Excel工作表的字段
@strSqlTableName = 't_test',--Sql数据库导入表名
@strSqlFiled = 'sid,sno,sname'--Sql表的字段
过程是这样的。
首先要在库中建立一个表，这个表要和excel的字段名对应。
我们在操作的时候是不用建立表的，但是在写存储过程的时候需要建立表，这点是比较麻烦。
然后写存储过程，4楼的牛人已经写的很清楚了，我这里再写一遍哈哈。
CREATE PROCEDURE spExcelOutIn
@strOptions varchar(200) = NULL,
@strWhere nvarchar(4000) = NULL,--导入/导出查询条件(包括Where 关键字)
@strExcelPath nvarchar(1000) = NULL,--Excel的绝对路径
@strExcelSheetName nvarchar(50) = NULL,--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$
@strExcelFiled nvarchar(1000) = NULL,--Excel工作表的字段
@strSqlTableName nvarchar(100) = NULL,--Sql数据库导入/导出表名
@strSqlFiled nvarchar(1000) = NULL--Sql表的字段
AS
DECLARE @strSql nvarchar(4000)
IF OBJECT_ID('tempdb..##Temp') IS NOT NULL DROP TABLE ##Temp
/* 导入 */
IF @strOptions='In' AND NOT @strExcelPath IS NULL BEGIN
SET @strExcelPath = '''Microsoft.Jet.OLEDB.4.0'',''Data Source="' + @strExcelPath + '";User ID=Admin;Password=;Extended properties=Excel 5.0'''
--将数据存放到临时表(一)
SET @strSql =
'SELECT ' + @strExcelFiled +
' INTO ##Temp
FROM
OpenDataSource('+ @strExcelPath +')...'+ @strExcelSheetName +'$ '
+@strWhere
--PRINT(@strSql)
EXEC(@strSql)
--将数据从临时表导入到sql数据库表(二,分两步可以做更多的处理)
SET @strSql = 'INSERT INTO ' + @strSqlTableName + '('+ @strSqlFiled +') SELECT * FROM ##Temp'
--PRINT(@strSql)
EXEC(@strSql)
--SELECT * FROM ##Temp
DROP TABLE ##Temp
END
/* 导出 */
IF @strOptions='Out' AND NOT @strExcelPath IS NULL BEGIN
SET @strExcelPath = ''
--导成类似（不是真正的Excel，是文本格式）Excel的文件,这里是固定的，真正用时需要修改相关参数
--EXEC master..xp_cmdshell 'bcp "SELECT * FROM wy_福州分公司.dbo.TExcelOutIn" queryout C:\test.xls -c -S"(local)" -U"sa" -P"123"'
END
GO
下一步是调用。
SET ANSI_WARNINGS ON--返回警告
GO
SET ANSI_NULLS ON-- 指定在对空值使用等于 (=) 和不等于 (<>) 比较运算符时，这些运算符的 SQL-92 遵从行为。
GO
--将Excel数据导入到sql数据库
--版权 : 启程 letwego.cn
--调用实例
EXEC spExcelOutIn
@strOptions = 'In',
@strWhere = 'WHERE sid>0',--导入/导出查询条件(包括Where 关键字)，这里是excel表中的字段也是sql表中的字段。
@strExcelPath = 'd:\a.xls',--Excel的绝对路径
@strExcelSheetName = 'Sheet1',--Excel里要导入的工作表名称 如Sheet2,注意,后面不要加上$
@strExcelFiled = 'sid,sno,sname',--Excel工作表的字段
@strSqlTableName = 't_test',--Sql数据库导入表名
@strSqlFiled = 'sid,sno,sname'--Sql表的字段
xu_xiao_jing_1 2009-05-06
- 打赏
- 举报
mark!
邓芳 2009-05-06
- 打赏
- 举报
create procedure import1
(
@tablename varchar(100),
@filepath varchar(100)
)
as
EXEC('INSERT INTO ' --要留个空格
+ @tablename
+' SELECT * FROM OPENROWSET (''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0; HDR=YES; Database ='+@filepath+',sheet1$)'
)
这个执行成功了，产生一个存储过程，但是执行的时候。
exec import 'tablename','D:\a.xls'
a.xls是存在的，但是sql编辑器提示
务器: 消息 208，级别 16，状态 1，行 1
对象名 'tablename' 无效。
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪［OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给出有关错误的任何信息。］。
怎么解决？
(
@tablename varchar(100),
@filepath varchar(100)
)
as
EXEC('INSERT INTO ' --要留个空格
+ @tablename
+' SELECT * FROM OPENROWSET (''MICROSOFT.JET.OLEDB.4.0'',''Excel 8.0; HDR=YES; Database ='+@filepath+',sheet1$)'
)
这个执行成功了，产生一个存储过程，但是执行的时候。
exec import 'tablename','D:\a.xls'
a.xls是存在的，但是sql编辑器提示
务器: 消息 208，级别 16，状态 1，行 1
对象名 'tablename' 无效。
服务器: 消息 7399，级别 16，状态 1，行 1
OLE DB 提供程序 'MICROSOFT.JET.OLEDB.4.0' 报错。提供程序未给出有关错误的任何信息。
OLE DB 错误跟踪［OLE/DB Provider 'MICROSOFT.JET.OLEDB.4.0' IDBInitialize::Initialize returned 0x80004005: 提供程序未给出有关错误的任何信息。］。
怎么解决？
bigpretty 2009-05-06
- 打赏
- 举报
顶了
邓芳 2009-05-06
- 打赏
- 举报
每天来CSDN都有收获。真是很不错！
wym0807 2009-05-06
- 打赏
- 举报
顶！！！
LichKing 2009-05-06
- 打赏
- 举报
study
happy664618843 2009-05-06
- 打赏
- 举报
mark mark!
fcuandy 2009-05-06
- 打赏
- 举报
以前发的总结贴，楼主肯定没看过。
加载更多回复（37）
