469,301 Members | 2,282 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,301 developers. It's quick & easy.

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.
Feb 18 '08 #1
9 12339
acoder
16,027 Expert Mod 8TB
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?
Feb 18 '08 #2
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
Feb 19 '08 #3
acoder
16,027 Expert Mod 8TB
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.
Feb 19 '08 #4
How can I make an Email Textbox as a readonly using dojo widget?
Mar 1 '08 #5
acoder
16,027 Expert Mod 8TB
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?
Mar 1 '08 #6
copy and past the codes below save it as read.cfm:

Expand|Select|Wrap|Line Numbers
  1. <!--- default values --->
  2. <cfparam name="form.InputExcelFile" default="">
  3. <cfparam name="form.StartCol" default="A">
  4. <cfparam name="form.EndCol" default="0">
  5. <cfparam name="form.StartRow" default="1">
  6. <cfparam name="form.EndRow" default="0">
  7. <cfparam name="form.Sheet" default="Sheet1">
  8. <cfparam name="form.HeaderRow" default="">
  9. <cfparam name="form.Formula" default="process">
  10. <cfparam name="form.DecimalDigit" default="2">
  11. <cfparam name="form.ProcessMerged" default="true">
  12.  
  13. <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
  14.  
  15. <html>
  16. <head>
  17.     <title>CFX_Excel Example</title>
  18.     <style type="text/css">
  19.         .textfield {  font-family: "MS Sans Serif"; font-size: 9pt; border: 1px solid #cccccc; }
  20.         .button {  font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; font-weight: bold; color: #FFFFFF; background-color: #000000; cursor: hand}
  21.         .tablestandard { font-family: Verdana, Arial, Helvetica, sans-serif; font-size: 10px; }
  22.     </style>
  23. </head>
  24.  
  25. <body>
  26.     <cfoutput>
  27.     <h3>CFX_Excel Example</h3>
  28.     <h4>Read Operation - Convert MS Excel File To ColdFusion Query</h4>
  29.     <hr size=1>
  30.     <table width="100%" cellpadding="2" cellspacing="2" border="0" class="tablestandard">
  31.         <cfform action="example_read.cfm" method="POST" enctype="multipart/form-data">
  32.             <tr>
  33.                 <td nowrap valign="top">Upload Excel file to read:</td>
  34.                 <td width="100%" valign="top">
  35.                     <input type="File" name="InputExcelFile" size="40" class="textfield">
  36.                 </td>
  37.             </tr>
  38.             <tr>
  39.                 <td nowrap valign="top">Sheet name or index##:</td>
  40.                 <td width="100%" valign="top">
  41.                     <cfinput type="Text" class="textfield" name="Sheet" value="#form.Sheet#" size="8">
  42.                 </td>
  43.             </tr>
  44.             <tr>
  45.                 <td nowrap valign="top">Column to process:</td>
  46.                 <td width="100%" valign="top">
  47.                     Start: <cfinput type="Text" class="textfield" name="StartCol" value="#form.StartCol#" size="8">
  48.                     End: <cfinput type="Text" class="textfield" name="EndCol" value="#form.EndCol#" size="8">
  49.                     (type '0' in the end attribute to process all non-empty/edited columns)
  50.                 </td>
  51.             </tr>
  52.             <tr>
  53.                 <td nowrap valign="top">Row to process:</td>
  54.                 <td width="100%" valign="top">
  55.                     Start: <cfinput type="Text" class="textfield" name="StartRow" value="#form.StartRow#" range="1," size="8">
  56.                     End: <cfinput type="Text" class="textfield" name="EndRow" value="#form.EndRow#" size="8">
  57.                     (type '0' in the end attribute to process all non-empty/edited rows)
  58.                 </td>
  59.             </tr>
  60.             <tr>
  61.                 <td nowrap valign="top">Header row:</td>
  62.                 <td width="100%" valign="top">
  63.                     <cfinput type="Text" class="textfield" name="HeaderRow" value="#form.HeaderRow#" validate="integer" range="0," size="4"><br>
  64.                     (Row containing column names, if any)
  65.                 </td>
  66.             </tr>
  67.             <tr>
  68.                 <td nowrap valign="top">Number of decimal digits:</td>
  69.                 <td width="100%" valign="top">
  70.                     <cfinput type="Text" class="textfield" name="DecimalDigit" value="#form.DecimalDigit#" validate="integer" range="0," size="4">
  71.                 </td>
  72.             </tr>
  73.             <tr>
  74.                 <td valign="top">Formula:</td>
  75.                 <td valign="top">
  76.                     <input type="Radio" name="Formula" value="process" #iif(form.Formula is "process",de("checked"),de(""))#>Process<br>
  77.                     <input type="Radio" name="Formula" value="show" #iif(form.Formula is "show",de("checked"),de(""))#>Show<br>
  78.                 </td>
  79.             </tr>
  80.             <tr>
  81.                 <td valign="top">Process merged cells:</td>
  82.                 <td valign="top">
  83.                     <input type="Radio" name="ProcessMerged" value="true" #iif(form.ProcessMerged is "true",de("checked"),de(""))#>True<br>
  84.                     <input type="Radio" name="ProcessMerged" value="false" #iif(form.ProcessMerged is "false",de("checked"),de(""))#>False<br>
  85.                 </td>
  86.             </tr>
  87.             <tr>
  88.                 <td colspan="2">
  89.                     <input type="Submit" value="Convert Excel To CF Query Now" class="button"><br>
  90.                     <br>
  91.                     <font size="1">
  92.                         <a href="example_write.cfm">Click here to see the CFX_Excel write example</a>
  93.                     </font>                    
  94.                 </td>
  95.             </tr>
  96.         </cfform>
  97.     </table>
  98.  
  99.     <cfif form.InputExcelFile neq "">
  100.         <!--- read operation --->
  101.         <hr size="1">
  102.  
  103.         <!--- define temp excel --->
  104.         <cfset strDir=GetDirectoryFromPath(ExpandPath("*.*")) & "/temp">
  105.         <cfset strInExcel=strDir>
  106.  
  107.         <!--- upload image --->
  108.         <cffile action="Upload"
  109.                       filefield="InputExcelFile"
  110.                       destination="#strInExcel#"
  111.                       nameconflict="MAKEUNIQUE" 
  112.                         mode="757">
  113.         <cfset prodThumbDir=file.ServerDirectory>
  114.         <cfset prodThumbFile=file.ServerFile>
  115.         <cfset prodThumbExt=file.serverfileext>
  116.         <cfif (prodThumbExt neq "xls")>
  117.             CFX_Excel accepts .xls file only
  118.         <cfelse>
  119.             <!--- read excel --->
  120.             <cftry>
  121.                 <cfset tickBegin = GetTickCount()>
  122.                 <cfx_excel action="read" 
  123.                                         File="#prodThumbDir#/#prodThumbFile#"
  124.                                         Sheet="#form.Sheet#"
  125.                                         Result="qryResult"
  126.                                         Formula="#form.Formula#"
  127.                                         StartRow="#form.StartRow#"
  128.                                         EndRow="#form.EndRow#"
  129.                                         StartCol="#form.StartCol#"
  130.                                         EndCol="#form.EndCol#"
  131.                                         HeaderRow="#form.HeaderRow#"
  132.                                         ProcessMerged="#form.ProcessMerged#"
  133.                                         DecimalDigit="#form.DecimalDigit#">
  134.                 <cfset tickEnd = GetTickCount()>
  135.                 <cfset loopTime = tickEnd - tickBegin>            
  136.  
  137.                 <!--- show result query --->
  138.                 <h3>Conversion Result:</h3>
  139.                 <cfdump var="#qryResult#">
  140.  
  141.                 <hr size="2">
  142.                 <h3>Performance</h3>
  143.                 Convert time was: #loopTime# milliseconds<br>
  144.                 Query info: #qryResult.recordcount#<br>
  145.                 <cfif looptime gt 0>
  146.                     Approx. speed: #numberformat(qryResult.recordcount/(looptime/1000))# rows/second
  147.                 </cfif>
  148.                 <br><br>
  149.                 <font size="-1">
  150.                     (Please note that our live example is running in a very busy shared hosting environment)
  151.                 </font>
  152.  
  153.             <cfcatch type="Any">
  154.                 <h3>There was a problem while reading '#prodThumbFile#'</h3>
  155.                 Check the file, columns to process, rows to process and other properties,<br>
  156.                 to make sure all source cells can be converted to recordset.
  157.             </cfcatch>
  158.             </cftry>
  159.         </cfif>        
  160.  
  161.         <!--- remove temp excel --->
  162.         <cftry>
  163.             <cffile action="DELETE" file="#prodThumbDir#/#prodThumbFile#">
  164.             <cfcatch type="Any"></cfcatch>
  165.         </cftry>
  166.     </cfif>
  167.     </cfoutput>
  168. </body>
  169. </html>
  170.  

The same with the codes below and save it as viewexcel.cfm:


Expand|Select|Wrap|Line Numbers
  1. <cfparam name="url.ExcelFile" default="">
  2. <cfif listlast(url.ExcelFile,".") neq "xls">
  3.     <cfabort>
  4. </cfif>
  5. <cfset strDir=GetDirectoryFromPath(ExpandPath("*.*"))>
  6. <cfif not fileexists("#strDir#temp/#url.ExcelFile#")>
  7.     <cfabort>
  8. </cfif>
  9. <cfheader name="Content-Disposition" value="inline; filename=#url.ExcelFile#">
  10. <cfcontent type="application/unknown" file="#strDir#temp/#url.ExcelFile#" deletefile="yes">
You can Convert MS Excel File To ColdFusion Query.
Mar 13 '08 #7
acoder
16,027 Expert Mod 8TB
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?
Mar 13 '08 #8
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.
Mar 13 '08 #9
acoder
16,027 Expert Mod 8TB
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'.
Mar 13 '08 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Damien | last post: by
reply views Thread by Phil Perrin | last post: by
8 posts views Thread by Johnny | last post: by
reply views Thread by harlem98 | last post: by
1 post views Thread by Geralt96 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.