how can i import excel data into mysql

[sukumar 123]

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.

[acoder]

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?

[sukumar 123]

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..
For an Eg:
Dot ip Dec Coucode Couname
19.2.2.2 11111111 Ja Japan

[acoder]

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.

[sukumar 123]

How can I make an Email Textbox as a readonly using dojo widget?

[acoder]

How can I make an Email Textbox as a readonly using dojo widget?

That's a different problem. Did you manage to solve the original problem?

[cfmx2008]

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.

[acoder]

copy and past the codes below save it as read.cfm:
...
You can Convert MS Excel File To ColdFusion Query.

It makes use of a custom tag to do the job. Did the code contain any copyright information?

[cfmx2008]

It makes use of a custom tag to do the job. Did the code contain any copyright information?

No there is no Copyright. it's on the web for free. I used it befor so I thought it may be hany.

[acoder]

No there is no Copyright. it's on the web for free. I used it befor so I thought it may be hany.

OK, just making sure. Thanks.

It does use cfx_excel which is a custom tag, though. So it wouldn't work 'out of the box'.