使用 Openrowset 从 Excel 2016 中提取数据的 SQL 查询 - 出现语法错误

Question

这个查询在文件位置末尾的逗号附近给我一个语法错误，就在 .谁能告诉我为什么？我已经搜索无济于事。我正在使用 SQL Server 2014 Express 和 Excel 2016。

谢谢

INSERT INTO Simply_Capitation_Lineal (uniqueid, mos, groupid, GroupName, mos2, QnxMemID, dob, age, 
sex, mname, CapRateID, CapTermID, CapEffDate, CapAmount, CapAffID, PCPProvid, PCPFullName, PayToAffID, 
PayToProvid, PayToName, CapDate, PlanID, MedicareID, Simple_County, [cover&OME], Triangle_Cover, 
CarrierMemberID, CheckNumber)
SELECT uniqueid, mos, groupid, GroupName, mos2, QnxMemID, dob, age, 
sex, mname, CapRateID, CapTermID, CapEffDate, CapAmount, CapAffID, PCPProvid, PCPFullName, PayToAffID, 
PayToProvid, PayToName, CapDate, PlanID, MedicareID, Simple_County, [cover&OME], Triangle_Cover, 
CarrierMemberID, CheckNumber
FROM OPENROWSET('MSDASQL',
                'driver=IMAHQ12\sqlexpress',
                'excel 12.0 xml; Database=C:\Users\td\Dropbox (IMA Team)\IMA Utilization Reports\DB\201605\Simply\Capitation Lineal - INHEALTH MD ALLIANCE LLC - MSO - FINANCE - 2016.04.xlsx','SELECT * FROM [Detail$]')

Answer 1

考虑使用与 Excel 兼容的驱动程序:

...
SELECT * FROM
OPENROWSET('MSDASQL','DRIVER={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};
     DBQ=C:\Users\td\Dropbox (IMA Team)\IMA Utilization Reports\DB\201605\Simply\Capitation Lineal - INHEALTH MD ALLIANCE LLC - MSO - FINANCE - 2016.04.xlsx', 
     'SELECT * FROM [Detail$]')

或者，您可以使用提供程序版本:

SELECT * FROM
OPENDATASOURCE('Microsoft.ACE.OLEDB.12.0',
   'Data Source=C:\Users\td\Dropbox (IMA Team)\IMA Utilization Reports\DB\201605\Simply\Capitation Lineal - INHEALTH MD ALLIANCE LLC - MSO - FINANCE - 2016.04.xlsx;Extended Properties=Excel 12.0')
   ...Detail$