uploading excel file through Server into Access DB

uploading excel file through Server into Access DB

Page 1 of 1

1 Replies - 11566 Views - Last Post: 17 April 2007 - 01:46 PM Rate Topic: -----

#1 jessehanson2001  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 29-March 07

uploading excel file through Server into Access DB

Posted 29 March 2007 - 06:49 PM

I am trying to upload an excel files through a server and into a Access DB, i get an error msg on my site. I have done numerous research and could not find a solution to this program without purchasing a software to upload excel files by coldfusion.



<cfinclude template="adheader.cfm">


<!--- Accept the actual file upload --->
<!--- The file will be placed into the same folder as this ColdFusion page --->
<cffile destination="#getDirectoryFromPath(getBaseTemplatePath())#"
action="upload" nameConflict="makeunique" filefield="fileName" accept="application/vnd.ms-excel">
<!--- Output information about the status of the upload --->
<cfoutput>

File Upload was Successful! Information about the file is detailed below
</p>
<table>
<caption><b>File Information</b></caption>
<tr valign=”top”>
<th align=”left”>File Name:</th>
<td>#cffile.ServerDirectory#\#cffile.ServerFile#</td>
<th align=”left”>Content Type:</th><td>#cffile.ContentType#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Content SubType:</th>
<td>#cffile.ContentSubType#</td>
<th align=”left”>Client Path:</th>
<td>#cffile.ClientDirectory#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Client File:</th>
<td>#cffile.ClientFile#</td>
<th align=”left”>Client FileName:</th>
<td>#cffile.ClientFileName#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Client FileExt:</th>
<td>#cffile.ClientFileExt#</td>
<th align=”left”>Server Path:</th>

<td>#cffile.ServerDirectory#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Server File:</th>
<td>#cffile.ServerFile#</td>
<th align=”left”>Server FileName:</th>
<td>#cffile.ServerFileName#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Server FileExt:</th>
<td align=”left”>#cffile.ServerFileExt#</td>
<th align=”left”>Attempted ServerFile:</th>
<td>#cffile.AttemptedServerFile#</td>
</tr>
<tr valign=”top”>
<th align=”left”>File Existed?</th>
<td>#cffile.FileExisted#</td>
<th align=”left”>File Was Saved?</th>
<td>#cffile.FileWasSaved#</td>
</tr>
<tr valign=”top”>
<th align=”left”>File Was Overwritten?</th>
<td>#cffile.FileWasOverWritten#</td>
<th align=”left”>File Was Appended?</th>
<td>#cffile.FileWasAppended#</td>
</tr>
<tr valign=”top”>
<th align=”left”>File Was Renamed?</th>
<td>#cffile.FileWasRenamed#</td>
<th align=”left”>File Size:</th>
<td>#cffile.Filesize#</td></th>
</tr>
<tr valign=”top”>
<th align=”left”>Old File Size:</th>
<td>#cffile.OldFileSize#</td>
<th align=”left”>Date Last Accessed:</th>
<td>#dateFormat(cffile.DateLastAccessed,'dd mmm yyyy')#</td>
</tr>
<tr valign=”top”>
<th align=”left”>Date/Time Created:</th>
<td>
#dateFormat(cffile.TimeCreated,'dd mmm yyyy')#
#timeformat(cffile.TimeCreated,'hh:mm:ss')#
</td>
<th align=”left”>Date/Time Modified:</th>
<td>
#dateFormat(cffile.TimeLastModified,'dd mmm yyyy')#
#timeformat(cffile.TimeLastModified,'hh:mm:ss')#
</td>
</tr>
</table>
</cfoutput>

Your upload is successful.

<cfoutput>#file.ServerFile#</cfoutput>


<!--- <cfset xlsfile = "#file.serverfile#">

<cfquery datasource="12502Group1" name="XLSData">
INSERT INTO Members('Microsoft.Jet.OLEDB.4.0', 'Excel;Database=#xlsfile#;HDR=YES', 'SELECT * FROM Sheet1$')
</cfquery> --->

<cfquery datasource="12502Group1" name="doit">

INSERT INTO Members (MemLName, MemFName, MemMName, MemOrgName, MemEffDate, MemUFDues, MemEmpCat)
SELECT MemLName, MemFName, MemMName, MemOrgName, MemEffDate, MemUFDues, MemEmpCat
FROM #cffile.ServerDirectory#\#file.ServerFile#.[Sheet1$]

</cfquery>

<cfinclude template="adfooter.cfm">









This is the error on the site: Any help would be appreciated thanks!



The MIME type of the uploaded file "application/octet-stream" was not accepted by the server.

Only files of type "application/vnd.ms-excel" can be uploaded.
Verify that you are uploading a file of the appropriate type.

The error occurred in T:\Internet\Users\12502Group1\Admin\upload_action.cfm: line 7

5 : <!--- The file will be placed into the same folder as this ColdFusion page --->
6 : <cffile destination="#getDirectoryFromPath(getBaseTemplatePath())#"
7 : action="upload" nameConflict="makeunique" filefield="fileName" accept="application/vnd.ms-excel">
8 : <!--- Output information about the status of the upload --->
9 : <cfoutput>

Is This A Good Question/Topic? 0
  • +

Replies To: uploading excel file through Server into Access DB

#2 ajwsurfer  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 21
  • View blog
  • Posts: 373
  • Joined: 24-October 06

Re: uploading excel file through Server into Access DB

Posted 17 April 2007 - 01:46 PM

It is obvious that this line is giving you trouble.
[action="upload" nameConflict="makeunique" filefield="fileName" accept="application/vnd.ms-excel">]
I would try a bunch of differnent parameters here. If that does not work read on.



Uploading a file to the server, and importing the data from that file into a database are two completely different things.

I would suggest you take this in a two step process.

First get the file to upload to a temp directory. There is a file type in the forms. So you can upload a file using a few simple lines.
<form action ="uploadAction.cfm" encltype="multipar/form-data" method="post">
...
<input type="file" name="photo" size="45"><br />
...
<input type="submit" value="upload">
</form>



So in the uploadAction.cfm file you just need the line

<cffile action="upload" filefield="photo" destination="c:\CFusionMX\wwwroot\members\">




(Adam Phillip Churvis, Hal Helms, Charles Arehart & David Chruvis, Page 713 to 719, "ColdFusion MX Bible" Wiley Publishing 2003)


So if you can get the file to exist on the server, you have the second task of building a routine that will import the fields to the database. You might be able to test this operation without ColdFusion to make sure it acutaly works first. I think I would write a page that would display data from both databases, or physicaly place the file on the server and open a page that just imports the data into the database, to make sure both datasources are actualy working. You will also need write permission to the database you are persisting to. The ColdFusion Administator page is also going to play a pretty big part, as far as making sure those datasources are working properly.

Once you can varify that this is working and the file exists on the server, the rest of the code can be written and tested. So there are plenty of things to check, and plenty of things that could go wrong. :blink:

This post has been edited by ajwsurfer: 17 April 2007 - 01:59 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1