[SQL Server] Sort out the usage and problems of connecting OpenRowSet and OpenDataSource functions to excel

Sort out the usage and problems of connecting OpenRowSet and OpenDataSource functions to excel

 

First, we made such an Excel project with two tables,

 

 

-- 1. OpenRowSet

 

-- Query two methods
-- 1,
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $]'
)
-- 2,
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
[Sheet1 $]
)
/*
A1 A2 A3
------------------------------------------
1 R 10
2 R 11
3 R 12
4 R 13
5 R 14
1 12 15
1 18 16
1 14 17
2 19 null -------------> the preceding data is a number.
2 30 null -------------> so these non-numbers change to null.
2 21 null -------------> only the preceding number can be changed to a non-digit number.
1 12 null
1 18 null
1 14 null
2 19 null
2 30 null
2 21 null

(17 rows are affected)

*/

-- Insert
Insert OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $]'
) Select 101, 'aaa', 123
/* --- This does not work, nor does update Delete work.
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Insert into [sheet1 $] values (12,111,101 )'
)
*/

Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $]'
) Where a1 = 101

/* -- This works.
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $] Where a1 = 100'
)
*/
/*
A1 A2 A3
----------------------------------------------------
101 AAA 123

(One row is affected)

*/

-- Update
Update OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $]'
) Set a2 = 'bbb', A3 = 345 where a1 = 101

Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet1 $]'
) Where a1 = 101
/*
A1 A2 A3
----------------------------------------------------
101 BBB 345

(One row is affected)

*/

-- Delete
Delete from OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password =; HDR = yes; IMEX = 2 ',
'Select * from [sheet1 $]'
) Where a1 = 101
/*
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" returns a message. "This isam does not support data deletion in the chain table. ".
Message 7345, level 16, state 1, 1st rows
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" Cannot delete data from the table "select * from [sheet1 $. Recoverable provider-specific errors, such as RPC failure.
*/
Delete OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password =; HDR = yes; IMEX = 2 ',
[Sheet1 $]
) Where a1 = '000000'
/*
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" returns the message "from clause syntax error. ".
Message 7321, level 16, status 2, 1st rows
Prepare the ole db access interface "Microsoft. jet. oledb.4.0 "An error occurred while executing the query" delete from sheet1 $ where 'a1' = (1.00000000000000e + 002.
*/
Delete OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password =; HDR = yes; IMEX = 2 ',
[Sheet1 $]
) Where a2 = 'bbb'
/*
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" returns a message. "This isam does not support data deletion in the chain table. ".
Message 7345, level 16, state 1, 1st rows
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" Cannot delete data from the table "sheet1 $. Recoverable provider-specific errors, such as RPC failure.
*/

---> Deletion is not supported.

-- 2. OpenDataSource and OpenRowset are basically the same. There are only some differences.

-- 1 Query
Select * From OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = "C:/test.xls"; extended properties = "Excel 8.0; HDR = yes; IMEX = 2;" '-- "This parameter can be set to include, but multiple attributes must contain
)... [Sheet1 $]

-- 2 Increase
Insert OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = C:/test.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 2 ;"'
)... [Sheet1 $]
Select '123', 'ccc ', '123'

-- 3 Update

Update OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = C:/test.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 2 ;"'
)... [Sheet1 $]
Set a2 = 'ddd ', A3 = '000000' where a1 = 203
/*
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" returns the syntax error of the message "Update statement. ".
Message 7321, level 16, status 2, 1st rows
Prepare the ole db access interface "Microsoft. jet. oledb.4.0 "execution query" Update sheet1 $ set 'a2 '= 'ddd', 'a3 '= (2.030000000000000e + 002) Where 'a1' = (1.020000000000000e + 002) "error.
*/
----- ^ Seems to have poor support for numeric types

Update OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = C:/test.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 2 ;"'
)... [Sheet1 $]
Set a2 = 'ddd ', A3 = '000000' where a2 = 'ccc'
-- This sentence is correct.

-- 4 Delete
Delete OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = C:/test.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 2 ;"'
)... [Sheet1 $] Where a2 = 'bbb'
/*
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" returns a message. "This isam does not support data deletion in the chain table. ".
Message 7345, level 16, state 1, 2nd rows
The ole db access interface "Microsoft. Jet. oledb.4.0" of the linked server "(null)" Cannot delete data from the table "sheet1 $. Recoverable provider-specific errors, such as RPC failure.
*/

One difference:
The table name sheet-2 in Excel contains the '-' character, which cannot be supported in OpenDataSource in any way. OpenRowSet can solve this problem.

Select * From OpenDataSource (
'Microsoft. Jet. oledb.4.0 ',
'Data source = C:/test.xls; extended properties = "Excel 8.0; HDR = yes; IMEX = 2 ;"'
)... [Sheet-2 $]
/*
Message 7314, level 16, state 1, 1st rows
The ole db access interface for the linked server "(null)" "Microsoft. Jet. oledb.4.0" does not contain the table "sheet-2 $ ". The table does not exist, or the current user does not have the permission to access the table.

*/
--- This is feasible
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
'Select * from [sheet-2 $]'
)

-- This is not acceptable.
Select * From OpenRowSet (
'Microsoft. Jet. oledb.4.0 ',
'Excel 5.0; database = C:/test.xls; user id =; Password = ;',
[Sheet-2 $]
)

 

------------

Special note

Extended properties = 'excel 8.0; HDR = yes; IMEX = 1'

A: HDR (header row) settings

If the value is yes, the first row of the worksheet in the Excel file is the column name.

If the value is no, the first row of the worksheet in the Excel file is the item, and there is no column name.

B: IMEX (Import Export mode) settings

There are three modes for IMEX, And the read/write behaviors are also different:

0 is export Mode

1 is import Mode

2 is linked mode (full update capabilities)

Here I particularly want to explain the IMEX parameter, because different modes represent different read/write behaviors:

When IMEX = 0, the "Export mode" is enabled. The Excel files enabled in this mode can only be used for "writing" purposes.

When IMEX = 1, it is set to "Import mode". The Excel files opened in this mode can only be used for "read" purposes.

When IMEX = 2, the connection mode is enabled. The Excel files enabled in this mode support both "read" and "write.