9 Replies Latest reply on Jun 28, 2010 9:36 AM by WebolutionDesigns

Reading Excel file from another site

WebolutionDesigns Jun 18, 2010 3:00 PM

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

1. Re: Reading Excel file from another site

Dan Bracuk Jun 19, 2010 3:50 PM (in response to WebolutionDesigns)

Use cfspreadsheet to read it from it's current location.
Like Show 0 Likes(0)

Actions
2. Re: Reading Excel file from another site

WebolutionDesigns Jun 19, 2010 9:50 PM (in response to Dan Bracuk)

Unfortunately, this client is not in my hosting environment, which is CF9. They are on an inferior host which has them on CF6 (yuck!).
Like Show 0 Likes(0)

Actions
3. Re: Reading Excel file from another site

Dan Bracuk Jun 20, 2010 4:56 AM (in response to WebolutionDesigns)

Find Ben Nadel's poi for getting coldfusion to play with excel. I know it works on v7. It might work on v6 also.
Like Show 0 Likes(0)

Actions
4. Re: Reading Excel file from another site

WebolutionDesigns Jun 22, 2010 6:45 AM (in response to Dan Bracuk)

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.
Like Show 0 Likes(0)

Actions
5. Re: Reading Excel file from another site

tclaremont Jun 22, 2010 9:58 AM (in response to WebolutionDesigns)

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="">
Like Show 0 Likes(0)

Actions
6. Re: Reading Excel file from another site

ilssac Jun 22, 2010 9:45 AM (in response to WebolutionDesigns)

<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.
Like Show 0 Likes(0)

Actions
7. Re: Reading Excel file from another site

Dan Bracuk Jun 22, 2010 12:49 PM (in response to WebolutionDesigns)

What sort of difficulties are you having? File not found? Not enough permission?
Like Show 0 Likes(0)

Actions
8. Re: Reading Excel file from another site

WebolutionDesigns Jun 22, 2010 1:29 PM (in response to Dan Bracuk)

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?
Like Show 0 Likes(0)

Actions
9. Re: Reading Excel file from another site

WebolutionDesigns Jun 28, 2010 9:36 AM (in response to WebolutionDesigns)

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.
Like Show 0 Likes(0)

Actions