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

Quote:

Originally Posted by 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?

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

Quote:

Originally Posted by sukumar 123

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:

Expand|Select|Wrap|Line Numbers

 <!--- default values --->

<cfparam name="form.InputExcelFile" default="">

<cfparam name="form.StartCol" default="A">

<cfparam name="form.EndCol" default="0">

<cfparam name="form.StartRow" default="1">

<cfparam name="form.EndRow" default="0">

<cfparam name="form.Sheet" default="Sheet1">

<cfparam name="form.HeaderRow" default="">

<cfparam name="form.Formula" default="process">

<cfparam name="form.DecimalDigit" default="2">

<cfparam name="form.ProcessMerged" default="true">
 
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
 
<html>

<head>

    <title>CFX_Excel 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="tablestandard">

        <cfform action="example_read.cfm" method="POST" enctype="multipart/form-data">

            <tr>

                <td nowrap valign="top">Upload Excel file to read:</td>

                <td width="100%" valign="top">

                    <input type="File" name="InputExcelFile" size="40" class="textfield">

                </td>

            </tr>

            <tr>

                <td nowrap valign="top">Sheet name or index##:</td>

                <td width="100%" valign="top">

                    <cfinput type="Text" class="textfield" name="Sheet" value="#form.Sheet#" size="8">

                </td>

            </tr>

            <tr>

                <td nowrap valign="top">Column to process:</td>

                <td width="100%" valign="top">

                    Start: <cfinput type="Text" class="textfield" name="StartCol" value="#form.StartCol#" size="8">

                    End: <cfinput type="Text" class="textfield" name="EndCol" value="#form.EndCol#" size="8">

                    (type '0' in the end attribute to process all non-empty/edited columns)

                </td>

            </tr>

            <tr>

                <td nowrap valign="top">Row to process:</td>

                <td width="100%" valign="top">

                    Start: <cfinput type="Text" class="textfield" name="StartRow" value="#form.StartRow#" range="1," size="8">

                    End: <cfinput type="Text" class="textfield" name="EndRow" value="#form.EndRow#" size="8">

                    (type '0' in the end attribute to process all non-empty/edited rows)

                </td>

            </tr>

            <tr>

                <td nowrap valign="top">Header row:</td>

                <td width="100%" valign="top">

                    <cfinput type="Text" class="textfield" name="HeaderRow" value="#form.HeaderRow#" validate="integer" range="0," size="4"><br>

                    (Row containing column names, if any)

                </td>

            </tr>

            <tr>

                <td nowrap valign="top">Number of decimal digits:</td>

                <td width="100%" valign="top">

                    <cfinput type="Text" class="textfield" name="DecimalDigit" value="#form.DecimalDigit#" validate="integer" range="0," size="4">

                </td>

            </tr>

            <tr>

                <td valign="top">Formula:</td>

                <td valign="top">

                    <input type="Radio" name="Formula" value="process" #iif(form.Formula is "process",de("checked"),de(""))#>Process<br>

                    <input type="Radio" name="Formula" value="show" #iif(form.Formula is "show",de("checked"),de(""))#>Show<br>

                </td>

            </tr>

            <tr>

                <td valign="top">Process merged cells:</td>

                <td valign="top">

                    <input type="Radio" name="ProcessMerged" value="true" #iif(form.ProcessMerged is "true",de("checked"),de(""))#>True<br>

                    <input type="Radio" name="ProcessMerged" value="false" #iif(form.ProcessMerged is "false",de("checked"),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_write.cfm">Click here to see the CFX_Excel write example</a>

                    </font>                    

                </td>

            </tr>

        </cfform>

    </table>
 
    <cfif form.InputExcelFile neq "">

        <!--- read operation --->

        <hr size="1">
 
        <!--- define temp excel --->

        <cfset strDir=GetDirectoryFromPath(ExpandPath("*.*")) & "/temp">

        <cfset strInExcel=strDir>
 
        <!--- upload image --->

        <cffile action="Upload"

                      filefield="InputExcelFile"

                      destination="#strInExcel#"

                      nameconflict="MAKEUNIQUE" 

                        mode="757">

        <cfset prodThumbDir=file.ServerDirectory>

        <cfset prodThumbFile=file.ServerFile>

        <cfset prodThumbExt=file.serverfileext>

        <cfif (prodThumbExt neq "xls")>

            CFX_Excel accepts .xls file only

        <cfelse>

            <!--- read excel --->

            <cftry>

                <cfset tickBegin = GetTickCount()>

                <cfx_excel action="read" 

                                        File="#prodThumbDir#/#prodThumbFile#"

                                        Sheet="#form.Sheet#"

                                        Result="qryResult"

                                        Formula="#form.Formula#"

                                        StartRow="#form.StartRow#"

                                        EndRow="#form.EndRow#"

                                        StartCol="#form.StartCol#"

                                        EndCol="#form.EndCol#"

                                        HeaderRow="#form.HeaderRow#"

                                        ProcessMerged="#form.ProcessMerged#"

                                        DecimalDigit="#form.DecimalDigit#">

                <cfset tickEnd = GetTickCount()>

                <cfset loopTime = tickEnd - tickBegin>            
 
                <!--- show result query --->

                <h3>Conversion Result:</h3>

                <cfdump var="#qryResult#">
 
                <hr size="2">

                <h3>Performance</h3>

                Convert time was: #loopTime# milliseconds<br>

                Query info: #qryResult.recordcount#<br>

                <cfif looptime gt 0>

                    Approx. speed: #numberformat(qryResult.recordcount/(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="#prodThumbDir#/#prodThumbFile#">

            <cfcatch type="Any"></cfcatch>

        </cftry>

    </cfif>

    </cfoutput>

</body>

</html>

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

Expand|Select|Wrap|Line Numbers

 <cfparam name="url.ExcelFile" default="">

<cfif listlast(url.ExcelFile,".") neq "xls">

    <cfabort>

</cfif>

<cfset strDir=GetDirectoryFromPath(ExpandPath("*.*"))>

<cfif not fileexists("#strDir#temp/#url.ExcelFile#")>

    <cfabort>

</cfif>

<cfheader name="Content-Disposition" value="inline; filename=#url.ExcelFile#">

<cfcontent type="application/unknown" file="#strDir#temp/#url.ExcelFile#" deletefile="yes">

You can Convert MS Excel File To ColdFusion Query.

acoder

Please enclose your posted code in [code] tags (See How to Ask a Question).

This makes it easier for everyone to read and understand it. Failing to do so creates extra work for the moderators, thus wasting resources, otherwise available to answer the members' questions.

Please use [code] tags in future.

MODERATOR

acoder

Quote:

Originally Posted by cfmx2008

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

Quote:

Originally Posted by acoder

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

Quote:

Originally Posted by cfmx2008

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'.

how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

re: how can i import excel data into mysql

Similar ColdFusion bytes

/bytes/development

/bytes/it

/bytes/about