Upload excel data into SQL DB using CFQUERY

[Tpaul]

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

[Guest]

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

[Adam Cameron.]

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

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

--

Adam

[Tpaul]

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

[Adam Cameron.]

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

[-__cfSearching__-]

Are your ODBC drivers all up to date?  Like I mean the

Windows ones, not the CF ones.

I am pretty sure you would need to update your drivers.  IIRC the old Jet 4 drivers do not support Office 2007 format.

Message was edited by: -==cfSearching==-

[ilssac]

P.S. that 500 error could be another layer of obscufication masking the real errors, IF you have not turned on the Enable Robust Exception Information option in the ColdFusion administrator of ColdFusion.

[Tpaul]

ODBC drivers all up to date and where can I find *Enable Robust Exception Information*?

I don't see it under Debugging &logging Settings

Thanks

[ilssac]

Debugging & Logging > Debug Output Settings

[Steve_Rittler]

you'll also want to turn off friendly error messages in your browser (instructions vary depending on which one you're using)

[julia1985dd]

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

[-__cfSearching__-]

Did you update your drivers?

[Tpaul]

It worked with the following.

1) Used 'EXCEL 12.0' in my query

2) Updated the drives for Excel 2007

Thanks