SQL Server Stored Procedure for importing Excel Data

I. The code for creating a stored procedure is as follows:

-- Create stored procedure set quoted_identifier ongoset ansi_nulls ongocreate procedure folder @ err1 intset @ err1 = 0 set nocount on -- SET transaction isolation level SERIALIZABLE--OLE/DB provider does not support isolation layer Tran in tran -- start transaction -- set xact_abort on -- If (select count (*) from fh_sk2) = 0 begin -- import data from an Excel table to an insert into fh_sk2 select * From OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ test.xls"; extended properties = Excel 8.0 ')... [sheet1 $] End commit transet nocount offgoset quoted_identifier offgoset ansi_nulls ongo -- execute sqlexecute u_fh_sk -- Query imported data select * From fh_sk2

PS: fh_sk2is the data table in SQL, test.xls is located under drive C, and sheet1 $ is the default first tab page (it is worth noting that the number and type of fields in the database table and Excel table must be exactly the same)

If there are many Excel fields, the column/column conversion problem is involved as follows:

A word table shown in 1 is used as an example to introduce the specific transpose method.

　　

Figure 1

1. in word, right-click the cross icon in the upper left corner of the table and select the entire table. Then, right-click the table and choose copy from the context menu.

2. Next, open the Excel file, right-click any cell, select the "paste SELECT> text" command, and paste the Word Table to the Excel file. 2.

　　

Figure 2

3. Right-click and copy the cells with data in figure 2 (Remember, this step is indispensable and important !)

4. Switch to another blank worksheet, right-click the worksheet, and execute the "paste selectively" command. Finally, in the displayed Dialog Box 3, select the "transpose" check box and click "OK ".

　　

Figure 3

5. at this time, you will find that the rows and columns in the Excel table have been exchanged according to our requirements, and the data of each cell is not bad, then copy the converted table back to word. 4 is the word table that has been converted. How can this problem be solved? Good results.

　　

2. Execute the Stored Procedure

During the storage process, the following error message may be displayed: MSDTC on server ××× is unavailable.

The solution is as follows:

MSDTC (Distributed Transaction Coordinator) coordinates transactions across multiple databases, message queues, file systems, and other resource managers. The service is named msdtc.exe. The process calls Microsoft Personal Web server and Microsoft SQL Server. This service is used to manage multiple servers.
Location: control panel -- Administrative Tools -- service -- Distributed Transaction Coordinator
Dependency: Remote Procedure Call (RPC) and security accounts manager
Suggestion: Generally, it is not involved in the home computer, unless you enable the Message Queuing service, you can stop. Solution: 1. choose Windows Control Panel> Administrative Tools> services> Distributed Transaction Coordinator> Properties> Start 2. run "Net start MSDTC" under cmd to enable the Service.

NOTE: If Step 1 Distributed Transaction Coordinator cannot be started, it is because the log file is lost, re-create the log file, and then start it. To recreate the MSDTC log and restart the service, follow these steps:
(1) Click Start, click Run, Enter cmd, and then press OK ".
(2) Input MSDTC-resetlog (note that when running this command, do not execute the pending transaction)
(3) Enter Net start MSDTC and press Enter!