导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

本地excel导入到远程sql问题...

yilee 2007-11-29 09:42:42
看完了相关的帖子,没找到具体的解决办法,所以只有请各位高手们..指点一下了...
我现在是本机上的E盘有个excel文件,想要导入服务器的sql中.(用的是2005)
用本机去连服务器的sql,在查询分析器里面输入:
insert OPENDATASOURCE('SQLOLEDB','Data Source=192.168.18.128;User ID=sa;Password='
).LMSPrint.dbo.biao2(yibaohao,name,sex,gongzi)
SELECT yibaohao,name,sex,gongzi
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\book2.xls";Extended properties=Excel 8.0')...sheet1$
出现错误:
消息 7314,级别 16,状态 1,第 1 行
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "sheet1$". The table either does not exist or the current user does not have permissions on that table.
在程序中的错误提示:(C# winform)
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

看了其它帖子,改成:
SELECT * INTO biao2 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;IMEX=1;Data Source=\\192.168.18.104\\e:\book2.xls;')...[Sheet1$](不知道data source的写法写错了没有.)
出现错误:
消息 7399,级别 16,状态 1,第 1 行
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
消息 7303,级别 16,状态 1,第 1 行
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
程序中的错误提示:
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" reported an error. The provider did not give any information about the error.
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

请各位高手帮帮忙..我被这个问题困扰了许久了,希望能给小弟一个具体的解决方法...小弟先谢谢各位了...
...全文
179 点赞 收藏 12
写回复
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
yilee 2007-11-30
谢谢你们..我自己用其他办法解决了...
回复
yilee 2007-11-29
就是在本机上有错``不知道怎么解决....
回复
fa_ge 2007-11-29

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\book2.xls";UserID=admin;password=;Extended properties=Excel 8.0')...sheet1$
回复
fa_ge 2007-11-29

SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Extended properties=Excel 8.0;Data Source=e:\book2.xls;User ID=admin;password='
)...sheet1$
我试过了,在服务器执行可以,但是在本机上出现以下错误:
消息 7314,级别 16,状态 1,第 1 行
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "sheet1$". The table either does not exist or the current user does not have permissions on that table.


---------------------------
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Data Source="e:\book2.xls";
User ID=admin;password=;'Extended properties=Excel 8.0')...sheet1$


回复
yilee 2007-11-29
我换了一下思路,我把excel读到了
DataTable dt = new DataTable();
dt里面,有没有把dt导入到指定的sql表中的方法呢?
不能循环,因为数据量太大了...


期待高手们的回答.......
回复
yilee 2007-11-29
谢谢 dawugui(潇洒老乌龟)
我是这么写的:
SELECT * FROM OPENDATASOURCE(
'Microsoft.Jet.OLEDB.4.0',
'Extended properties=Excel 8.0;Data Source=e:\book2.xls;User ID=admin;password='
)...sheet1$
我试过了,在服务器执行可以,但是在本机上出现以下错误:
消息 7314,级别 16,状态 1,第 1 行
The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)" does not contain the table "sheet1$". The table either does not exist or the current user does not have permissions on that table.

==================================================================================
期待.....
回复
yilee 2007-11-29
但是要求就是用代码来完成,最苦恼的就是要在本地去操作服务器,用DTS也只能在服务器上操作...
有没有解决这个问题的办法啊??
回复
fa_ge 2007-11-29
建议楼主不要用代码,直接用dts 导入,我就是这样用的
回复
yilee 2007-11-29
谢谢..我试试~~
回复
dawugui 2007-11-29
我觉得应该是本地SQL导入远程的excel文件.

注册一个连接后用下面的语句:

--导出excel处理
declare @s nvarchar(4000)
set @s='bcp "'+
+N'select top 100 percent '
+N' case c.colid when 1 then o.name else N'''' end as 表名,'
+N' c.colid as 序号,'
+N' c.name as 字段名,'
+N' t.name 数据类型,'
+N' c.prec as 长度,'
+N' p.value as 字段说明,'
+N' m.text as 默认值'
+N' from '+quotename(@dbname)+N'.dbo.sysobjects o'
+N' inner join '+quotename(@dbname)+N'.dbo.syscolumns c on o.id=c.id'
+N' inner join '+quotename(@dbname)+N'.dbo.systypes t on c.xusertype=t.xusertype'
+N' left join '+quotename(@dbname)+N'.dbo.sysproperties p on c.id=p.id and c.colid = p.smallid'
+N' left join '+quotename(@dbname)+N'.dbo.syscomments m on c.cdefault=m.id'
+N' where o.xtype in(N''U'') and o.status>=0'
+N' order by c.id,c.colid'
+N'" queryout "'+@fname
+N'" /P"" /w'
exec master..xp_cmdshell @s,no_output
go


--查询
sqlserver中直接通过代码访问excel


SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions

甚至可以:
DELETE
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\test.xls";User ID=Admin;Password=;Extended properties=Excel 5.0')...xactions


你还可以:

EXEC sp_addlinkedserver 'ExcelSource',
'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0',
'c:\Temp\Part.xls',
NULL,
'Excel 5.0'
GO

EXEC sp_addlinkedsrvlogin 'excelsource', 'false', NULL, NULL, NULL

在excel中选定范围,选择菜单'插入'->'名称'->'定义'

示例

SELECT *
FROM EXCEL...SalesData (整页使用 Sheet1$)

如果碰到转换为NULL值请使用文本文件来做DTS转换


回复
yilee 2007-11-29
怎么没人回答啊?都去哪了哦?
高手们..快来帮我看看啊...
回复
yilee 2007-11-29
我换了一下思路,我把excel读到了
DataTable dt = new DataTable();
dt里面,有没有把dt导入到指定的sql表中的方法呢?
不能循环,因为数据量太大了...


高手们指点一下啊...dt导入sql又怎么导啊?
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告