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

[yilee]

看完了相关的帖子,没找到具体的解决办法,所以只有请各位高手们..指点一下了...
我现在是本机上的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)".

请各位高手帮帮忙..我被这个问题困扰了许久了,希望能给小弟一个具体的解决方法...小弟先谢谢各位了...

[yilee]

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


高手们指点一下啊...dt导入sql又怎么导啊?

[yilee]

怎么没人回答啊?都去哪了哦?
高手们..快来帮我看看啊...

[dawugui]

我觉得应该是本地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]

谢谢..我试试~~

[fa_ge]

建议楼主不要用代码，直接用dts 导入,我就是这样用的

[yilee]

但是要求就是用代码来完成,最苦恼的就是要在本地去操作服务器,用DTS也只能在服务器上操作...
有没有解决这个问题的办法啊??

[yilee]

谢谢 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]

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


期待高手们的回答.......

[fa_ge]

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$ 

[fa_ge]

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

[yilee]

就是在本机上有错``不知道怎么解决....

[yilee]

谢谢你们..我自己用其他办法解决了...