--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 ONdeclare @name varchar(100)declare cur CURSOR for select name from syscolumns where object_id('临时表')=idopen curfetch next from cur into @namewhile @@fetch_status = 0 begin exec('insert into 正式表(字段) select '+@name+' from 临时表') fetch next from cur into @name endclose cur deallocate cur SET NOCOUNT Off-- 查看结果 select 字段 from 正式表--删除临时表 drop table 临时表
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
</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 ifend sub </script> </body> </html>
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 ONdeclare @name varchar(100)declare cur CURSOR for select name from syscolumns where object_id('临时表')=idopen curfetch next from cur into @namewhile @@fetch_status = 0
begin exec('insert into 正式表(字段) select '+@name+' from 临时表') fetch next from cur into @name
endclose 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 ifend sub
</script>
</body>
</html>
你好!
我也大概知道先把excel導入臨時表,
但是導入臨時表時
臨時表的列 排列和 Excel不同啊
各位有什麼辦法嗎??
上面表名應該為列名
按烈數就可以 不理會Excel “列名”是否錯誤 或者字體不相同;