【原创】Excel导入\导出 SqlServer

原文链接:http://www.cnblogs.com/compass/articles/1294519.html

EXCEL导入SQL并生成表

 

SQL导入脚本
select * into 要创建的表名 from 
openrowset('microsoft.jet.oledb.4.0','excel 5.0;hdr=yes;database=C:Book1.xls',sheet1$)

注释:hdr=yes;是插入到SQL时候把EXCEL的第一行数据当作列名。database后是要导入的EXCEL的绝对路径。sheet1是Excel的表名,后面需加$,不加的话有时会报错。

====================================================================


EXCEL导入SQL中已存在的表格

按照EXCEL的数据格式创建好TABLE 

 

SQL导入脚本2
insert into 已存在的表名 
select * from 
openrowset('microsoft.jet.oledb.4.0','excel 5.0;hdr=no;database=c:filename.xls',sheet1$)

=====================================================================================

 

从SQLSERVER导出查询数据到EXCEL

导出并创建EXCEL

 

SQLSERVER导出到EXCEL
EXEC master..xp_cmdshell 'bcp "select * from 数据库名..表名" queryout "C:test.xls" -c -U"sa" -P"123456"'

注:注意大小写 -U为数据库登陆名,-P为密码

 

 

 

转载于:https://www.cnblogs.com/compass/articles/1294519.html

展开阅读全文
博主设置当前文章不允许评论。

SQLServer 导入/导出 Excel 的方法

06-27

最近在晚上看了一篇文章(从SQLServer 导入/导出 Excel 的方法 ): 但是把源码拷贝过来后 出现很多错误 改了好久也没亚成功! 不知道那位高人 能帮忙看看 并帮忙测试一下后 把源码贴出来供小弟看看 也好给大家分享一下!rnrn下面是 网络源码:rnrnrncreate proc p_exporttbrn@tbname sysname, --要导出的表名rn@path nvarchar(1000), --文件存放目录rn@fname nvarchar(250)=’’ --文件名,默认为表名rnasrndeclare @err int,@src nvarchar(255),@desc nvarchar(255),@out intrndeclare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)rnrn--参数检测rnif isnull(@fname,’’)=’’ set @fname=@tbname+’.xls’rnrn--检查文件是否已经存在rnif right(@path,1)〈〉’’ set @path=@path+’’rncreate table #tb(a bit,b bit,c bit)rnset @sql=@path+@fnamerninsert into #tb exec master..xp_fileexist @sqlrnrn--数据库创建语句rnset @sql=@path+@fnamernif exists(select 1 from #tb where a=1)rnset @constr=’DRIVER=Microsoft Excel Driver (*.xls);DSN=’’’’;READONLY=FALSE’rn +’;CREATE_DB=“ +’;DATABASE=’+@sql+’“’rnrnrn--连接数据库rnexec @err=sp_oacreate ’adodb.connection’,@obj outrnif @err〈〉0 goto lberrrnrnexec @err=sp_oamethod @obj,’open’,null,@constrrnif @err〈〉0 goto lberrrnrn/*--如果覆盖已经存在的表,就加上下面的语句rn--创建之前先删除表/如果存在的话rnselect @sql=’drop table [’+@tbname+’]’rnexec @err=sp_oamethod @obj,’execute’,@out out,@sqlrn--*/rnrn--创建表的SQLrnselect @sql=’’,@fdlist=’’rnselect @fdlist=@fdlist+’,[’+a.name+’]’rn,@sql=@sql+’,[’+a.name+’] ’rn +case when b.name in(’char’,’nchar’,’varchar’,’nvarchar’) thenrn ’text(’+cast(case when a.length〉255 then 255 else a.length end as varchar)+’)’rn when b.name in(’tynyint’,’int’,’bigint’,’tinyint’) then ’int’rn when b.name in(’smalldatetime’,’datetime’) then ’datetime’rn when b.name in(’money’,’smallmoney’) then ’money’rn else b.name endrnFROM syscolumns a left join systypes b on a.xtype=b.xusertypernwhere b.name not in(’image’,’text’,’uniqueidentifier’,’sql_variant’,’ntext’,’varbinary’,’binary’,’timestamp’)rnand object_id(@tbname)=idrnselect @sql=’create table [’+@tbnamern+’](’+substring(@sql,2,8000)+’)’rn,@fdlist=substring(@fdlist,2,8000)rnexec @err=sp_oamethod @obj,’execute’,@out out,@sqlrnif @err〈〉0 goto lberrrnrnexec @err=sp_oadestroy @objrnrn--导入数据rnset @sql=’openrowset(’’MICROSOFT.JET.OLEDB.4.0’’,’’Excel 5.0;HDR=YESrn ;DATABASE=’+@path+@fname+’’’,[’+@tbname+’$])’rnrnexec(’insert into ’+@sql+’(’+@fdlist+’) select ’+@fdlist+’ from ’+@tbname)rnrnreturnrnrnlberr:rnexec sp_oageterrorinfo 0,@src out,@desc outrnlbexit:rnselect cast(@err as varbinary(4)) as 错误号rn ,@src as 错误源,@desc as 错误描述rnselect @sql,@constr,@fdlistrngorn 论坛

EXCEL 导入 sqlserver问题。。。

04-27

rn 导入学生表rn rn rnrnrnrnrnexcelTosql.asprnrn<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>rnrn<%rn'sql 连接驱动rndim conn,connstr,time1,time2,mdbrntime1=timerrn'如果是sql 2000数据库,请用下面连接驱动rn'connstr="PROVIDER=MSDASQL;DRIVER=SQL Server;SERVER=(local);DATABASE=education;UID=sa;PWD=" rn'如果是sql 2005数据库,请用下面连接驱动rnconnstr="Provider=SQLNCLI;Server=LVGONGYI\SQLEXPRESS;Database=education;Uid=sa;Pwd=lgy682682111" rnrnrnIf Err Thenrnerr.ClearrnSet Conn = NothingrnResponse.Write "数据库连接出错,请检查连接字串。"rnResponse.EndrnEnd Ifrnsub closedb()rnconn.closernset conn=nothingrnend sub rn rn'dim aa rn'aa=request.form("bbb") '获取传递过来的值rn Dim StrConnect,cc rn Dim objConn rn Dim rs rn Dim Sql rn cc=0 rn 'Excel连接驱动 rn dim aaa rn aaa="provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & request.form("bbb") & ";User ID=Admin;Password=;Extended properties=Excel 8.0"rnrn set StrConnect=CreateObject("ADODB.Connection")rn StrConnect.Open aaarn Set objConn=CreateObject("ADODB.Connection") rn objConn.Open StrConnect rn '注意 表名一定要以下边这种格试 "[表名$]" 书写 rn Set rs = Server.CreateObject("ADODB.Recordset") rn Sql="select * from [sheet1$] " rn rs.Open Sql,StrConnect,2,2 rnIF rs.Eof And Rs.Bof Then %> rn rn<%else rnDo While Not rs.EOF rn'列出表内信息 rnresponse.write"excel数据为: " rnresponse.write ""&rs(0)&"-"&rs(1)&"-"&rs(2)&"-"&rs(3)&"-"&rs(4)&"-"&rs(5)&"-"&rs(6)&"" rnresponse.write" " rnset rssql=server.CreateObject("adodb.recordset") rn'插入SQL2000里 rnrssql.Open "select * from student",conn,1,3 rnrssql.AddNew rnif rs(0)<>"" then rnrssql(1)=rs(0) rnend if rnif rs(1)<>"" then rnrssql(2)=rs(1) rnend if rnif rs(2)<>"" then rnrssql(3)= "'"rs(2)"'"rnend if rnif rs(3)<>"" then rnrssql(4)=rs(3) rnend if rnrnif rs(4)<>"" then rnrssql(5)=rs(4) rnend if rnif rs(5)<>"" then rnrssql(6)=rs(5) rnend if rnif rs(6)<>"" then rnrssql(7)=rs(6) rnend if rn rnrssql.Update rnRs.MoveNext rnLoop rnend if%> rn rnrnrn错误类型:rnMicrosoft VBScript 编译器错误 (0x800A0401)rn语句未结束rn/lx/admin/excelTosql.asp, line 62, column 13rnrssql(3)= "'"rs(2)"'"rn------------^rnrnrn哪位大大帮忙看看啊。。。谢谢。。因为我暂时没分,等有分了我补。。rn 论坛

没有更多推荐了,返回首页