Sqlserver reads data in Excel

1. sqlserver reads data from Excel

Select *

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]

2. sqlserver insert data in Excel

2.1Insert an existing table

Insert into T_1 (A, B)

Select a, B

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $] As derivedtbl_1

2.2 Insert a new table

 

Select a, B

Into T_2

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',

'Data source = "C:/book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $] As derivedtbl_1

FAQs:

(1) Microsoft. Jet. oledb.4.0 is not registered on the local computer.Program

Problems and Solutions:

1. Microsoft. Jet. oledb.4.0 should not be written as miscrosoft. Jet. oledb.4.0 ,;

2. Do not write data source as "Data sourse ",

3. Check whether the Office is installed

4.reinstall and register mdac28.exe. (generally, your installation directory contains or goes to Microsoft's official website)

5. this problem may also occur on a 64-bit platform. Solution: Generate, Configure Manager, and platform. Click the any CPU tab. Choose new from create platform to x86.
(2) Enable OpenRowSet and OpenDataSource support for sqlserver

And then restart sqlserver

For details, refer: