Import Excel Data to the SQL Server database

When you encounter a requirement, You need to import the data in Excel into the SQL Server database. Of course, it is not the kind of direct data import using Enterprise Manager, but the program. My initial consideration was to read the data in Excel to dataset and insert it into the database separately.

Reading data from Excel is not too difficult. Simply put, it is just the following code:

 

Code
Public dataset exceltodataset (string path)
{
Try
{
String constringexcel = @ "provider = Microsoft. Jet. oledb.4.0; Data Source =" + path + "; extended properties = \" Excel 8.0; HDR = no ;\"";
String SQL = "select * from [sheet1 $]";
Dataset DS = new dataset ();
Oledbdataadapter da = new oledbdataadapter (SQL, constringexcel );
Da. Fill (DS );
Return Ds;
}
Catch (exception ex)
{
Throw ex;
}
}

The path is the Excel path.

 

In other cases, such as multiple sheet, work areas, and so on, it is not difficult to check in the garden.

However, considering this is a little troublesome, and basically the Excel format and sheet are fixed, can you use other methods to solve it?

After checking in the park, the park thought that a stored procedure was provided by a great god, using the method of connecting to the server. The basic code is as follows:

 

Code
Alter procedure [DBO]. [pro_xls]
@ Sheetname varchar (50), -- sheet name in Excel
@ Path varchar (2000) -- excel path
As

/**//*
Sp_addmediaserver -- creates a linked server that allows access to distributed, heterogeneous queries against ole db data sources
@ Server = 'user _ list', -- the server alias to be accessed
@ Srvproduct = 'user _ list', -- Name of the product to be added as the ole db data source of the linked server
@ Datasrc = 'd: \ file \ users.xls -- the server to be accessed
*/
Exec sp_addmediaserver @ Server = 'server', @ srvproduct = 'excel ', @ provider = 'Microsoft. jet. oledb.4.0 ', @ datasrc = @ path, @ provstr = 'excel 8.0; HDR = no; IMEX = 1 ;'
Exec sp_add1_srvlogin 'server', 'false'
/**//*
When set xact_abort is on, if a running error occurs when a Transact-SQL statement is executed, the entire transaction is terminated and rolled back.
When set xact_abort is off, only the wrong Transact-SQL statement is generated and the transaction will continue to be processed. If the error is serious, the entire transaction may be rolled back even if set xact_abort is off.
Compilation errors (such as syntax errors) are not affected by set xact_abort.
*/
Set xact_abort on

Begin distributed tran

Insert into test (testname, testpwd, istest) Select * from [@ sheetname $]
If @ error> 0
Goto chkrollback

Chkrollback:
If @ error> 0
Rollback tran
Else
Commit tran

Set xact_abort off
Exec sp_dropserver 'server', null

The above Code was modified by the master in the garden, but it seems that it is still a bit of a problem, mainly because it is not clear about the storage process of the sp_addmediaserver stream, and several parameters do not know what to do.

 

Well, it's a headache.

The result shows that the code is easy to write because it looks like an ad hoc query:

 

Code
-- If you want to import data to an existing table, use

Insert into Table select * From OpenRowSet ('Microsoft. Jet. oledb.4.0'

, 'Excel 5.0; HDR = yes; database = c: \ test.xls ', sheet1 $)

-- If you are importing data and adding a table, use

Select * into table from OpenRowSet ('Microsoft. Jet. oledb.4.0'

, 'Excel 5.0; HDR = yes; database = c: \ test.xls ', sheet1 $)

-- The preceding statement reads all the columns in the sheet 1 worksheet in an Excel file. If you only want to export some columns, you can

Insert into table (A1, A2, A3) Select A1, A2, A3 from OpenRowSet ('Microsoft. Jet. oledb.4.0'

, 'Excel 5.0; HDR = yes; database = c: \ test.xls ', sheet1 $)

 

Distributed Query Portal

 

I am only playing a role here. I hope you will not be enlightened by the great gods in the park.