Azure SQL存储过程中通过SQL查询读取Excel数据遇阻求助

问题概述 #

尝试通过SQL查询读取Excel文件并插入临时表时，遭遇以下版本限制错误：

• 此SQL Server版本不支持'OPENDATASOURCE'行集提供程序。
• 此SQL Server版本不支持'OPENROWSET'行集提供程序。
• 此SQL Server版本不支持链接服务器。

已尝试的操作均无效：

1. 使用OPENROWSET查询Excel：

SELECT * FROM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 8.0;HDR=NO;Database=T:\temp\Test.xlsx',
    'select * from [sheet1$]')

2. 使用OPENDATASOURCE查询Excel：

SELECT * FROM OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0', 'Data Source=C:\Users\Downloads\Excel Logic File.xlsx;Extended Properties=EXCEL 12.0')..[Sheet1$];

3. 启用高级分布式查询配置：

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
GO
EXEC sp_configure 'ad hoc distributed queries', 1
RECONFIGURE
GO

4. 尝试链接服务器查询：

EXEC ('select * from table') at linkedserver

执行后仍报错：Linked servers are not supported in this version of SQL Server

可行替代方案 #

由于当前SQL Server版本不支持分布式查询相关特性，可通过以下方法实现Excel数据导入：

1. SSIS（SQL Server Integration Services）导入 #

创建SSIS包，通过Excel数据源组件读取目标文件，再配置OLE DB目标组件将数据写入SQL Server临时表。该方法支持复杂Excel格式，且不受版本限制。

2. SSMS图形界面手动导入 #

在SQL Server Management Studio中操作：

• 右键目标数据库 → 任务 → 导入数据
• 数据源选择Microsoft Excel，指定文件路径及Excel版本
• 目标选择SQL Server，完成连接配置后，将数据导入预先创建的临时表（如#TempTable）

3. PowerShell脚本批量导入 #

编写PowerShell脚本读取Excel数据，再批量插入SQL Server临时表（需提前安装Microsoft.ACE.OLEDB.12.0驱动）：

# 定义文件路径与连接字符串
$excelPath = "C:\Users\Downloads\Excel Logic File.xlsx"
$excelConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=$excelPath;Extended Properties='Excel 12.0 Xml;HDR=YES';"

# 读取Excel数据到DataTable
$excelConn = New-Object System.Data.OleDb.OleDbConnection($excelConnStr)
$excelConn.Open()
$query = "SELECT * FROM [Sheet1$]"
$cmd = New-Object System.Data.OleDb.OleDbCommand($query, $excelConn)
$adapter = New-Object System.Data.OleDb.OleDbDataAdapter($cmd)
$dataTable = New-Object System.Data.DataTable
$adapter.Fill($dataTable)
$excelConn.Close()

# 连接SQL Server并插入临时表
$sqlConnStr = "Server=YourServerName;Database=YourDBName;Integrated Security=True;"
$sqlConn = New-Object System.Data.SqlClient.SqlConnection($sqlConnStr)
$sqlConn.Open()

# 创建临时表（需根据Excel列结构调整字段定义）
$createTableSql = @"
CREATE TABLE #TempTable (
    Column1 VARCHAR(50),
    Column2 INT,
    Column3 DATETIME
)
"@
$createCmd = New-Object System.Data.SqlClient.SqlCommand($createTableSql, $sqlConn)
$createCmd.ExecuteNonQuery()

# 批量插入数据
$bulkCopy = New-Object System.Data.SqlClient.SqlBulkCopy($sqlConn)
$bulkCopy.DestinationTableName = "#TempTable"
$bulkCopy.WriteToServer($dataTable)

$sqlConn.Close()

4. 转CSV后使用BULK INSERT #

将Excel文件另存为CSV格式，再通过BULK INSERT命令导入临时表：

-- 先创建与CSV列匹配的临时表
CREATE TABLE #TempTable (
    Column1 VARCHAR(100),
    Column2 DATETIME,
    Column3 DECIMAL(18,2)
)

-- 执行BULK INSERT
BULK INSERT #TempTable
FROM 'C:\Users\Downloads\Converted_File.csv'
WITH (
    FIELDTERMINATOR = ',', -- CSV字段分隔符
    ROWTERMINATOR = '\n', -- 行分隔符
    FIRSTROW = 2, -- 若CSV含表头则从第2行开始读取
    TABLOCK
)

内容的提问来源于stack exchange，提问作者T S Nandakumar