Tuesday, October 21, 2008

64 Bit Microsoft Windows ODBC Drivers (Excel)

If you've moved from a 32 bit environment to a 64 bit one and are using the Microsoft ODBC drivers for things like Excel (reading a worksheet) and Access, I'm afraid you're out of luck (this includes even text files etc).

I just found out the hard way that code written to pull data using the 'jdbc:odbc:Driver={Microsoft Excel Driver (*.xls)}' no longer works in Windows Server 64 bit edition. Microsoft apparently feels that they don't need to provide 64 bit drivers at all for Office connectivity. Which I guess means that we as developers and software designers should no longer support Office, which is OK by me.

I've hated working with Excel to import/export data to / from, and have had nothing but trouble either direct to the Database with OPENDATASOURCE and having to move uploaded files to the database server, or simply on the server through a jdbc odbc call.

I'm fine with Eliminating Excel as a valid import option - I just need to find / create a fast alternative that will do CSV or other formats (not using CF to do the heavy work with those files as they can be large). There is one ray of hope - in an opencsv format found here.


[Justice] said...

Check out Ben Nadel's POI Utility. Its super simple, and accesses the .xls file directly, rather than as a datasource.

Anonymous said...
This comment has been removed by a blog administrator.
Code Fusion, LLC (Kevin Penny) said...

Thanks Justice -
Yeah I had that one in mind - and after checking out a few others, I've implemented his solution. The only change I had to make to the Utility was to include an option to return the actual column headers from the excel document as the query columns headers in the returned recordset - vs. Column1, Column2 etc.

I think this should do the trick - thanks for the heads up. If anyone's interested in the modified code let me know. I commented to Ben Nadel about it but if anyone is looking for that functionality let me know and I can email the code to you.

Anonymous said...

this could be the solution to work with more effectiveness in Generic Viagra ambient, I say this because many times is too much keep a so heavy program, with this is more easy.

Buminda said...

What's the connection string used ? I tried installing AccessDatabaseEngine64 , but no luck.

CisAnubhav said...

coldfusion web services Says: I am using 64 bit Linux Ubuntu Platform. Can anybody tell me where to find POI Utility.