如何按 列數 把excel數據導入 sql server 2000 數據庫表!
各位大俠：
怎麼樣才能實現把 Excel 裡面的數據 按列導入
已經存在的數據庫表；
就是不按excel 列名，按列數導入!
這樣行不行啊!？？
做項目在這裡卡住了，急啊!
謝謝!
[解决办法]
--1、 数据导入临时表
SELECT * into 临时表
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0 ',
'Data Source= "c:\111.xls ";User ID=Admin;Password=;Extended properties=Excel 5.0 ')...Sheet1$
--2 把临时表里的数据导入正式表里
SET NOCOUNT ON
declare @name varchar(100)
declare cur CURSOR for select name from syscolumns where object_id( '临时表 ')=id
open cur
fetch next from cur into @name
while @@fetch_status = 0
begin
exec( 'insert into 正式表(字段) select '+@name+ ' from 临时表 ')
fetch next from cur into @name
end
close cur
deallocate cur
SET NOCOUNT Off
-- 查看结果
select 字段 from 正式表
--删除临时表
drop table 临时表
[解决办法]
列名，列數 有什么区别吗？不懂。
下面是asp把excel导入到sqlserver的例子，希望对你有帮助。
--------------------------------------
excel_upload_update.asp：
<html>
<title> my test </title>
<body>
<%if trim(request( "type "))= "upload " then
Set FileUp = Server.CreateObject( "Yousoft.UploadFile ")
vSheet=trim(FileUp.Form( "sheetName "))
'===============================================================================================================================
userFile=FileUp.userFile(0)
FileExt = FileUp.FileExt(userFile)
if FileExt= ".xls " then
FileUp.SaveFile userFile, "D:\pos\pr\dev\1.xls "
'===============================================================================================================================
set cn=server.CreateObject( "adodb.connection ")
cn.Open "Provider=SQLOLEDB;Data Source=192.168.8.48;Initial Catalog=pos;User ID=pos1;Password=pos1 "
strSql= "select b.lost_date,b.season_year,a.lostrate,a.out_season_status from "& _
"openrowset( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel 8.0;HDR=yes;IMEX=1;database=\\192.168.8.48\pos$\pr\dev\1.xls; ', 'select * from [ " & vSheet & "$] ')a, "& _
"cost_lostrate_log1 b where a.lost_date=b.lost_date and a.season_year=b.season_year "
on error resume next
set myrecordset= cn.Execute (strSql)
if err.number <> 0 then
Response.Write "Error: Please check <BR> if the excel file has " & vSheet & " sheet <BR> or the sheet has lost_date|season_year|lostrate|out_season_status column! "
%> <a href= "excel_upload_update.asp " > back </a> </font> <%
else
do while not myrecordset.EOF
sql= "update cost_lostrate_log1 set lostrate= " & myrecordset(2)& ",out_season_status= " & myrecordset(3) & _
" where lost_date = " & myrecordset(0)& " and season_year = " & myrecordset(1)
cn.Execute sql
myrecordset.MoveNext
loop
strSql= "insert into cost_lostrate_log1 select * from openrowset( 'Microsoft.Jet.OLEDB.4.0 ', 'Excel 8.0;HDR=yes;IMEX=1;database=\\192.168.7.48\pos$\pr\dev\1.xls; ', 'select * from [ " & vSheet & "$] ')a where a.season_year not in (select season_year from cost_lostrate_log1 b where a.lost_date=b.lost_date) "
set rs= cn.Execute (strSql)
if err.number <> 0 then
Response.Write "Error: Please check <BR> if the excel file has " & vSheet & " sheet <BR> or the sheet has lost_date|season_year|lostrate|out_season_status column! "
%> <a href= "excel_upload_update.asp " > back </a> </font> <%
else
Response.Write "Upload success! "
%> <a href= "excel_upload_update.asp " > upload another </a> </font> <%
end if
end if
else
Response.Write "Error:The upload must be excel file! "
%> <a href= "excel_upload_update.asp " > back </a> </font> <%
end if
'=================================================================================================================================
else
%>
<form id=form1 enctype= "multipart/form-data " method= 'post ' name=form1 action= "excel_upload_update.asp?type=upload ">
<table>
<tr>
<td> File Name: </td>
<td id= "file1new "> <input TYPE= "file " NAME= "newattach " style= "font-family:Arial; font-size:8pt;width:367px "> </td>
</tr>
<tr>
<td> Sheet Name: </td>
<td> <input type= 'text 'id= 'sheetName ' name= 'sheetName ' style= "font-family:Arial; font-size:8pt;width:300px ">
<input type= 'button 'id= 'btn ' name= 'cmdUpload ' value= 'Upload '> </td>
</tr>
</table>
</form>
<%end if %>
<script language= "vbscript ">
Sub cmdUpload_onclick()
vFile=form1.newattach.value
vSheet=form1.sheetName.value
if vFile= " " or vSheet= " " then
alert( "Please input excel file path and sheet name ")
else
form1.submit
end if
end sub
</script>
</body>
</html>
