Posted: 23 September 2009 at 8:22pm | IP Logged

I'm trying to figure out how to import an excel spreadsheet into a table within a SQL Server database.  Searching on the internet, I found this:

select * 
into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=D:\testing.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]')

I just slapped this code into a cf page, changed the 
table name, and the location and name of the spreadsheet, 
and tried it out.

It bombs at the "From" clause. "Incorrect syntax near the
keyword 'from'.  Here's my statement:



<cfquery name="insert" datasource="jeffy">
insert into jefftable from openrowset('Microsoft.Jet.OLEDB.4.0', 
    'Excel 8.0;Database=C:\dothis.xls;HDR=YES', 
    'SELECT * FROM [Sheet1$]');

</cfquery>

I have no clue what I'm doing, so any help or suggestions 
will be appreciated.

Posted: 24 September 2009 at 2:04pm | IP Logged

Thanks PaulK,

But I can't seem to get around my previously mentioned error message regarding the "From" clause.  I did look at the link you suggested, but unfortunately it didn't tell me anything more than what I already know.  If you have any other ideas, I'd love to hear them.

Thanks again 

Posted: 04 October 2009 at 12:29pm | IP Logged

perhaps this might help you

http://www.bennadel.com/blog/472-Reading-Excel-Files-With-Co ldFusion-And-POI.htm