将Excel数据快速大批量导入数据库的代码
来源:互联网 发布:mac插件在哪里看 编辑:程序博客网 时间:2024/06/05 04:21
介绍两种途径将数据从EXCEL中导入到SQL SERVER。
一、 在程序中,用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
不管是哪种方式,哪种途径,系统都会默认将第一行上的内容作为字段名。
在做项目时,经常遇到要将Excel中的大量数据导入到Access数据库中,原来的做法是读一条写一条,若导入上万条的数据需要几分仲时间,速度很慢。有没有最快的方法呢?经本人研究、反复的实验,终于写出了最快速的批量导入大批量数据的方法,上万条数据只需几秒钟就可全部导入,够快了吧。代码公布出来与大家分享。
- Set conn = Server.CreateObject("adodb.Connection")
- connstr = "Provider=Microsoft.Jet.OLEDB.4.0; Data source=" & Server.MapPath("test.mdb")
- conn.Open connstr
- sql = "insert into userinfo select userName,userAccount,userStatus from [userinfo$] in '" & Server.MapPath("hbwlUserInfo.xls")
- & "' 'Excel 8.0;' where userAccount is not null"
- conn.Execute (sql)
SQL Server
大部分人都知道用oledb来读取数据到dataset,但是读取之后怎么处理dataset就千奇百怪了。很多人通过循环来拼接sql,这样做不但容易出错而且效率低下,System.Data.SqlClient.SqlBulkCopy 对于新手来说还是比较陌生的,这个就是传说中效率极高的bcp,6万多数据从excel导入到sql只需要4.5秒。using System;
using System.Data;
using System.Windows.Forms;
using System.Data.OleDb;
namespace WindowsApplication2
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
private void button1_Click(object sender, EventArgs e)
{
//测试,将excel中的sheet1导入到sqlserver中
string connString = "server=localhost;uid=sa;pwd=sqlgis;database=master";
System.Windows.Forms.OpenFileDialog fd = new OpenFileDialog();
if (fd.ShowDialog() == DialogResult.OK)
{
TransferData(fd.FileName, "sheet1", connString);
}
}
public void TransferData(string excelFile, string sheetName, string connectionString)
{
DataSet ds = new DataSet();
try
{
//获取全部数据
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + excelFile + ";" + "Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
strExcel = string.Format("select * from [{0}$]", sheetName);
myCommand = new OleDbDataAdapter(strExcel, strConn);
myCommand.Fill(ds, sheetName);
//如果目标表不存在则创建
string strSql = string.Format("if object_id('{0}') is null create table {0}(", sheetName);
foreach (System.Data.DataColumn c in ds.Tables[0].Columns)
{
strSql += string.Format("[{0}] varchar(255),", c.ColumnName);
}
strSql = strSql.Trim(',') + ")";
using (System.Data.SqlClient.SqlConnection sqlconn = new System.Data.SqlClient.SqlConnection(connectionString))
{
sqlconn.Open();
System.Data.SqlClient.SqlCommand command = sqlconn.CreateCommand();
command.CommandText = strSql;
command.ExecuteNonQuery();
sqlconn.Close();
}
//用bcp导入数据
using (System.Data.SqlClient.SqlBulkCopy bcp = new System.Data.SqlClient.SqlBulkCopy(connectionString))
{
bcp.SqlRowsCopied += new System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.BatchSize = 100;//每次传输的行数
bcp.NotifyAfter = 100;//进度提示的行数
bcp.DestinationTableName = sheetName;//目标表
bcp.WriteToServer(ds.Tables[0]);
}
}
catch (Exception ex)
{
System.Windows.Forms.MessageBox.Show(ex.Message);
}
}
//进度显示
void bcp_SqlRowsCopied(object sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
{
this.Text = e.RowsCopied.ToString();
this.Update();
}
}
}上面的TransferData基本可以直接使用,如果要考虑周全的话,可以用oledb来获取excel的表结构,并且加入ColumnMappings来设置对照字段,这样效果就完全可以做到和sqlserver的dts相同的效果了。
- 将Excel数据快速大批量导入数据库的代码
- 将Excel数据快速大批量导入数据库的代码
- 将Excel数据快速大批量导入数据库的代码
- 将Excel数据快速大批量导入数据库的代码
- 将Excel数据快速大批量导入数据库
- 将excel数据快速导入数据库
- c#将Excel数据导入到数据库的实现代码
- Delphi编程将数据库数据快速导入Excel
- Delphi编程将数据库数据快速导入Excel
- Delphi编程将数据库数据快速导入Excel
- Delphi编程将数据库数据快速导入Excel
- Delphi编程将数据库数据用流快速导入Excel
- Delphi编程将数据库数据快速导入Excel
- excel数据快速导入数据库
- 很牛的一条SQL -------将Excel数据快速导入数据库
- 将数据库数据导入Excel
- 将excel数据导入数据库
- ASP.NET将EXCEL导入SQL SERVER数据库的代码
- sql格式化日期
- 谈PHP生成静态页面
- C#实现的水纹特效例子
- linux -- 搜索目录下所有具有某个字符串的文件夹
- BMP格式详解
- 将Excel数据快速大批量导入数据库的代码
- Linux中tty pty pts 概念区别
- SQL SERVER单页数据存储行数计算
- WMP和REAL的一些属性 方法
- RPM 的介绍和应用
- 普元软件获一亿战略投资 董事长刘亚东谈三大核心竞争优势
- QueryPerformanceFrequency() - 基本介绍(转)
- java xml知识
- 英特尔CEO欧德宁:2010年企业PC支出将出现增长