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. 
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
reconfigure
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.
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.
3)Enable Server Proxy Account :
you need to Enable the proxy connection 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.
BULK
INSERT CSVTest
FROM '\\192.168.2.50\Excelstore\TenK.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
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
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
declare @sql varchar(8000)
select @sql = 'bcp master.dbo.CSVTest -i
\\192.168.2.50\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=\\192.168.2.50\\data\10kUAE.xls','select * from [sheet1$]')
Attachments
Follow