need to import Excel data into Mysql database using coldfusion script. How can i do it. Is there any utility or any script can do it. Thanks in advance.
-
-
Welcome to TSDN!
You can use the Excel file as a datasource. Loop over the rows and insert them into the database.
What format is the data in? -
The datas are Dotted IP and its corresponding decimal value...And also The sheet included two other columns like country code and country name..Originally posted by acoder
For an Eg:
Dot ip Dec Coucode Couname
19.2.2.2 11111111 Ja JapanComment
-
Set up the Excel file as a data source. Use cfquery to get the data and then loop over it as you would with a normal query and insert each row into the MySQL database. If you get stuck, post your code.Comment
-
How can I make an Email Textbox as a readonly using dojo widget?Comment
-
That's a different problem. Did you manage to solve the original problem?Originally posted by sukumar 123Comment
-
copy and past the codes below save it as read.cfm:
[CODE=cfm]<!--- default values --->
<cfparam name="form.Inpu tExcelFile" default="">
<cfparam name="form.Star tCol" default="A">
<cfparam name="form.EndC ol" default="0">
<cfparam name="form.Star tRow" default="1">
<cfparam name="form.EndR ow" default="0">
<cfparam name="form.Shee t" default="Sheet1 ">
<cfparam name="form.Head erRow" default="">
<cfparam name="form.Form ula" default="proces s">
<cfparam name="form.Deci malDigit" default="2">
<cfparam name="form.Proc essMerged" default="true">
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>CFX_Exce l Example</title>
<style type="text/css">
.textfield { font-family: "MS Sans Serif"; font-size: 9pt; border: 1px solid #cccccc; }
.button { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; font-weight: bold; color: #FFFFFF; background-color: #000000; cursor: hand}
.tablestandard { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; }
</style>
</head>
<body>
<cfoutput>
<h3>CFX_Excel Example</h3>
<h4>Read Operation - Convert MS Excel File To ColdFusion Query</h4>
<hr size=1>
<table width="100%" cellpadding="2" cellspacing="2" border="0" class="tablesta ndard">
<cfform action="example _read.cfm" method="POST" enctype="multip art/form-data">
<tr>
<td nowrap valign="top">Up load Excel file to read:</td>
<td width="100%" valign="top">
<input type="File" name="InputExce lFile" size="40" class="textfiel d">
</td>
</tr>
<tr>
<td nowrap valign="top">Sh eet name or index##:</td>
<td width="100%" valign="top">
<cfinput type="Text" class="textfiel d" name="Sheet" value="#form.Sh eet#" size="8">
</td>
</tr>
<tr>
<td nowrap valign="top">Co lumn to process:</td>
<td width="100%" valign="top">
Start: <cfinput type="Text" class="textfiel d" name="StartCol" value="#form.St artCol#" size="8">
End: <cfinput type="Text" class="textfiel d" name="EndCol" value="#form.En dCol#" size="8">
(type '0' in the end attribute to process all non-empty/edited columns)
</td>
</tr>
<tr>
<td nowrap valign="top">Ro w to process:</td>
<td width="100%" valign="top">
Start: <cfinput type="Text" class="textfiel d" name="StartRow" value="#form.St artRow#" range="1," size="8">
End: <cfinput type="Text" class="textfiel d" name="EndRow" value="#form.En dRow#" size="8">
(type '0' in the end attribute to process all non-empty/edited rows)
</td>
</tr>
<tr>
<td nowrap valign="top">He ader row:</td>
<td width="100%" valign="top">
<cfinput type="Text" class="textfiel d" name="HeaderRow " value="#form.He aderRow#" validate="integ er" range="0," size="4"><br>
(Row containing column names, if any)
</td>
</tr>
<tr>
<td nowrap valign="top">Nu mber of decimal digits:</td>
<td width="100%" valign="top">
<cfinput type="Text" class="textfiel d" name="DecimalDi git" value="#form.De cimalDigit#" validate="integ er" range="0," size="4">
</td>
</tr>
<tr>
<td valign="top">Fo rmula:</td>
<td valign="top">
<input type="Radio" name="Formula" value="process" #iif(form.Formu la is "process",de("c hecked"),de("") )#>Process<br>
<input type="Radio" name="Formula" value="show" #iif(form.Formu la is "show",de("chec ked"),de(""))#> Show<br>
</td>
</tr>
<tr>
<td valign="top">Pr ocess merged cells:</td>
<td valign="top">
<input type="Radio" name="ProcessMe rged" value="true" #iif(form.Proce ssMerged is "true",de("chec ked"),de(""))#> True<br>
<input type="Radio" name="ProcessMe rged" value="false" #iif(form.Proce ssMerged is "false",de("che cked"),de(""))# >False<br>
</td>
</tr>
<tr>
<td colspan="2">
<input type="Submit" value="Convert Excel To CF Query Now" class="button"> <br>
<br>
<font size="1">
<a href="example_w rite.cfm">Click here to see the CFX_Excel write example</a>
</font>
</td>
</tr>
</cfform>
</table>
<cfif form.InputExcel File neq "">
<!--- read operation --->
<hr size="1">
<!--- define temp excel --->
<cfset strDir=GetDirec toryFromPath(Ex pandPath("*.*") ) & "/temp">
<cfset strInExcel=strD ir>
<!--- upload image --->
<cffile action="Upload"
filefield="Inpu tExcelFile"
destination="#s trInExcel#"
nameconflict="M AKEUNIQUE"
mode="757">
<cfset prodThumbDir=fi le.ServerDirect ory>
<cfset prodThumbFile=f ile.ServerFile>
<cfset prodThumbExt=fi le.serverfileex t>
<cfif (prodThumbExt neq "xls")>
CFX_Excel accepts .xls file only
<cfelse>
<!--- read excel --->
<cftry>
<cfset tickBegin = GetTickCount()>
<cfx_excel action="read"
File="#prodThum bDir#/#prodThumbFile# "
Sheet="#form.Sh eet#"
Result="qryResu lt"
Formula="#form. Formula#"
StartRow="#form .StartRow#"
EndRow="#form.E ndRow#"
StartCol="#form .StartCol#"
EndCol="#form.E ndCol#"
HeaderRow="#for m.HeaderRow#"
ProcessMerged=" #form.ProcessMe rged#"
DecimalDigit="# form.DecimalDig it#">
<cfset tickEnd = GetTickCount()>
<cfset loopTime = tickEnd - tickBegin>
<!--- show result query --->
<h3>Conversio n Result:</h3>
<cfdump var="#qryResult #">
<hr size="2">
<h3>Performance </h3>
Convert time was: #loopTime# milliseconds<br >
Query info: #qryResult.reco rdcount#<br>
<cfif looptime gt 0>
Approx. speed: #numberformat(q ryResult.record count/(looptime/1000))# rows/second
</cfif>
<br><br>
<font size="-1">
(Please note that our live example is running in a very busy shared hosting environment)
</font>
<cfcatch type="Any">
<h3>There was a problem while reading '#prodThumbFile #'</h3>
Check the file, columns to process, rows to process and other properties,<br>
to make sure all source cells can be converted to recordset.
</cfcatch>
</cftry>
</cfif>
<!--- remove temp excel --->
<cftry>
<cffile action="DELETE" file="#prodThum bDir#/#prodThumbFile# ">
<cfcatch type="Any"></cfcatch>
</cftry>
</cfif>
</cfoutput>
</body>
</html>
[/CODE]
The same with the codes below and save it as viewexcel.cfm:
[CODE=cfm]<cfparam name="url.Excel File" default="">
<cfif listlast(url.Ex celFile,".") neq "xls">
<cfabort>
</cfif>
<cfset strDir=GetDirec toryFromPath(Ex pandPath("*.*") )>
<cfif not fileexists("#st rDir#temp/#url.ExcelFile# ")>
<cfabort>
</cfif>
<cfheader name="Content-Disposition" value="inline; filename=#url.E xcelFile#">
<cfcontent type="applicati on/unknown" file="#strDir#t emp/#url.ExcelFile# " deletefile="yes ">[/CODE]
You can Convert MS Excel File To ColdFusion Query.Comment
-
It makes use of a custom tag to do the job. Did the code contain any copyright information?Originally posted by cfmx2008Comment
-
No there is no Copyright. it's on the web for free. I used it befor so I thought it may be hany.Originally posted by acoderComment
-
OK, just making sure. Thanks.Originally posted by cfmx2008
It does use cfx_excel which is a custom tag, though. So it wouldn't work 'out of the box'.Comment
Comment