CODEWITHSUNDEEP
sql-serverexcelsql-server-2012oledbconnection
Arash
Arash

Reputation: 1826

Import data from Excel into SQL Server

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:

Upvotes: 0

Views: 3712

Answers (3)

Vishwaram Sankaran
Vishwaram Sankaran

Reputation: 357

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.

Upvotes: 1

whywake
whywake

Reputation: 910

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.

Upvotes: 0

Chamithra Thenuwara
Chamithra Thenuwara

Reputation: 419

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$]');

Upvotes: 0

Related Questions