from excel to sqlserver2005(转)

最新推荐文章于 2024-10-11 17:04:33 发布

清，纯一色

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量83

点赞数

文章标签：数据库

原文链接：http://www.cnblogs.com/yening10000/archive/2009/07/06/1517755.html

版权

一、 1. 在程序中，用ADO.NET。代码如下：

//连接串

string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;Data Source=" + [EXCEL文件，含路径] + ";";

OleDbConnection conn = new OleDbConnection(strConn);

conn.Open();

DataTable dtSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,new object[] {null, null, null, "TABLE"});

DataSet ds = new DataSet();

//一个EXCEL文件可能有多个工作表，遍历之

foreach( DataRow dr in dtSchema.Rows )

{

string table = dr["TABLE_NAME"].ToString();

string strExcel = "SELECT * FROM [" + table + "]";

ds.Tables.Add(table);

OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel,conn);

myCommand.Fill(ds,table);

}

conn.Close();

这样，读取出来的数据就藏在DataSet里了。

采用这种方式，数据库所在机器不必装有EXCEL。

二、在查询分析器里，直接写SQL语句：

如果是导入数据到现有表，则采用

INSERT INTO 表 SELECT * FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:"test.xls',sheet1$)

的形式

如果是导入数据并新增表，则采用

SELECT * INTO 表 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:"test.xls',sheet1$)

的形式。

以上语句是将EXCEL文件里SHEET1工作表中所有的列都读进来，如果只想导部分列，可以

INSERT INTO 表(a1,a2,a3) SELECT a1,a2,a3 FROM OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:"test.xls',sheet1$)

其实可以将OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:"test.xls',sheet1$)当成一个表，例如我就写过这样一个句子：

INSERT INTO eval_channel_employee(channel,employee_id)

SELECT CASE a.渠道 WHEN 'DIY' THEN 1 WHEN 'RDC' THEN 0 WHEN 'KCM' THEN 2 ELSE 3 END

,b.id FROM

OPENROWSET('MICROSOFT.JET.OLEDB.4.0'

,'Excel 5.0;HDR=YES;DATABASE=c:"temp"name.xls',sheet1$) AS a,pers_employee b

WHERE a.员工编码=b.code

不管是哪种方式，哪种途径，系统都会默认将第一行上的内容作为字段名。

这二种方式虽然非常好，但操作过程不太方便，经过测试发现在SQL2005中直接可以实现导入功能。操作过程如下：

第一步：登录到SQL Server Management Studio，

第二步：在“对象资源管理器”中右键单击“管理”，在弹出列表中单击“导入数据”

第三步：在“导入向导”对话框中单击“下一步”，进入到“选择数据源”对话框，在“数据源”列表中选择“Microsoft Excel ”，同时选择相应的Excel 文档，完成后单击“下一步”（一定要勾选该对话框中的“首行包含列名称”，因此它是将Excel文档中的列标题为数据库表中的列项标题）

第四步：指定目标数据库服务，依次单击“下一步”。。。。至到“完成”

第五步：重新打到SQL Server Management Studio，进入到导入的数据库表，可以发现所导入的Excel文档数据。

假设你的Excel文件的存放路径为"D:"2.xls",表名为1,那么可以这么写代码;
OleDbConnection cn = new OleDbConnection("provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:""2.xls;Extend Properties=Excel 8.0");
OleDbDataAdapter da = new OleDbDataAdapter("select * from [1$]"cn);
DataSet ds= new DataSet();
cn.Open();
da.Fill(ds,"newtabel");
da.Dispose();
cn.Close();
定义sql连接sqlcn,命令sqlcmd,代码我就不写了
sqlcn.Open()
for(int i=0;i<ds.Tabels["newtabel"].Rows.Count;i++)
{
cmd=new SqlCommand("insert into monthdata (目标字段,自己写)values('"+ds.Tabels["newtabel"].Rows[i][0]+"'.......)",sqlcn);//这里注意对应关系,以及数据类型就可以了
sqlcmd.ExecuteNoQuery();
}
sqlcmd.Dispose();
sqlcn.Close();

以下仅供参考
   string strConn;
   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:""2.xls;Extended Properties=Excel 8.0";
   OleDbConnection conn = new OleDbConnection(strConn);
   OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT * FROM [1$]", strConn);
   DataSet ds = new DataSet();
   myCommand.Fill(ds,"newtabel");
   DataGrid1.DataBind();
   SqlConnection sqlcn=DB.createCon();
            sqlcn.Open();
             for(int i=0;i<ds.Tables ["newtabel"].Rows.Count;i++)
            {
          SqlCommand MyAdd=new SqlCommand("insert into monthdata (DateTypeID,DataTitle,DataName,DanWei,MonthTol,LastMonthTol,Amount,BFB,DataMonth)values('"+ds.Tables ["newtabel"].Rows [i][0]+"','"+ds.Tables["newtabel"].Rows[i][1]+"','"+ds.Tables["newtabel"].Rows[i][2]+"','"+ds.Tables["newtabel"].Rows[i][3]+"','"+ds.Tables["newtabel"].Rows[i][4]+"','"+ds.Tables["newtabel"].Rows[i][5]+"','"+ds.Tables["newtabel"].Rows[i][6]+"','"+ds.Tables["newtabel"].Rows[i][7]+"','"+ds.Tables["newtabel"].Rows[i][8]+"')",sqlcn);//这里注意对应关系,以及数据类型就可以了
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][0].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][1].ToString (),SqlDbType.VarChar,50));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][2].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][3].ToString (),SqlDbType.VarChar,20));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][4].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][5].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][6].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][7].ToString (),SqlDbType.Decimal ,9));
     MyAdd.Parameters.Add(new SqlParameter (ds.Tables ["newtabel"].Rows[i][8].ToString (),SqlDbType.VarChar,20));
     MyAdd.ExecuteNonQuery ();
            }

(SQL Server 导入和导出向导)

- 正在复制到 [QValleyTravel_db].[dbo].[scene_info_table] (已停止)

- 执行之后 (成功)
消息
* 信息 0x402090df: 数据流任务: 数据插入操作的最终提交已开始。
(SQL Server 导入和导出向导)

* 信息 0x402090e0: 数据流任务: 数据插入操作的最终提交已结束。
(SQL Server 导入和导出向导)

- 清除 (成功)
消息
* 信息 0x4004300b: 数据流任务: “组件“目标 - scene_info_table”(62)”已写入 0 行。
(SQL Server 导入和导出向导)

网友回复:你插入的数据长度与你插入到表里对应列的长度不匹配小于对应列的长度
网友回复:把表里对应列的长度改大点就可以了
网友回复:以前我也碰到过这样的现象，但有些相同的现象，其原因却不同，可能是微软定的很多很细的东西，开发的人偷懒没把东西做细吧，呵，言归正传。

你可以试着更改一下SQL的服务

打开sql server configuration Manager(SQL SERVER 配置治理器)

把SSIS（SQL SERVER Integration Service）的运行身份改成"LocalSystem"(本地)
然后重启一下服务就可以导数据了，非常郁闷。

有些系统是以"Network Service"运行的，也能正常导入数据

只供参考，不行不收费，呵
网友回复:在导入前转换一下列的长度 cast(col as nvarchar(20))--在SSIS里可设置方法与2000DTS没有什么区别
网友回复:不设置的情况下，Excel的长度全为256
或在查询分析器的插入:
insert T(col...)
select cast(COL as nvarchar(20))......--
FROM openrowset( 'Microsoft.Jet.OLEDB.4.0',
'EXCEL 8.0;HDR=YES;IMEX=1; DATABASE=e:"test.xls',Sheet1$)