Acessing Remote Excel Files/Csv/Text Files data from Remote Server to your Local Database.

Today i am going to Introduce how to Access Remote Excel Files/Csv Files/Text Files data to Your Local sql-server table.So that with no time we can insert or bulk copy the Data to your local sql-server .So what are the steps we need to follow and how to use the query to dump or insert the data in sql-server.I will illustrate here.

There are many approaches importing Excel data in sql-server.One way of approach is by using Sql-server query to import the Excel/csv/textfile data is the best way from Other ways.

First we need to install AccessDatabaseEngine 2010 distribution package.

After installing the Above package You see in your server object Micorsoft Ace.Oledb12.0 and having server roles.for Server roles right click on Your User Account and Select server roles and check all check boxes.

Server Object and server roles

Execute these stored procedures where some of the Properties will get set with this

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
sp_configure 'show advanced option', 1
sp_configure 'Ad Hoc Distributed Queries', 1

Bulkinsert ,XP_CMDSHELL,OPENROWSET,bcputility,opendatasource.

But before Using all these. Let us discuss How to set your Local sql-server for accessing the Remote File.Without these settings you can not access the Remote file to your Local system.

1)Make Sure that your Sqlserver Accessing the Remote Server.

By right Clicking the sql-server Server Name in the Object Explorer pane Enable Checkbox for remote servers
Go to Connections Check the Checkbox Allow remote Connections Enabled to true.

Allow Remote Connections to sqlserver

2)Make Sure that your settings are Checked for Network Service :

This is an important setting with out this setting you cannot access the Remote File.If you are trying to Acess the remote file You will get Acess denied.

For this You need to Go SqlserverConfiguration Manager.Go to sql server Logon tab .There select the this Account and give Your Username and Password.

Network Service

3)Enable Server Proxy Account :

you need to Enable the proxy connection setting

Proxy Connect Setting

Bulkinsert is the best approach when your Text files data having Comma seperated data with columns.The below line of Code illistrute how to work with remote files.
txt with delimeters

FROM '\\\Excelstore\TenK.txt'

select * from CSVTest

xp_CmdShell :When Using xp_cmdshell you need to execute this query in sql-server You need to run below extended stored procedures.

EXEC sp_configure 'show advanced options', 1
-- To update the currently configured value for advanced options.
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
-- To update the currently configured value for this feature.

declare @sql varchar(8000)
select @sql = 'bcp master.dbo.CSVTest -i
\\\data\50.txt -c, -T -S' + @@servername -C -T
eexec xp_cmdshell @sql

and In very olden days this is implemented through bcputility command in Dos Command Prompt


OpenRowset : With OpenRowset we can Access the Remote Excel files and dump the Data .

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=\\\\data\10kUAE.xls','select * from [sheet1$]')


Article by srirama
A Good advice from parent to a Child , Master to a Student , Scholar to an Ignorant is like a doctor prescribed pill it is bitter to take but when they take it will do all good for them --- Bhushan

Follow srirama or read 74 articles authored by srirama


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: