Setup A Excel file as A linked server in ms-sql

USE [master]
GO
sp_configure ‘show advanced options’, 1
GO
RECONFIGURE WITH OverRide
GO
sp_configure ‘Ad Hoc Distributed Queries’, 1
GO
RECONFIGURE WITH OverRide
GO

EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’AllowInProcess’ , 1
GO

EXEC master . dbo. sp_MSset_oledb_prop N’Microsoft.ACE.OLEDB.12.0′ , N’DynamicParameters’ , 1

EXEC sp_addLinkedServer
@server= ‘XLSX_MATRIX’,
@srvproduct = ‘ACE 12.0’,
@provider = ‘Microsoft.ACE.OLEDB.12.0’,
@datasrc =’D:\SQL\vn_item_no.xlsx’,
@provstr = ‘Excel 12.0; HDR=Yes’

SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [Application$]’)
SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [Device$]’)
SELECT * FROM OPENQUERY (XLSX_MATRIX, ‘Select * from [User$]’)

SELECT * FROM XLSX_MATRIX…[Application$]
SELECT * FROM XLSX_MATRIX…[Device$]
SELECT * FROM XLSX_MATRIX…[User$]

select b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[工作表1$] a right outter join [XLSX_MATRIX]…[‘原料(B倉)$’] b on a.f_2=b.f_2 where (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null)
order by 1

select b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on a.f_2=b.f_2
where (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) and sort_id is not null
order by sort_id

final**
select distinct b.*,a.item_no,a.ref_no from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on a.f_2=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

select distinct b.*,a.item_no,a.ref_no,isnull(a.v_no,a.t_no) from [XLSX_MATRIX]…[‘原料(B倉)$’] b left outer join [XLSX_MATRIX]…[工作表1$] a on isnull(a.v_no,a.t_no)=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

AccessDatabaseEngine_X64 maybe needed for Microsoft.ACE.OLEDB.12.0 driver Microsoft Access Database Engine 2010 可轉散發套件

p.s.使用openrowset 可直接讀excel檔案，不用設置linked server
select a.* from
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=2;Database=c:\temp\Employee.xls’,[工作表1$])) a,
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=2;Database=c:\temp\Employee.xls’,[工作表1$])) b
where a.col1 = b.col1

select distinct b.*,a.item_no,a.ref_no,isnull(a.v_no,a.t_no) from
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=1;Database=D:\SQL\vn_item_no.xlsx’,[‘原料(B倉)$’])) b left outer join
(SELECT * FROM OpenRowSet(‘Microsoft.ACE.OLEDB.12.0’,
‘Excel 12.0;HDR=Yes;IMEX=1;Database=D:\SQL\vn_item_no.xlsx’,[工作表1$])) a on isnull(a.v_no,a.t_no)=b.f_2
and (b.ref_no like ‘%’+a.ref_no+’%’ or (a.ref_no) is null) where sort_id is not null
order by sort_id

(註1)SQL Server預設不允許使用 OPENROWSET 和 OPENDATASOURCE 進行特定分散式查詢。使用 OpenRowSet前，需使用sp_configure 來啟用 ‘Ad Hoc Distributed Queries’

EXEC sp_configure 'show advanced options','1'
RECONFIGURE 
EXEC sp_configure 'Ad Hoc Distributed Queries',1
RECONFIGURE 

參照

Import data from Excel to SQL Server or Azure SQL Database

使用 OpenRowset、OpenDataSource 查詢EXCE

設定 IMEX=1 透過 OleDb 讀取 Excel 仍然出現 null 值的問題