Known Participant

Question

Upload excel data into SQL DB using CFQUERY

Forum|Forum|15 years ago
October 5, 2010
3 replies
2185 views

I am using Excel 2007 and SQL 2005, but the folliwng code is not working and I am getting the message which I have set. Could you please help me out what changes to be made for my <cfquery>Tag?

Cftry>
<CfQuery name= "MyXLQry" datasource="myXL">
Select * from [FirstTab$]
IN '#thisDirectory#\#newName#' 'EXCEL 5.0;'
</cfquery>
<cfcatch type="Database">
<cfset message="This is not the correct Excel file as the FirstTab is not present.">
<cflocation url="dsp_message.cfm?message=#message#" addtoken="No">
</cfcatch>
</cftry>

I have set up "myXL" as ODBC Socket in CF Admin and using CF 7.0 (don't want to CFCs)

Thanks

Advanced techniques

This topic has been closed for replies.

Participant

I have the same issue as you......

-__cfSearching__-

Inspiring

Did you update your drivers?

Inspiring

Take the try/catch off and tell us the actual error...

To fix something, first one needs to know what the problem is.

--

Adam

TpaulAuthor

Known Participant

Took out all the <cftry> and <cfcatch>.

Getting HTTP 500 Internal Server rerror

Most likely causes:

The website is under maintenance.
The website has a programming error.

But the code works well with .XLS and getting error for .xlsx

is there any problem with OBDC socket or dsn?

Thanks

Inspiring

Dunno mate, sorry.

Are your ODBC drivers all up to date? Like I mean the Windows ones, not the CF ones.

Have you looked @ using POI instead of ODBC?

--

Adam

A

Anonymous

You can try this way...convert it to array and then import to db. Refer http://www.cflib.org/udf/CSVtoArray