Create Linked server with Excel 2007 worksheet in SQL Server 2005

I have written one article to create linked server with Access database but I seen in many forums that people mostly would like to use excel file and wants to import its data. However, there are many ways to import Excel data to SQL Server but Linked server gives you more freedom over data. Let us see how we can do this. Before you start doing this, I would like you to create one excel 2007 file named “Dept” and put it in C: drive.

See my previous article for linked server with Access at

http://www.sqlhub.com/2009/03/linked-server-in-sql-server-2005-from.html

There will be two column in your excel 2007 file with following data in Sheet1

Department Description
MIS IT department
Acct Account Department
Chem Chemist Department

Now let us move to SQL server and make linked server.

EXECUTE sp_addlinkedserver

@server = ‘DeptExcel’,

@srvproduct = ‘ACE 12.0’,

@provider = ‘Microsoft.ACE.OLEDB.12.0’,

@datasrc = ‘C:\Dept.xlsx’,

@provstr = ‘Excel 12.0’

GO

–excel’s credential if any

EXEC sp_addlinkedsrvlogin ‘DeptExcel’, ‘false’

go

–list all tables from excel file

exec sp_tables_ex ‘DeptExcel’

go

–see table in sheet1

SELECT * FROM [DeptExcel]sheet1$

go

–create table in SQL and imports data

select * into tempTable FROM [DeptExcel]sheet1$

go

–check your SQL table

select * from tempTable

Reference: Ritesh Shah

http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com

Advertisements

4 Responses to “Create Linked server with Excel 2007 worksheet in SQL Server 2005”

  1. Anonymous Says:

    I get this error when I run exec sp_tables_ex 'DeptExcel'. I have already run AccessDatabaseEngine.exe:Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DeptExcel" reported an error. Access denied.Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DeptExcel".

  2. Anonymous Says:

    I get this error when I run exec sp_tables_ex 'DeptExcel'. I have already run AccessDatabaseEngine.exe:Msg 7399, Level 16, State 1, Procedure sp_tables_ex, Line 41The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DeptExcel" reported an error. Access denied.Msg 7301, Level 16, State 2, Procedure sp_tables_ex, Line 41Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "DeptExcel".

  3. Ritesh Shah Says:

    do you have proper permission on the file you are using? seems like either permission issue or file is open somewhere

  4. Ritesh Shah Says:

    do you have proper permission on the file you are using? seems like either permission issue or file is open somewhere


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: