Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
How to import an Excel file 2007 into SQL Server 2005 using sql query.
 
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:
 
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 2-Apr-12 4:26am
Edited 2-Apr-12 6:31am
v3
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
priya from Madras at 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
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

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:
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[^]
  Permalink  

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



Advertise | Privacy | Mobile
Web01 | 2.8.150327.1 | Last Updated 2 Apr 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100