Click here to Skip to main content
15,867,568 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
How to import an Excel file 2007 into SQL Server 2005 using sql query.

SQL
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\Test.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')

when i execute the above query it select the table lik below

MyID MyDesc
1 jasmine
2 rose
3 lilly
4 lotus

but i used to insert the query , getting error lik:

SQL
insert into OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 8.0;Database=C:\Test.xlsx;HDR=YES',
'SELECT * FROM [Sheet1$]')


Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ')'.
Posted
Updated 2-Apr-12 5:31am
v3

 
Share this answer
 
Comments
priya from Madras 2-Apr-12 9:32am    
Hi Rohan actually i want to import direct not paste method .i am trying some method but i ve error .so cant find the solution .

Check the link ,i need lik this :

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49926
There is a microsoft knowledge base article that lays out all the ways this is possible.
http://support.microsoft.com/kb/321686[^]

I think using OPENROWSET or OPENDATASOURCE will be the easiest way:
SQL
SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

See OPENROWSET documentation, with examples lower down the page.
http://www.sqlservercentral.com/Forums/Topic604617-1292-1.aspx[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900