SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Import Excel Data to a sql server table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

vimalraj
Starting Member

5 Posts

Posted - 09/11/2007 :  01:36:38  Show Profile  Reply with Quote
Hi,

I need to import data from more than 10 excels having the same format in to a single sql server table.

I tried to use

INSERT INTO MyTempTable
SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 11.0;Database=C:\Book1.xls', [Sheet1$])

but got the below error
Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

If DTS package is used then I am not sure how I can place 10 excels at a time so that they can be picked one by one and data is imported in to table.

Please help.

Thanks,
Vimalraj



SwePeso
Patron Saint of Lost Yaks

Sweden
30106 Posts

Posted - 09/11/2007 :  01:44:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
This is already discussed in detail here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926

And it seems you are using SQL Server 2005. Please use the Surface Area Configuration to allow AD HOC queries.



E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000