Locked Excel Files

Hey all ... quick question. I know this has been written about before, but I need a little more clarification.

I recently switched from a Cold Fusion 5 to MX. I had some functionality for uploading Excel files and reading them into the database. I was using the CFUSION_DISABLE_DBCONNECTIONS function to unlock my Excel files, but apparently that is no longer supported in MX.

Unlocking the file is a major problem as I use one file (and datasource) as a focal point that I continually overwrite with new info and re-run the process using the same datasource.

I have heard that you can unlock the Excel file with a bogus query but I was unable to get results with that. I also have a script that processes every error and forwards it to a "human readable" explanation page and emails me a copy of the error, so I was wondering if I could get Cold Fusion to skip passed the error and keep going on with the uploading and overwriting of the file in the rest of the Cold Fusion page.

Besdides all of that, I am now getting an error when I read the file with the following query:

<cfquery datasource="examExcel" name="getDataSheet">
SELECT *
FROM `Sheet1$`
</CFQUERY>

It continually tells me that it cannot locate 'Sheet$' even though the datasource name is correct, and it does correctly point to the Excel file, which I know (after repeated checking) has a "Sheet1".

Any help regarding skipping past errors, unlocking Excel files, or the error listed above would be MOST APPRECIATED!

Many thanks,

Fletch

I figured out some of my problem and thought I would post it for those who may not be familiar.

I needed to run a bogus query to clear an Excel file but did not want the rest of my script to pooch, so I used the <cftry> and <cfcatch> tags. It looks like this:

<cftry>
<cfquery datasource="examExcel" name="FreeFileQuery">
SELECT nothing
FROM `Sheet1$`
</cfquery>
<cfcatch type="DatabaseQuery Complete</font><br>
</cfcatch>
</cftry>

The <cftry> tag lets the query error without stopping the page to announce the error, and the >cfcatch> tag lets me kno that the process happened the way I wanted it to. NOTE: Every <cftry> tag requires a <cfcatch> tag to be present.

Hope this can help someone.

Fletch

Some more information I have discovered from the web:

The two most common ways to unlock an Excel file in Cold Fusion have been to use the CFUSION_DISABLE_DBCONNECTIONS function like so:

<CFSET CFUSION_DISABLE_DBCONNECTIONS("excel", 1)>

... or to run a bogus query on the datasource that will intentionally error like in my last message with the <CFTRY> example.

The function above does not seem to work in Cold Fusion MX. Also, I have found that running a bogus query may not always release the file. I read that someone had investigated a problem where running the bogus query did not work and discovered that it was the Jet Database engine that was keeping the connection open.

Since I am on a shared host and do not have access to the settings, it looks as though I am going to have to use another solution. Maybe converting the files to tab-delimited and reading them into an array.

I am still open for responses if anyone has any ideas.

Thanks,

Fletch