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:\Website\MacauStore\Data\AllData_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

转载于:https://www.cnblogs.com/xiaobuild/p/5050981.html

相关资源：java实现Excel数据导入到数据库