logo
Welcome Guest! To enable all features please Login. New Registrations are disabled.

Notification

Icon
Error

Options
Go to last post Go to first unread
bart  
#1 Posted : Monday, September 19, 2011 1:55:14 PM(UTC)
bart

Rank: Administration

Reputation:

Groups: Administration
Joined: 10/8/2008(UTC)
Posts: 188
Man

Was thanked: 1 time(s) in 1 post(s)
If you want to create an automatic Excel import in SQL you will run into a couple of problems.

The first one is, the default excel import depends on the excel installation on the client side. but in an automatic import the server is the client side and here Excel isn't installed.
Lucky MS comes with a tool for office data connection and it's for free.
You still have to install this on your server, so you need RDP with admin rights.

Here you can download the latest version of this tool:
http://www.microsoft.com...en/details.aspx?id=13255

after you installed this tool you have to make sure the sql user which is running the import has rights to: "setupadmin" and "sysadmin", both global rights and not db depended.

Now we have to change some settings to sql to make the import possible,
after the import you can change them back, so no worry.

If you have sql express installed, you first have to enable the sql advanced options. and it is just as easy as typing this:
Code:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

After enabling this options, you have to enable "Ad Hoc Distributed Queries" and you can to it like this
Code:
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE

Now you have to enable rights for The MS ACE eninge and we can do it like this:
Code:
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1


After we do this step, we have to add a linkserver to link to the actual excel file. we can do it like this, where "D:\test.xlsx" is the local path of the excel file, "ExcelSource" is the name af the linked server and can be filled in the way you like.
Code:
EXEC sp_addlinkedserver 'ExcelSource',
   'ACE 12.0',
   'Microsoft.ACE.OLEDB.12.0',
   'D:\test.xlsx',
   NULL,
   'Excel 12.0'
GO

we need GO here, else the complete query will fail.

You can whatch your added linked servers using this command:
Code:
EXEC sp_linkedservers


Now here is the complete sql query I used:
"10523063" is the Excel Tab, normally it's called "Sheet1" or something.
You will see, after my query I drop the linked server and change all the settings back.
I'm not sure if I need to change back the settings, but I'm not totally aware of the consequences.
I delete the linked server so I can use the same query again without already having the linked servers.
Code:
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
EXEC sp_addlinkedserver 'ExcelSource',
   'ACE 12.0',
   'Microsoft.ACE.OLEDB.12.0',
   'D:\test.xlsx',
   NULL,
   'Excel 12.0'
GO
SELECT *
INTO #temptable
FROM OPENROWSET 
('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=D:\test.xlsx;HDR=YES','select * from [10523063$]') AS A
exec sp_dropserver 'ExcelSource'
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 0
EXEC sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 0
EXEC sp_configure 'Ad Hoc Distributed Queries', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE
/*here goes you own import code*/
select *
from #temptable
/*here ends you own code*/
drop table #temptable


Between my command, you can write your own import/update or whatever code.

Typing this it looks very easy, but finding out was a hell of a job,.

If you have any questions or suggestions, please let me know.

Edited by user Friday, July 8, 2016 2:24:02 PM(UTC)  | Reason: Not specified

Wanna join the discussion?! Login to your Sienn Forum forum account. New Registrations are disabled.

Users browsing this topic
Forum Jump  
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.