asp如何?excel 匯入sql 資料庫

【轉】asp如何?excel 匯入sql 資料庫

http://www.webuc.net/moody/articles/7430.aspx

所謂的資料轉送，其實是指SQLServer訪問Access、Excel間的資料。

為什麼要考慮到這個問題呢？

由於曆史的原因，客戶以前的資料很多都是在存入在文本資料庫中，如Acess、Excel、Foxpro。現在系統升級及資料庫伺服器如SQLServer、ORACLE後，經常需要訪問文本資料庫中的資料，所以就會產生這樣的需求。前段時間出差的項目，就是面臨這樣的一個問題：SQLServer和VFP之間的資料交換。

要完成標題的需要，在SQLServer中是一件非常簡單的事情。

通常的可以有3種方式：1、DTS工具 2、BCP 3、分散式查詢

DTS就不需要說了，因為那是圖形化操作介面，很容易上手。

這裡主要講下後面兩們，分別以查、增、刪、改作為簡單的例子：

下面廢話就不說了，直接以T-SQL的形式表現出來。

一、SQLServer和Access

1、查詢Access中資料的方法：

select * from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')

或

select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\DB2.mdb";User ID=Admin;Password=')...serv_user

2、從SQLServer向Access寫資料：

insert into OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')

select * from SQLServer表
或用BCP

master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'

上面的區別主要是：OpenRowSet需要mdb和表存在，BCP會在不存在的時候產生該mdb

3、從Access向SQLServer寫資料：有了上面的基礎，這個就很簡單了

insert into SQLServer表 select * from
OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from Accee表')

或用BCP

master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" in "c:\db3.mdb" -c -q -S"." -U"sa" -P"sa"'

4、刪除Access資料：

delete from OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')

where lock=0

5、修改Access資料：

update OpenRowSet('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from serv_user')

set lock=1

SQLServer和Access大致就這麼多。

二、SQLServer和Excel

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、修改Execl

update 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

或BCP

master..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$]')

如果表serv_user_bak不存在，則建立

有關BCP和分散式查詢的詳細解答，就查SQLServer內建的協助吧。
SQLServer和txt檔案、HTML檔案、VFP檔案的資料交換都顯得非常容易了。。。。

/*=================== 匯入/匯出 excel 的基本方法 ===================*/

從excel檔案中,匯入資料到sql資料庫中,很簡單,直接用下面的語句:

/*===================================================================*/
--如果接受資料匯入的表已經存在
insert into 表 select * from
openrowset(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;database=c:test.xls'',sheet1$)

--如果匯入資料並產生表
select * into 表 from
openrowset(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;database=c:test.xls'',sheet1$)

/*===================================================================*/
--如果從sql資料庫中,匯出資料到excel,如果excel檔案已經存在,而且已經按照要接收的資料建立好表頭,就可以簡單的用:
insert into openrowset(''microsoft.jet.oledb.4.0''
,''excel 5.0;hdr=yes;database=c:test.xls'',sheet1$)
select * from 表

--如果excel檔案不存在,也可以用bcp來導成類excel的檔案,注意大小寫:
--匯出表的情況
exec master..xp_cmdshell ''bcp 資料庫名.dbo.表名 out "c:test.xls" /c -/s"伺服器名" /u"使用者名稱" -p"密碼"''

--匯出查詢的情況
exec master..xp_cmdshell ''bcp "select au_fname, au_lname from pubs..authors order by au_lname" queryout "c:test.xls" /c -/s"伺服器名" /u"使用者名稱" -p"密碼"''

/*--說明:
c:test.xls 為匯入/匯出的excel檔案名稱.
sheet1$ 為excel檔案的工作表名,一般要加上$才能正常使用.
--*/
--上面已經說過,用bcp匯出的是類excel檔案,其實質為文字檔,

--要匯出真正的excel檔案.就用下面的方法

/*--資料匯出excel

匯出表中的資料到excel,包含欄位名,檔案為真正的excel檔案
,如果檔案不存在,將自動建立檔案
,如果表不存在,將自動建立表
基於通用性考慮,僅支援匯出標準資料類型
--鄒建 2003.10--*/

/*--調用樣本

p_exporttb @tbname=''地區資料'',@path=''c:'',@fname=''aa.xls''
--*/
if exists (select * from dbo.sysobjects where id = object_id(n''[dbo].[p_exporttb]'') and objectproperty(id, n''isprocedure'') = 1)

drop procedure [dbo].[p_exporttb]
go

create proc p_exporttb
@tbname sysname, --要匯出的表名
@path nvarchar(1000), --檔案存放目錄
@fname nvarchar(250)='''' --檔案名稱,預設為表名
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--參數檢測
if isnull(@fname,'''')='''' set @fname=@tbname+''.xls''

--檢查檔案是否已經存在
if right(@path,1)<>'''' set @path=@path+''''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--資料庫建立語句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr=''driver={microsoft excel driver (*.xls)};dsn='''''''';readonly=false''

+'';create_db=" +'';database=''+@sql+''"''

--串連資料庫
exec @err=sp_oacreate ''adodb.connection'',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,''open'',null,@constr
if @err<>0 goto lberr

/*--如果覆蓋已經存在的表,就加上下面的語句
--建立之前先刪除表/如果存在的話
select @sql=''drop table [''+@tbname+'']''
exec @err=sp_oamethod @obj,''execute'',@out out,@sql
--*/

--建立表的sql
select @sql='''',@fdlist=''''
select @fdlist=@fdlist+'',[''+a.name+'']''
,@sql=@sql+'',[''+a.name+''] ''
+case when b.name in(''char'',''nchar'',''varchar'',''nvarchar'') then
''text(''+cast(case when a.length>255 then 255 else a.length end as varchar)+'')''

when b.name in(''tynyint'',''int'',''bigint'',''tinyint'') then ''int''
when b.name in(''smalldatetime'',''datetime'') then ''datetime''
when b.name in(''money'',''smallmoney'') then ''money''
else b.name end
from syscolumns a left join systypes b on a.xtype=b.xusertype
where b.name not in(''image'',''text'',''uniqueidentifier'',''sql_variant'',''ntext'',''varbinary'',''binary'',''timestamp'')

and object_id(@tbname)=id
select @sql=''create table [''+@tbname
+''](''+substring(@sql,2,8000)+'')''
,@fdlist=substring(@fdlist,2,8000)
exec @err=sp_oamethod @obj,''execute'',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--匯入資料
set @sql=''openrowset(''''microsoft.jet.oledb.4.0'''',''''excel 5.0;hdr=yes
;database=''+@path+@fname+'''''',[''+@tbname+''$])''

exec(''insert into ''+@sql+''(''+@fdlist+'') select ''+@fdlist+'' from ''+@tbname)

return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 錯誤號碼
,@src as 錯誤源,@desc as 錯誤描述
select @sql,@constr,@fdlist
go
--上面是導表的,下面是導查詢語句的.

/*--資料匯出excel

匯出查詢中的資料到excel,包含欄位名,檔案為真正的excel檔案
,如果檔案不存在,將自動建立檔案
,如果表不存在,將自動建立表
基於通用性考慮,僅支援匯出標準資料類型
--鄒建 2004.10--*/

/*--調用樣本

p_exporttb @sqlstr=''select * from 地區資料''
,@path=''c:'',@fname=''aa.xls'',@sheetname=''地區資料''
--*/
if exists (select * from dbo.sysobjects where id = object_id(n''[dbo].[p_exporttb]'') and objectproperty(id, n''isprocedure'') = 1)

drop procedure [dbo].[p_exporttb]
go

create proc p_exporttb
@sqlstr varchar(8000), --查詢語句,如果查詢語句中使用了order by ,請加上top 100 percent
@path nvarchar(1000), --檔案存放目錄
@fname nvarchar(250), --檔案名稱
@sheetname varchar(250)='''' --要建立的工作表名,預設為檔案名稱
as
declare @err int,@src nvarchar(255),@desc nvarchar(255),@out int
declare @obj int,@constr nvarchar(1000),@sql varchar(8000),@fdlist varchar(8000)

--參數檢測
if isnull(@fname,'''')='''' set @fname=''temp.xls''
if isnull(@sheetname,'''')='''' set @sheetname=replace(@fname,''.'',''#'')

--檢查檔案是否已經存在
if right(@path,1)<>'''' set @path=@path+''''
create table #tb(a bit,b bit,c bit)
set @sql=@path+@fname
insert into #tb exec master..xp_fileexist @sql

--資料庫建立語句
set @sql=@path+@fname
if exists(select 1 from #tb where a=1)
set @constr=''driver={microsoft excel driver (*.xls)};dsn='''''''';readonly=false''

+'';create_db=" +'';database=''+@sql+''"''

--串連資料庫
exec @err=sp_oacreate ''adodb.connection'',@obj out
if @err<>0 goto lberr

exec @err=sp_oamethod @obj,''open'',null,@constr
if @err<>0 goto lberr

--建立表的sql
declare @tbname sysname
set @tbname=''##tmp_''+convert(varchar(38),newid())
set @sql=''select * into [''+@tbname+''] from(''+@sqlstr+'') a''
exec(@sql)

select @sql='''',@fdlist=''''
select @fdlist=@fdlist+'',[''+a.name+'']''
,@sql=@sql+'',[''+a.name+''] ''
+case when b.name in(''char'',''nchar'',''varchar'',''nvarchar'') then
''text(''+cast(case when a.length>255 then 255 else a.length end as varchar)+'')''

when b.name in(''tynyint'',''int'',''bigint'',''tinyint'') then ''int''
when b.name in(''smalldatetime'',''datetime'') then ''datetime''
when b.name in(''money'',''smallmoney'') then ''money''
else b.name end
from tempdb..syscolumns a left join tempdb..systypes b on a.xtype=b.xusertype
where b.name not in(''image'',''text'',''uniqueidentifier'',''sql_variant'',''ntext'',''varbinary'',''binary'',''timestamp'')

and a.id=(select id from tempdb..sysobjects where name=@tbname)
select @sql=''create table [''+@sheetname
+''](''+substring(@sql,2,8000)+'')''
,@fdlist=substring(@fdlist,2,8000)

exec @err=sp_oamethod @obj,''execute'',@out out,@sql
if @err<>0 goto lberr

exec @err=sp_oadestroy @obj

--匯入資料
set @sql=''openrowset(''''microsoft.jet.oledb.4.0'''',''''excel 5.0;hdr=yes
;database=''+@path+@fname+'''''',[''+@sheetname+''$])''

exec(''insert into ''+@sql+''(''+@fdlist+'') select ''+@fdlist+'' from [''+@tbname+'']'')

set @sql=''drop table [''+@tbname+'']''
exec(@sql)
return

lberr:
exec sp_oageterrorinfo 0,@src out,@desc out
lbexit:
select cast(@err as varbinary(4)) as 錯誤號碼
,@src as 錯誤源,@desc as 錯誤描述
select @sql,@constr,@fdlist
go