Need help 1

[Firsttimeprogrammer]

I just started working with Coldfusion and would like some help.

I need to create a form which allow a user to enter a path to the directory where a excel file exists. After which the data will need to be upload into an oracle database.

Can anyone give me some help as to how to do this.

Thanks in advance

 

[amillia]

I just completed a project like this. I allow the user to upload a spreadsheet from their computer to the webserver using <cffile> then I have a dsn to the spreadsheet and one to a SQL database. Then I run a select query from the spreadsheet. Then I loop through the select query with the update query to sql database.

 

[Firsttimeprogrammer]

Could you give a bit more details. As I have never worked with coldfusion before.

 

[amillia]

This is the form page minus the HTML:

<cfform action="upload_process.cfm?reportID=#url.reportID#" method="post" enablecab="yes" name="upload" enctype="multipart/form-data">
<input type="file" name="fileupload" size="75">
<input type="submit" name="submit" value="Submit">
</cfform>

This is the process page:

<cffile action="upload" filefield="fileupload"
destination="D:\Coldfusionsites" nameconflict="overwrite">
<!--- We will do a cftry and cfcatch in order to catch it when a user trys to upload the wrong report or there is an error in the report --->
<cftry>

<cfquery name="getExcel" datasource="ExcelData">
Select WUC from [sheet1$]
in 'D:\Coldfusionsites\test.xls'"Excel 8.0;IMEX=1;"
</cfquery>
<CFLOOP query="getExcel">
<cfquery name="addrecords" datasource="#application.dsn#">
Insert into defectsRepairs(WUC)
Values (
<CFQUERYPARAM CFSQLTYPE="cf_sql_varchar" value="#getExcel.WUC#">)
</CFQUERY>
</CFLOOP>
<!--- the spreadsheet does not upload the associated rptid so now we do that with an update query
--->
<cfquery name="updateDefects" datasource="#application.dsn#">
Update DefectsRepairs Set ReportID = #url.reportID#
WHERE (((DefectsRepairs.ReportID)=0)) OR (((DefectsRepairs.ReportID) Is Null));
</cfquery>
<!---then redirect the page to the success page--->
<cfif isdefined("form.fileupload") and #form.fileupload# neq "">
<cflocation url="Successful.cfm?reportID=#url.reportID#"></cfif>
<cfcatch type="any">
<font color="#FF0000" face="Verdana, Arial, Helvetica, sans-serif">I am sorry
you have tried to update the database with the wrong spreadsheet type.<br>
<br>
Please make sure you are updating a NAC with the correct column headings
and then try again.</font>
<a href="upload.cfm?reportID=<cfoutput>#url.reportID#</cfoutput>"><font color="#990033">Back</font></a></font>

</cfcatch>
</cftry>