excel 数据导入数据表

环境：

Windows server 2012 rm

sql server 2012

excel 数据导入数据表

INSERT INTO [dbo].[AdminUser]
            
    SELECT [AdminUserID]
           ,[NameZH]
           ,isnull( [NameEng],'') as [NameEng]
           ,[Password]
           ,[CreateDateTime]
           ,[UpdateDateTime]
           ,[RecordTimeStamp] FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 
'Excel 12.0;Database=C:WebsiteMacauStoreDataAllData_20151216.xls;HDR=YES;IMEX=1',
'select * from [AdminUser$]')

View Code

错误解决：http://stackoverflow.com/questions/13888082/ole-db-provider-microsoft-ace-oledb-12-0-for-linked-server-null-returned-m

Download and install the new component from Microsoft: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=c06b8369-60dd-4b64-a44b-84b371ede16d&displaylang=en
- This will install the access and other engines you need to set up linked servers, OPENROWSET excel files, etc.

Open up SQL Server and run the following:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
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
GO

View Code

Now, if you are running OPENROWSET calls you need to abandon calls ,made using the old JET parameters and use the new calls as follows:

(*Example, importing an EXCEL file directly into SQL):
DONT DO THIS….
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','Excel 8.0;HDR=YES;Database=c:PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')

USE THIS INSTEAD…
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=c:PATH_TO_YOUR_EXCEL_FILE.xls','select * from [sheet1$]')

*At this point resolved two SQL issues and ran perfectly

View Code