Import data from Excel into SQL Server

0
votes

I want to import an Excel file into SQL Server but I face an error.

My code:

create table test
(
    AccountNumber varchar(50),  
    AccountName varchar(50),
    ParentAccountNumber varchar(50)

)

select * 
into test 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

Error:

Msg 7302, Level 16, State 1, Line 10
Cannot create an instance of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".

Note:

  • Excel version : 2007
  • SQL Server version : 2012
3
sql-serverexcelsql-server-2012oledbconnection
Generally this means either the driver is not installed or only the 32bit version is installed and your have 64 bit SQL Server - Nick.McDermaid

3 Answers

1
votes

If you are not aware of writing scripts, you can do it by wizard itself.

  1. Right click the DB name you want to import your excel file into. select Task-> Import.
  2. New wizard will open and select the source as Microsoft excel and destination as Sql server, and give the authentication.
  3. Rest all are self expainable.
  4. You can preview the data before you import and you can control the columns you want to import.
  5. Atlast click on finish to run the package.
  6. Will acknowledge you for the successfull import.
0
votes

Try this after table creation,

INSERT INTO test (AccountNumber , AccountName , ParentAccountNumber)
SELECT *
FROM OPENROWSET ('Microsoft.Jet.OLEDB.4.0',
      'Excel 8.0;Database=D:\testing.xls;HDR=YES',
      'SELECT * FROM [Sheet1$]');
0
votes

Try running the below code:

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

select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',   
'Excel 12.0;Database=D:\testing.xls;HDR=YES',   
'SELECT * FROM [Sheet1$]')

Make sure your excel sheet is closed when you are running this code.