Data Import from Excel to database Trilogy

Some time ago, the examination system had to add a new function. to import the student table information in batches, that is, the information needs to be imported to the database table from the Excel file. The problem can be solved after a long time.

1. If the table is not created, we need to create a new table in the database table to store Excel Data:

Import Statement in SQL Server:

Select * revoke city2 from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'datasource = "F: \ test.xls"; user id = admin; Password =; extendedproperties = Excel 5.0 ')... [sheet1 $]

Note that if you write this statement directly, the following error occurs:

SQL Server blocks access to the statement 'openrowset/OpenDataSource 'of the 'ad hocdistributed queries' component because the component has been disabled as part of the server's security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hocdistributed querys', see "peripheral application configurator" in SQL Server books online ".

Therefore, we need to start the service here:

The START statement is:

execsp_configure 'show advanced options',1reconfigureexecsp_configure 'Ad Hoc Distributed Queries',1reconfigure

Of course, remember to close it after use:

The closing statement is:

execsp_configure 'Ad Hoc Distributed Queries',0reconfigureexecsp_configure 'show advanced options',0reconfigure

Because the examination system is implemented based on ASP.net, it is the implementation code of Asp.net. Note that because the statements contain special symbols such, we need to use escape characters to make these special symbols a string type, here are some common escape character symbols: http://baike.baidu.com/view/73.htm

 

   protectedvoid btntoLaad_Click(object sender, EventArgs e){ SqlConnectionmycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");string sqlstr = "SELECT * into cityFROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'Data Source=\"f:\\test.xls\";User ID=Admin;Password=;Extended properties=Excel5.0')...[Sheet1$]";         SqlCommand cmd = new SqlCommand(sqlstr, mycon);        mycon.Open();        cmd.ExecuteNonQuery();        mycon.Close();}

In this way, a city table is created in the database to store data in Excel.

2. Import an Excel table to an existing database table

Note that the data in the Excel table must be consistent with that in the database table.

For example, if the field in the database table is

The corresponding Excel table field is:

protectedvoid btnExist_Click(object sender, EventArgs e)    {        SqlConnection mycon = new SqlConnection("server=.;database=qingniao;uid=sa;pwd=123");        string sqlstr = " insert intocity1 SELECT * FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0', 'DataSource=\"f:\\test.xls\";User ID=Admin;Password=;Extendedproperties=Excel 5.0')...[Sheet1$]";             SqlCommand cmd = new SqlCommand(sqlstr,mycon);         mycon.Open();         cmd.ExecuteNonQuery();         mycon.Close();    }

You also need to enable the service here, which is the same as the first method.

Iii. Existing tables usually have some settings, such as primary key, foreign key, or others. If the primary key or foreign key conflicts, the import may fail. Therefore, we need to judge the data in the Excel table.

Import the data to the able.

Protected void btnleadingin_click (objectsender, eventargs e) {datatable dt = new datatable (); dt = createexceldatasource ("F: \ abc.xls"); sqlconnection sqlcon = con (); sqlcon. open (); gridview1.datasource = DT; gridview1.databind (); For (INT I = 0; I <DT. rows. count; I ++) {// import data to the database. Writing data to the database should be very simple. I won't write more here.} public static datatablecreateexceldatasource (string URL) {datatable dt = NULL; // string connetionstr = "provider = Microsoft. ace. oledb.12.0; "+" Data Source = "+ URL +"; "+" extended properties = 'excel 8.0; HDR = yes; IMEX = 1 ';"; string connetionstr = "provider = Microsoft. jet. oledb.4.0; "+" Data Source = "+ URL +"; extended properties = 'excel 8.0; HDR = yes; IMEX = 1 '"; string strsql = "select * from [sheet1 $]"; oledbconnection oleconn = new oledbconnection (connetionstr); oledbdataadapter oleadapter = new oledbdataadapter (strsql, connetionstr ); try {dt = new datatable (); oleadapter. fill (DT); Return DT;} catch (exception ex) {Throw ex;} finally {oleadapter. dispose (); oleconn. close (); oleconn. dispose ();}}

Of course, I feel that it is at least time-consuming and memory-consuming to retrieve the data in the datatable one by one, and it is best to add rollback items here, this problem often occurs during the import process. When using a transaction, you can roll back the data to the status before the import to prevent unexpected events, I will not add them here.

The above three methods are the Excel import that we have implemented. Of course, I think there are more than these methods. Of course, in addition to import, there are also ways to export data from a database table to an Excel table, because the part of the system I did not involve, I will not mention it here.

The following describes the detours I have taken during the solution:

I. I have not figured out all the practices for importing databases. For example, whether to directly create a table or import an existing table, I cannot find a proper method at the beginning.

2. In the subsequent search process, I found that I had a big problem, that is, my keyword is in "Asp.net,". In fact, since it is imported to the database, Asp.net is just a single execution. Therefore, because of the keyword constraints, we found little information and formed a mindset, nothing except Asp.net.

3. I cannot understand the information I cannot find. I can only use it if I can. I can change it if I cannot. That is why I have been using the escape string for a long time, because when SQL statements are not incorrectly executed, we need to put them in Asp.net for execution, because these statements require a string to be displayed, and this execution statement contains quotation marks, so we need to escape characters. After solving the problem of quotation marks, I found that it was still incorrect. After a long time, I found that it was path F: in \ test.xls, "\ t" is the Escape Character of the table. Therefore, two \ escape characters are required here. This should be written as "F: \ test.xls ", when I wrote it like this, I remembered that in fact all the code was like this at the beginning of the query, but I didn't think much about it at the time, I thought the path should be like this, and eventually it will continue to fall behind.