Solved

How do I read Excel files in Coldfusion

Posted on 2004-10-06
7
21,546 Views
Last Modified: 2013-12-20
After much searching I have decided to join EE so I might be able to solve my problem.

I got some code from my fellow developers to read a Excel file in Coldfusion but it no longer works in MX

<cfquery dbtype="dynamic" CONNECTSTRING="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=#Session.filepath#;DefaultDir=#Session.filepath#;" name="InputQuery">

Then I managed to find some COM object thing that is a Custom tag that suppose to do the same thing..{ReadXLS.cfm} but it also doesn't work

Is there anybody out there that can actually do this?

Custom Tag / CFFile / CFQuery .. I don't care.. I just need to get the excel file read into CF, after that I will split the data and validate it and write a text file out to do the rest of my process.. In a perfect world I would ask the end-users to actually save the Data Dumps as CSV but I can't and they will not do this... So I need to somehow read the DATA from the Excel file.

My next option is to just do it in PHP... but that would require a whole new system.. and I would rather find a solution to my current problem than re-write everything in PHP

Kind Regards
Adesso21
0
Comment
Question by:Adesso21
  • 2
  • 2
  • 2
7 Comments
 
LVL 21

Accepted Solution

by:
pinaldave earned 125 total points
ID: 12239850
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12244824
DNS-less connection is no longer supported in MX.

This site shows you some workarounds
[quote]
With the introduction of JDBC drivers in ColdFusion MX, dynamic data source connections (also referred to as DSN-less connections) are no longer supported. This is because ColdFusion MX uses the data source services provided by the underlying J2EE server. It is possible to closely simulate this functionality however.
[\quote]

Dynamic data sources in ColdFusion MX
http://www.macromedia.com/support/coldfusion/ts/documents/dynamic_dsn_cfmx.htm
0
 
LVL 9

Expert Comment

by:Jerry_Pang
ID: 12244854
<cfquery dbtype="dynamic" CONNECTSTRING="Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=#Session.filepath#;DefaultDir=#Session.filepath#;" name="InputQuery">

is not supported anymore.

Either create an odbc and setup the administration area for the ODBC,
then change your <cfquery> to sumthing like
<cfquery name="InputQuery" datasource="Your_dnsname">

or use the workaround given by macromedia and modify your all queries
 like this
<cfquery name="test" datasource="dynamicMDB">
   SELECT FirstName
   FROM Employees
   IN 'C:\mysite\db\cfsnippets.mdb'
</cfquery

i would personally go for step one.
Setup the ODBC then modify the <cfquery tags>
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 2

Author Comment

by:Adesso21
ID: 12246502
DNS-less connections is no longer supported...... NOT

http://www.emerle.net/programming/display.cfm/t/cfx_excel2query has a very nice solution to make this all possible..

The requirment was reading in files on a server in a on-going basis.. and this nice little CFX tag does just that.. no more worries as my problem has been solve thanks to pinaldave. I knew it was not all that tuff..

Thanks for the link and time guys.. My work here is done.

Kind Regards

Adesso21
0
 
LVL 2

Author Comment

by:Adesso21
ID: 12246540
PS > www.cfcomet.com no longer exist...
0
 
LVL 21

Expert Comment

by:pinaldave
ID: 12248706
glad to help you and have a good day,
Regards,
---Pinal
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
Now that Expression Web 4.0 (http://www.microsoft.com/expression/products/Upgrade.aspx) is free if you buy or have the full version of Expression Web 3.0, now is the best time to  migrate from FrontPage to Expression Web (http://www.frontpage-to-exp…
The purpose of this video is to demonstrate how to reset a WordPress password if you are locked out and cannot reset the password. A typical use would be if you cannot access the email to which WordPress would send the password recovery email to…
The purpose of this video is to demonstrate how to integrate Mailchimp with WordPress, by placing a Mailchimp signup form on a WordPress Page or Post. This will be demonstrated using a Windows 8 PC. Mailchimp will be used. Log into your Mailchi…

947 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now