Import Excel Data to the SQL Server database

These two days, I made a small tool for batch import of EXCEL to the SQL Server database.

 

It is not difficult to find a wide search on the Internet. The simplest way is to use an import tool self-contained in SQL, you only need to use a t-SQL statement. The statement is as follows:

Select * into student from OpenDataSource
('Microsoft. Jet. oledb.4.1,
'Data source = "F:/student.xls"; user id = admin; Password =; extended properties = "Excel 8.0; HDR = yes; IMEX = 1 "')
... Sheet1 $

 

After processing the Statement, the statement cannot be executed and an error message is displayed: "SQL Server has blocked access to the statement 'openrowset/OpenDataSource 'that creates 'ad hoc distributed queries, this component is disabled as part of the server security configuration. The system administrator can enable 'ad hoc distributed queries 'by using sp_configure '. For more information about enabling 'ad hoc distributed querys', see "peripheral application configurator" in SQL Server books online ".

 

Run the SQL statement to enable ad hoc distributed queries:

Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

 

Running error:

 

After I checked the n data, I found that it may be an Access Version problem. I found that the version on my machine is 2000, so I changed the interface to Microsoft. Jet. oledb.3.51. Start with an error:

 

I have tested n methods and searched many answers, but the results are still not solved. It is very depressing.

We also found that we learned too narrowly in the school, with a wide range of knowledge and a low level of knowledge. So I had to go to Baidu when I encountered a problem. Baidu came out with a lot of methods that didn't know whether they were correct or wrong, and then practiced them one by one. Lucky people quickly discovered the answer, and unfortunately people are struggling to find but fail. The Network is a good thing, but it may not make you work efficiently.

 

At the same time, I hope you can help me to see what the problem is?

Note: During the import process, My Excel files are closed;