怎么按 列數 把excel數據導入 sql server 2000 數據庫表
日期：2014-05-19 浏览次数：20667 次
如何按 列數 把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 = " &
各位大俠：
怎麼樣才能實現把 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 = " &
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
