select * into test_tb from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\0.xls";Extended properties=Excel 8.0')...["sheet1$]select count(1) as 总字段数 from syscolumns where object('test_tb')=iddrop table test_tb
把这个EXCEL的内容插入临时表 再计算列的行数
select * into test_tb from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=c:\0.xls;Extended properties=Excel 8.0')...[sheet1$] where 1=0select count(1) as 总字段数 from syscolumns where object('test_tb')=iddrop table test_tb
select count(n) as '总字段数' from tablename
哦,我这个EXCEL中有几千个SHEET, 每个都去建下临时表?我的C#原代码是这样的:urlstr = rowview["url"].ToString(); urlstr = urlstr.Replace("'", ""); urlstr = "SHEET1"; comm2 = "insert into fubiao1 select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="+ExcelFileName.Text.ToString().Trim()+";Extended properties=Excel 8.0')...["+urlstr+"$]"; comm = new SqlCommand(comm2, myConnection); comm.ExecuteNonQuery();其中,URLSTR是传递过来的SHEET名,我想把这几千个SHEET存到一个SQLSERVER表中,可是,这几千个SHEET字段数不等,没有办法循环插入.会提示"插入错误: 列名或所提供值的数目与表定义不匹配。"所以,我就想是否可以先算出SHEET的列数,然后修改COMM2的句子为 comm2 = "insert into fubiao1 select *,少的数据列数 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="+ExcelFileName.Text.ToString().Trim()+";Extended properties=Excel 8.0')...["+urlstr+"$]"; 这样就可以完成填充.
into test_tb
from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="c:\0.xls";Extended properties=Excel 8.0')...["sheet1$]select count(1) as 总字段数
from syscolumns
where object('test_tb')=iddrop table test_tb
into test_tb
from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source=c:\0.xls;Extended properties=Excel 8.0')...[sheet1$]
where 1=0select count(1) as 总字段数
from syscolumns
where object('test_tb')=iddrop table test_tb
每个都去建下临时表?我的C#原代码是这样的:urlstr = rowview["url"].ToString();
urlstr = urlstr.Replace("'", "");
urlstr = "SHEET1"; comm2 = "insert into fubiao1 select * from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="+ExcelFileName.Text.ToString().Trim()+";Extended properties=Excel 8.0')...["+urlstr+"$]"; comm = new SqlCommand(comm2, myConnection);
comm.ExecuteNonQuery();其中,URLSTR是传递过来的SHEET名,我想把这几千个SHEET存到一个SQLSERVER表中,可是,这几千个SHEET字段数不等,没有办法循环插入.会提示"插入错误: 列名或所提供值的数目与表定义不匹配。"所以,我就想是否可以先算出SHEET的列数,然后修改COMM2的句子为
comm2 = "insert into fubiao1 select *,少的数据列数 from OpenDataSource('Microsoft.Jet.OLEDB.4.0','Data Source="+ExcelFileName.Text.ToString().Trim()+";Extended properties=Excel 8.0')...["+urlstr+"$]";
这样就可以完成填充.
如果这样,建议在EXCEL的最后一列增加一列(列数),把该列填充为:=column(),这样就不劳SQL了.
where name like 'exd%' 一个表中的字段名前缀应该是相同的吧。。比如说有一个表叫exd_det 那这张表的字段的前缀都应该相同的,比如EXD_rate,exd_eff_date
http://topic.csdn.net/u/20100401/12/21c37b19-ee53-4133-8b9f-946367053dc1.html