Click here to Skip to main content
12,456,454 members (56,871 online)
Rate this:
 
Please Sign up or sign in to vote.
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 3:26am
Updated 2-Apr-12 5:31am
v3
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
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
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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.160826.1 | Last Updated 2 Apr 2012
Copyright © CodeProject, 1999-2016
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