Copy link to clipboard
Copied
I have a client who wants to put information on their site that is on a third party site (we have permission). Ideally, I would love to use RSS, but this site does not have that option. There are two methods the info is available...1) within a table on the page and 2) within an excel file via a link on the page.
I looked at using cfhttp to screen scrape, but do not want my code to break if they redesign their page. So I thought if I could somehow pull the Excel file and then read it, that would be perfect. I have read info from Excel files before, so I have no problems there (I hope), but where I need assistance is pulling the file from the site. I was thinking that if I could use some sort of combination of cffile and cfschedule to pull it and put it on my server, that would be ideal. Has anyone ever done anything like this before?
Thoughts? I am open to alternatives too.
Thanks,
Clay
Copy link to clipboard
Copied
Use cfspreadsheet to read it from it's current location.
Copy link to clipboard
Copied
Unfortunately, this client is not in my hosting environment, which is CF9. They are on an inferior host which has them on CF6 (yuck!).
Copy link to clipboard
Copied
Find Ben Nadel's poi for getting coldfusion to play with excel. I know it works on v7. It might work on v6 also.
Copy link to clipboard
Copied
Thanks. I have used his utility in the past and loved it. I am having difficulties in getting it to read a remote file though.
Copy link to clipboard
Copied
Can you programmically retrieve it using CFFTP?
Here is a simple example...
<cfftp connection="#session.sessionID#" action="getfile" remotefile="#form.myget#" localfile="#scriptdir#/stuff/#form.myget#" transfermode="binary"
timeout="30" retrycount="3" stoponerror="no" failifexists="no">
<cfif cfftp.errorcode is not 0>
<cfoutput>
Attempt to get #form.myget# failed.
#cfftp.errortext#
</cfoutput>
<cfelse>
Got <cfoutput>#form.myget#</cfoutput> OK.
</cfif>
<cfset form.myget="">
Copy link to clipboard
Copied
What sort of difficulties are you having? File not found? Not enough permission?
Copy link to clipboard
Copied
I was able to successfullly download the excel file using cfhttp. (Thanks, Ian).
Now I am attempting to use Ben Nadel's POI utility (which I have used before with success) to read it. I am running into the following error...
org.apache.poi.poifs.filesystem.POIFSFileSystem
What does this mean? Is it because their host is not on a high enough level?
Copy link to clipboard
Copied
<cfhttp....> can be used to get excel file nearly the same as it can get the HTML content.
The documentation should give you the details.
Copy link to clipboard
Copied
I was able to successfully retrieve and manipulate the data from the external page using cfhttp and parsing through the filecontent to get the info I needed. Thanks for your assistance.