--开启“即席远程查询“
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go
--查询excel文件
insert into InvVenPrice
select * from openrowset('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=C:\InvVenPrice.xls;','select * from [sheet1$]')
--关闭“即席远程查询“
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
go
--查询excel文件
insert into InvVenPrice
select * from openrowset('microsoft.jet.oledb.4.0','excel 8.0;hdr=yes;database=C:\InvVenPrice.xls;','select * from [sheet1$]')
--关闭“即席远程查询“
exec sp_configure 'Ad Hoc Distributed Queries',0
reconfigure
exec sp_configure 'show advanced options',0
reconfigure
错误可能性:
1、表单名错误;解决办法:改正表单名字
2、表单在本地,而数据库是远程的。。解决办法:将excel传到服务器上,再进行操作。
1、在SQL SERVER里查询Excel数据:
-- ======================================================
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
下面是个查询的示例,它通过用于 Jet 的 OLE DB 提供程序查询 Excel 电子表格。
SELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactionsSELECT *
FROM OpenDataSource ( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\财务统计科目.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...vib
-------------------------------------------------------------------------------------------------2、将Excel的数据导入SQL server :
-- ======================================================
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\book1.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...[Sheet1$]
实例:
SELECT * into newtable
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\Finance\account.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions
2。检查C:\InvVenPrice.xls文件中是否有 sheet1$档。
3。检查InvVenPrice.xls文档是否被打开。
INSERT INTO CBSJCJL
SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="D:\检查表主表.xls";User ID=Admin;Password=;Extended properties=Excel 8.0')...[检查表主表$]--这是我用过的一个sql 你可以改改试试!
什么意思?
select * from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=yes;IMEX=1;databASE=C:\InvVenPrice.xls', 'SELECT * FROM [sheet1$]')
也就是看你InvVenPrice.xls这个文件里面的表单名称是否为sheet1
insert into InvVenPrice
select * from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;HDR=yes;IMEX=1;databASE=C:\InvVenPrice.xls', 'SELECT * FROM [sheet1$]')
/* 其实简单
<1>关闭要导入的Execl
<2>Execl里要有 sheet名称!
<3>InvVenPrice.xls 是否存在!
*/
就把有数据的那个sheet页。改个名字!
insert into InvVenPrice
select * from
openrowset('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;databASE=C:\InvVenPrice.xls;HDR=Yes;IMEX=1', InvVenPrice $)