大数据量的数据如何导入Mysql 大数据量excel导入数据库

转载

lanhy 2023-11-03 11:03:31

文章标签 大数据量的数据如何导入Mysql excel 数据库 dataset sqlserver 文章分类 MySQL 数据库

介绍两种途径将数据从EXCEL中导入到SQL

一、在程序中，用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里了。

采用这种方式，数据库

二、在查询分析器里，直接写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数据库中，原来的做法是读一条写一条，若导入上万条的数据需要几分仲时间，速度很慢。有没有最快的方法呢？经本人研究、反复的实验，终于写出了最快速的批量导入大批量数据的方法，上万条数据只需几秒钟就可全部导入，够快了吧。代码公布出来与大家分享。

Sql代码

1. Set  conn = Server.CreateObject( "adodb.Connection"
2. connstr =  "Provider=Microsoft.Jet.OLEDB.4.0; Data source="  & Server.MapPath( "test.mdb"
3. conn. Open
4. sql =  "insert into userinfo select userName,userAccount,userStatus from [userinfo$] in '"  & Server.MapPath( "hbwlUserInfo.xls"
5. "' 'Excel 8.0;' where userAccount is not null"
6. 7. conn. Execute  (sql)  


SQL Server 
 8.     
9.         　　大部分人都知道用oledb来读取数据到dataset，但是读取之后怎么处理dataset就千奇百怪了。很多人通过循环来拼接sql，这样做不但容易出错而且效率低下，System.Data.SqlClient.SqlBulkCopy 对于新手来说还是比较陌生的，这个就是传说中效率极高的bcp，6万多数据从excel导入到sql只需要4.5秒。 
       

 10.          
        
11. using 
12.          System;
13.  
14.         using 
         System.Data;
15.  
16.         using 
         System.Windows.Forms;
17.  
18.         using 
         System.Data.OleDb;
19.  
20.         namespace 
         WindowsApplication2
21.  
22.         
23.  
24.         {
25.       public   partial   class  Form1 : Form
26.       
27.  {
28.           public  Form1()
29.           
30.  {
31.              InitializeComponent();
32.          } 
33. 
34.           private   void  button1_Click( object  sender, EventArgs e)
35.           
36.  {
37.               // 测试，将excel中的sheet1导入到sqlserver中 
38.               string  connString  =   " server=localhost;uid=sa;pwd=sqlgis;database=master " ;
39.              System.Windows.Forms.OpenFileDialog fd  =   new  OpenFileDialog();
40.               if  (fd.ShowDialog()  ==  DialogResult.OK)
41.               
42.  {
43.                  TransferData(fd.FileName,  " sheet1 " , connString);
44.              } 
45.          } 
46. 
47.           public   void  TransferData( string  excelFile,  string  sheetName,  string  connectionString)
48.           
49.  {
50.              DataSet ds  =   new  DataSet();
51.               try 
52.               
53.  {
54.                   // 获取全部数据 
55.                   string  strConn  =   " Provider=Microsoft.Jet.OLEDB.4.0; "   +   " Data Source= "   +  excelFile  +   " ; "   +   " Extended Properties=Excel 8.0; " ;
56.                  OleDbConnection conn  =   new  OleDbConnection(strConn);
57.                  conn.Open();
58.                   string  strExcel  =   "" ;
59.                  OleDbDataAdapter myCommand  =   null ;
60.                  strExcel  =   string .Format( " select * from [{0}$] " , sheetName);
61.                  myCommand  =   new  OleDbDataAdapter(strExcel, strConn);
62.                  myCommand.Fill(ds, sheetName);
63. 
64.                   // 如果目标表不存在则创建 
65.                   string  strSql  =   string .Format( " if object_id('{0}') is null create table {0}( " , sheetName);
66.                   foreach  (System.Data.DataColumn c  in  ds.Tables[ 0 ].Columns)
67.                   
68.  {
69.                      strSql  +=   string .Format( " [{0}] varchar(255), " , c.ColumnName);
70.                  } 
71.                  strSql  =  strSql.Trim( & apos;, & apos;)  +   " ) " ;
72. 
73.                   using  (System.Data.SqlClient.SqlConnection sqlconn  =   new  System.Data.SqlClient.SqlConnection(connectionString))
74.                   
75.  {
76.                      sqlconn.Open();
77.                      System.Data.SqlClient.SqlCommand command  =  sqlconn.CreateCommand();
78.                      command.CommandText  =  strSql;
79.                      command.ExecuteNonQuery();
80.                      sqlconn.Close();
81.                  } 
82.                   // 用bcp导入数据 
83.                   using  (System.Data.SqlClient.SqlBulkCopy bcp  =   new  System.Data.SqlClient.SqlBulkCopy(connectionString))
84.                   
85.  {
86.                      bcp.SqlRowsCopied  +=   new  System.Data.SqlClient.SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
87.                      bcp.BatchSize  =   100 ; // 每次传输的行数 
88.                      bcp.NotifyAfter  =   100 ; // 进度提示的行数 
89.                      bcp.DestinationTableName  =  sheetName; // 目标表 
90.                      bcp.WriteToServer(ds.Tables[ 0 ]);
91.                  } 
92.              } 
93.               catch  (Exception ex)
94.               
95.  {
96.                  System.Windows.Forms.MessageBox.Show(ex.Message);
97.              } 
98. 
99.          } 
100. 
101.           // 进度显示 
102.           void  bcp_SqlRowsCopied( object  sender, System.Data.SqlClient.SqlRowsCopiedEventArgs e)
103.           
104.  {
105.               this .Text  =  e.RowsCopied.ToString();
106.               this .Update();
107.          } 
108. 
109. 
110.      } 
111.  }