Import Excel Data to SQL Server

Question

I need to import the data that is in excel into a table in sql. I looked at several websites but nothing has worked so far. Using sql server management studio I have done the following so far, following the step by step:

sp_configure
'show advanced options', 1
reconfigure
sp_configure
'Ad Hoc Distributed Queries', 1 
reconfigure

/* Creating the table with my spreadsheet attributes */

create table funcionario(
Código INT NOT NULL PRIMARY KEY,
Nome VARCHAR(100) NOT NULL,
Admissão SMALLDATETIME NULL,
Departamento VARCHAR(100) NULL )

/* Command to import the sheet into the table */

INSERT INTO FUNCIONARIO SELECT
* FROM OPENROWSET ('Microsoft.Jet.OleDB.4.0',
'EXCEL 8.0;Database=F:departamento.XLS'
,Dados$)

Everything goes well in this case until the last part, where I have the following error:

Cannot create an instance of OLE DB provider "Microsoft.Jet.OleDB.4.0" for linked server "(null)".

I really don’t know what else to do.

Answer 1

You can use the SQL Server Import and Export Wizard (included in SQL Server ) to do this.

With it, you can even create the target table, or use an existing table.

Using the 32-bit version, choose the Microsoft Excel provider for the data source and at destination the SQL Server driver.