我在SQL Server中通过查询分析器读取Excel某个表的数据出现了错误,请指教SQL语句如下:select *
from opendatasource('Microsoft.Jet.OLEDB.4.0','Data source="e:\aa.xls";user id=admin;password=;Extended properties=Excel 5.0')...[sheet1$]结果系统给出如下提示:OLE DB提供程序'Microsoft.Jet.OLEDB.4.0'报错.
[OLE/DB provider returned message:找不到可安装的ISAM.]请问问题该如何解决呢?
from opendatasource('Microsoft.Jet.OLEDB.4.0','Data source="e:\aa.xls";user id=admin;password=;Extended properties=Excel 5.0')...[sheet1$]结果系统给出如下提示:OLE DB提供程序'Microsoft.Jet.OLEDB.4.0'报错.
[OLE/DB provider returned message:找不到可安装的ISAM.]请问问题该如何解决呢?
1、向Excel查询select * from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]') where c like '%f%'select * from
OPENROWSET('MICROSOFT.JET.OLEDB.4.0'
,'Excel 5.0;HDR=YES;IMEX=2;DATABASE=c:\book1.xls',[sheet1$])1)hdr=yes时可以把xls的第1行作为字段看待,如第1个中hdr=no的话,where时就会报错
2)[]和美圆$必须要,否则M$可不认这个账2、修改Execlupdate OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet1$]')
set a='erquan' where c like '%f%'3、导入导出
insert into OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;hdr=yes;database=c:\book1.xls;','select * from [Sheet2$]')(id,name)
select id,name from serv_user或BCPmaster..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\book2.xls" -c -q -S"." -U"sa" -P"sa"'从Excel向SQLServer导入:select * into serv_user_bak
from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]')
select from OpenRowSet('microsoft.jet.oledb.4.0','Excel 8.0;HDR=yes;database=c:\book1.xls;','select * from [Sheet1$]')