SELECT * into Library
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\022n.xls";
User ID=Admin;Password=;
Extended properties=Excel 5.0')...table1


我搜索是这样，但是User ID和Password是指什么？我安装时是本地连接，没有设置用户和密码

那ID该怎么写？



excel到MSSQL的SQL语句在2楼，MSSQL到excel的SQL语句在6楼，ADO.NET的语句在7楼

[此贴子已经被作者于2006-3-16 12:07:51编辑过]

在SQL查询分析器运行这个成功了，但在ADO.NET不成功，怎么办？

SELECT * into live41
FROM OpenDataSource('Microsoft.Jet.OLEDB.4.0',
'Data Source="E:\022n.xls";
User ID=;Password=;
Extended properties=Excel 5.0')...[Sheet1$]

搞定了，原来是转义符的问题，

在C#下利用ADO.NET把Excel表直接导入到SQL Server 2000的代码如下：


SqlConnection sqlConnection1 = new SqlConnection("server=(local);integrated security=SSPI;initial catalog=Library");
//SqlConnection sqlConnection1 = new SqlConnection();
//sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";

string importSQL = "SELECT * into live41 FROM OpenDataSource" +
"('Microsoft.Jet.OLEDB.4.0','Data Source=" + "\"" + "E:\\022n.xls" + "\"" +
"; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";

//把E:\022n.xls的Sheet1$表 创建到Library数据库的live41表中

try
{
sqlConnection1.Open();

SqlCommand sqlCommand1 = new SqlCommand();
sqlCommand1.Connection = sqlConnection1;
sqlCommand1.CommandText = importSQL;

sqlCommand1.ExecuteNonQuery();
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}

问题又来了，为什么导入以后的字段顺序不对啊？！

例如excel里面本来是学号、姓名、成绩，到SQL Server里面的顺序变成了成绩、学号、姓名。


奇怪啊，谁指导一下？？

楼上的问题不可调和啊，系统默认导入后的数据必须按excel表的第一行的字段名称来排列顺序

无论用企业管理器自带的还是用SQL语句导入都必须按顺序创建表的字段


没办法了，看来只有写代码逐行逐行读入……

顺便把从SQL Server导出到excel的代码也给出吧

EXEC master..xp_cmdshell 'bcp Library.dbo.live41 out e:\live41.xls -c -q -S"" -U"" -P""'

把Library数据库中的live41表导出到e:\live41.xls文件，其中S后的双引号里面是SQL服务器名，U后是用户ID，P后是密码
如果安装MSSQL时设置为本地连接，且没有加用户名和密码的话，则三个都可以留空



例如 SQL服务器名是LEE，用户名是sa，密码是123456 ：

EXEC master..xp_cmdshell 'bcp Library.dbo.live41 out e:\live41.xls -c -q -S"LEE" -U"sa" -P"123456"'

[此贴子已经被作者于2006-3-16 11:42:31编辑过]

SqlConnection sqlConnection1 = new SqlConnection();
sqlConnection1.ConnectionString = "server=(local);integrated security=SSPI;initial catalog=Library";

//import excel into SQL Server 2000
string importSQL = "SELECT * into live41 FROM OpenDataSource" +
"('Microsoft.Jet.OLEDB.4.0','Data Source=" + "\"" + "E:\\022n.xls" + "\"" +
"; User ID=;Password=; Extended properties=Excel 5.0')...[Sheet1$]";

//export SQL Server 2000 into excel
string exportSQL = "EXEC master..xp_cmdshell " +
"'bcp Library.dbo.live41 out E:\\live41.xls -c -q -S" + "\"" + "\"" +
" -U" + "\"" + "\"" + " -P" + "\"" + "\"" + "\'";

try
{
sqlConnection1.Open();

SqlCommand sqlCommand1 = new SqlCommand();
SqlCommand sqlCommand2 = new SqlCommand();

sqlCommand1.Connection = sqlConnection1;
sqlCommand2.Connection = sqlConnection1;

sqlCommand1.CommandText = importSQL;
sqlCommand2.CommandText = exportSQL;

sqlCommand1.ExecuteNonQuery();
sqlCommand2.ExecuteNonQuery();

MessageBox.Show("import/export finish!");

}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}


if(sqlConnection1!=null)
{
sqlConnection1.Close();
sqlConnection1 = null;
}