excel导入sql提示外部表不是预期的格式解决思路
日期：2014-05-18 浏览次数：20878 次
excel导入sql提示外部表不是预期的格式
我从sql中导出EXCEL表，成功。但是导回去时，提示“外部表不是预期的格式”。当然，把EXCEL表粘贴到新建表中再导入sql是可以的。但我不想手处理。请问如何使导出的EXCEL表符合标准格式呢？顺便说一下，我用的语言是ASP，不过这无所谓的，只要说个思路就可以了。
------解决方案--------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>Excel导入MSSQL </title>
</head>
<body>
<%
Set Conn=Server.CreateObject("ADODB.Connection")
ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.mappath("test.mdb")&";User ID=admin;Password=;"
Conn.Open(ConnStr)
set rs=server.createobject("adodb.recordset")
sql="select * from test"
rs.Open sql,conn,3,3
for Createtablei=0 to rs.Fields.Count-1
Createtable=Createtable&rs.fields(Createtablei).name&" text ,"
next
Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")"
ExcelFile="Excel.xls"
set fso=Server.CreateObject ("Scripting.FileSystemObject")
fpath=Server.MapPath(ExcelFile)
if fso.FileExists(fpath) then
whichfile=Server.MapPath(ExcelFile)
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.GetFile(whichfile)
thisfile.delete true
dim excelfile,tbname
end if
Dim Driver,DBPath
Set conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};Readonly=0;"
DBPath = "DBQ=" & Server.MapPath(excelfile)
conn.Open Driver & DBPath
conn.Execute(Createtablesql)
for ii=0 to rs.recordcount-1
for i=0 to rs.Fields.Count-1
Inserttablename=Inserttablename&rs.fields(i).name&","
Inserttable=Inserttable&"'"&Rs(i)&"',"
Next
Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"
conn.Execute(Insertintosql)
Insertintosql =""
Inserttable=""
Inserttablename=""
rs.MoveNext
Next
Response.Redirect (ExcelFile)
%>
</body>
</html>
我从sql中导出EXCEL表，成功。但是导回去时，提示“外部表不是预期的格式”。当然，把EXCEL表粘贴到新建表中再导入sql是可以的。但我不想手处理。请问如何使导出的EXCEL表符合标准格式呢？顺便说一下，我用的语言是ASP，不过这无所谓的，只要说个思路就可以了。
------解决方案--------------------
<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312" />
<title>Excel导入MSSQL </title>
</head>
<body>
<%
Set Conn=Server.CreateObject("ADODB.Connection")
ConnStr="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.mappath("test.mdb")&";User ID=admin;Password=;"
Conn.Open(ConnStr)
set rs=server.createobject("adodb.recordset")
sql="select * from test"
rs.Open sql,conn,3,3
for Createtablei=0 to rs.Fields.Count-1
Createtable=Createtable&rs.fields(Createtablei).name&" text ,"
next
Createtablesql="Create table Sheet1("&left(Createtable,len(Createtable)-1)&")"
ExcelFile="Excel.xls"
set fso=Server.CreateObject ("Scripting.FileSystemObject")
fpath=Server.MapPath(ExcelFile)
if fso.FileExists(fpath) then
whichfile=Server.MapPath(ExcelFile)
Set fs = CreateObject("Scripting.FileSystemObject")
Set thisfile = fs.GetFile(whichfile)
thisfile.delete true
dim excelfile,tbname
end if
Dim Driver,DBPath
Set conn = Server.CreateObject("ADODB.Connection")
Driver = "Driver={Microsoft Excel Driver (*.xls)};Readonly=0;"
DBPath = "DBQ=" & Server.MapPath(excelfile)
conn.Open Driver & DBPath
conn.Execute(Createtablesql)
for ii=0 to rs.recordcount-1
for i=0 to rs.Fields.Count-1
Inserttablename=Inserttablename&rs.fields(i).name&","
Inserttable=Inserttable&"'"&Rs(i)&"',"
Next
Insertintosql="Insert into Sheet1("&left(Inserttablename,len(Inserttablename)-1)&")values("&left(Inserttable,len(Inserttable)-1)&")"
conn.Execute(Insertintosql)
Insertintosql =""
Inserttable=""
Inserttablename=""
rs.MoveNext
Next
Response.Redirect (ExcelFile)
%>
</body>
</html>
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
