Creating ODBC socket connection to Excel

Report · Mar 24, 2008

Hello, helpful people!

I installed CF8 without documentation. This caused the known issue of not doing a full install of ODBC drivers. I was actually doing fine (I could, for some reason, use MySQL drivers without an issue) until I suddenly wanted an Excel driver.

I searched online, found my answer, and began following the instructions:
http://www.adobe.com/go/kb402637

Unfortunately, I can't follow instruction #6, "Run instODBC.cfm in a browser using the location you stored it to." Every time I run it, I get a "The current user is not authorized to invoke this method" error - even when I'm logged in as admin.

I've tried moving the installODBC.cfm file to a couple of locations (including within CFIDE/administrator) to no avail.

I need to create access to Excel in order to finish my code! Help?

Best wishes,
Cat

Report · Mar 24, 2008

You can use the approach in the page http://tutorial5.easycfm.com also for Excel files.

In the queries, you can use sheet names as the table name.

BUT I would suggest to create an MS Access file which may contains a direct links to your Excel file and use it as required.

Report · Mar 24, 2008

Thank you for your answer, Oguz! Unfortunately, it appears a DNS-less connection is not an option for CF8 - it was only for pre-MX CF instances. Unless there's some documentation I've missed?

The end result I need to happen is:
I've got several users who will eventually be maintaining the system I'm creating. They are accustomed to Excel, and would like to bulk-upload data from Excel to the program (which is based in MS SQL). I've simplified the Excel file so as not to confuse the users, and will need to process each record before I can push it to the database (error checking, cross-referencing the sheets, etc). This prevents me from using ye olde MS SQL import functionality.

Based on my research, it looked like the best option was to:
1. upload the file to a temp directory on the server, asking the user for various other bits of information on the upload form.
2. run a couple of queries in the file and do my processing/datachecking, returning validation errors if anything is wrong
3. if all is kosher, call the appropriate functions to create/save, etc from my cfcs using the data I've processed

Is there a way of doing this other than setting up the DNS in administrator? Am I making things more difficult than they need to be? It seemed like a straightforward solution, if only I could get the bug fix to run.

Best wishes,
Cat

Report · Mar 24, 2008

I think CF MX support also DSNless connection.

Here is a technote.

http://www.adobe.com/go/tn_18656

But again I do not suggest any of these methods.

Report · Mar 24, 2008

Step 2 of that tech note is essentially where I am stuck. I cannot create the data source.

You see, for earlier versions of CF8, the installer required you to install documentation in order for the data sources to be correctly installed. This was fixed in the 8.0.1 installer, but I'm one of the unfortunates that installed using the earlier version of the installer, and the server was moved to production before the patched installer came out.

So, I'm trying to use the tech note I listed in order to fix the buggy install, which would allow me to create the data source.

If I attempt to create the data source without the patch, it gives me an error that the ODBC Connections were not installed. Based on my searching, my error is exactly what this patch is supposed to fix - if I could ever get it to run.

I'm trying to avoid bringing the entire production server down in order to do a re-install with the new installer, so am following the alternate directions in the tech note ( http://www.adobe.com/go/kb402637 ).

So, I hear what you're saying - and I agree. It's what I'm trying to do, in fact, if I could only get the fix installed.

Best wishes,
Cat

Report · Mar 24, 2008

Ok but I really wonder. Why don't you create a simple Access file which is linked to your Excel files?

If you have to use your Excel files as DS, this would be the easy way.

Report · Mar 24, 2008

How does that make things simpler? I'm certainly willing to learn! :)

Not that it makes much of a difference at this point - I can't make a connection to Access until I can run the fix, either.

Best wishes,
Cat

Report · Mar 24, 2008

quote:

They are accustomed to Excel, and would like to bulk-upload data from Excel to the program (which is based in MS SQL). I've simplified the Excel file so as not to confuse the users, and will need to process each record before I can push it to the database (error checking, cross-referencing the sheets, etc). This prevents me from using ye olde MS SQL import functionality.

Specificially what prevents this? Information could be imported into a temporary table, then validated and imported into your main tables.

Report · Mar 24, 2008

Cat,

The TechNote mentions a troubleshooting page. The comments by BKBK mention supplying the ColdFusion Administrator password when the script creates an instance of the Admin API. Trying adding your administrator password to the
installODBC.cfm file. Then run the script again.

Report · Mar 24, 2008

HA! Lovely. Thank you, that's exactly what I needed; I don't know how I missed it.

Don't you love how they make you go to a comment within a link in order to find out something which should be step #3 in the instructions? Not to mention calling it "InstODBC.cfm" rather than "InstallODBC.cfm" - the actual, correct file name.

(sorry if I'm sounding cranky; I'm annoyed that something so simple had me at a loss)

Best wishes,
Cat

Report · Mar 24, 2008

http://office.microsoft.com/en-us/access/HP010950951033.aspx#jmp_linktoXLdata

"Link to data in Excel

If you don't want to maintain a copy of the data in your Access database, you can instead link to the Excel worksheet. Linking lets you connect to data in Excel without importing it, so that you can view the data in Access. You can update the data in the worksheet when you are working in Excel and your changes will be shown when you view the linked table in Access.

When you link to a worksheet or a named range, Access creates a new table that is linked to the source cells. Linking has an advantage over importing — when you change data in the Excel worksheet, the linked table in Access updates to reflect those changes."

Report · Mar 24, 2008

Oguz and cfsearching,

Let's take an example. The software I'm developing is a way of viewing a variety of survey results. Any one of 12 people may be the one doing a bulk load of these results (as they don't want to directly link to the survey software quite yet, and they "fiddle" with the results a little to standardize them/clean them). Some of the results have over 256 characters (which, if not within the first 8 rows in a normal MS SQL import, will either error or cut off the data, even if the field is defined as text 16 field, or the data is imported to a temp table - yes, I've done this too many times. Can you tell?).

Each of these surveys will have different numbers of questions, different kinds of results, yadda yadda. You know, what you'd expect.

What I'm hearing you say is that I should make an Access database on the server that mimics my MS SQL database, then give them each an excel file for their local machine that links to the Access database, then have the Access database link to MS SQL?

That makes me nervous. These people are like any other non-technical people, and prone to mistakes. Mistakes that could break my front-end. Not to mention that maintaining this as development continues (as it does - unfortunately not in clean-cut phases) would be annoying. But, perhaps you mean something else?

My current (probably flawed) plan is to put the bulk of the work in my cfcs. This will make things slower, of course, but I'm thinking the benefits outweigh the speed issue. I feel that I have more control over the data this way, and can give user-friendly errors. I can kick back certain items to be fixed, but continue processing the rest of the file, for instance.

Now, if I've completely misunderstood what you've said, or am simply going about things the wrong way, please let me know. This is my first time developing this type of functionality, and I trust that y'all have more experience with it than me. So, please enlighten me!

Best wishes,
Cat

Report · Mar 24, 2008

graveyardfashions wrote:
What I'm hearing you say is that I should make an Access database on the server that mimics my MS SQL database, then give them each an excel file for their local machine that links to the Access database, then have the Access database link to MS SQL?

That makes me nervous.

Yes, that would not be my first suggestion.

Some of the results have over 256 characters (which, if not within the first 8 rows in a normal MS SQL import, will either error or cut off the data, even if the field is defined as text 16 field, or the data is imported to a temp table - yes, I've done this too many times. Can you tell?).

Specifically how did you import the data, and what version of MS SQL? My reason for asking is that ODBC also has limitations. So you may find that using an ODBC datasource yields similar results.

While no option is without flaws, java libraries like POI can sometimes offer better control over the import. There are also some pre-built cfc's for reading/writing excel files at riaforge.org. However, POI does not work with the new Office file format.

Report · Mar 24, 2008

quote:

Originally posted by: -==cfSearching==-
While no option is without flaws, java libraries like POI can sometimes offer better control over the import. There are also some pre-built cfc's for reading/writing excel files at riaforge.org. However, POI does not work with the new Office file format.

I was looking at POI as well, actually - http://www.bennadel.com/blog/624-ColdFusion-POIUtility-cfc-Updates-And-Bug-Fixes.htm seems promising. Do you have a favorite?

Thankfully, this customer is going to be using Office 2003 for a while - thank you very much for the tip about POI not working with the new office format.

So, if you were in my shoes, would you lean toward a POI cfc?

Best wishes,
Cat

Report · Mar 24, 2008

My opinions are somewhat biased.

Some time ago I had some data type problems when importing from excel files using things like odbc or opendatasource. Some of the issues I was unable to overcome. However, that may have been due in part to my lack of experience with it and things may also have improved since then. Depending on which version you are using, you may also have different options.

So POI would be my preference. I like the control it offers and I more familiar with it than other options like JExcel. I have not used the POIUtility.cfc beyond some basic testing, but I have heard good things about it.

Report · Mar 25, 2008

Thank you for your opinion! I will definitely give the POI solution a chance.

Best wishes,
Cat

Report · Mar 25, 2008

POI libraries are good for Excel files. But If you need to deal with other Office solutions like Word etc. POI would be not a perfect match.

I would also suggest to check Office 2003 XML features. You basically generates some XML files and save as .doc or .xls and they work as expected. :)

SpreadsheetML: http://openxmldeveloper.org/archive/category/1004.aspx

WorldML: http://openxmldeveloper.org/archive/category/1003.aspx

Report · Mar 25, 2008

Cat,

Just to add a clarification, obviously MS SQL's import tools would be the better option, if you can get around any import issues. If not, then POI, etcetera could be used to extract the data from the xls files. You must still import that data into your database.

Report · Mar 25, 2008

Cat,

Just to add a clarification, obviously MS SQL's import tools would be the better option, if you can get around any import issues. If not, then POI, etcetera could be used to extract the data from the xls files. You must still import that data into your database.

Creating ODBC socket connection to Excel

1 Correct answer