Excel Data Import Data Sheet

Environment:

Windows Server (RM)

SQL Server 2012

Excel Data Import Data Sheet

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

Error Resolution: 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 would 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;GORECONFIGURE;GOsp_configure'Ad Hoc Distributed Queries',1;GORECONFIGURE;GOEXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'allowinprocess',1GOEXECMaster.dbo.sp_MSset_oledb_prop N'microsoft.ace.oledb.12.0'N'dynamicparameters',1GO
  View Code
• Now, if you is running OPENROWSET calls you need to abandon calls, made using the old JET parameters and use the new call S as follows:
  (*Example, importing an EXCELfileDirectly intoSQL):D ont do ....SELECT *  from OPENROWSET('microsoft.jet.oledb.4.0','Excel 8.0; Hdr=yes;database=c:\path_to_your_excel_file.xls','select * FROM [sheet1$]') UseThis 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-SQL issues andRan perfectly
  View Code

Excel Data Import Data Sheet