Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

copy data from ms excel into mysql

firestar_lenefirestar_lene Posts: 33Member
is there any way to copy data from ms excel into mysql?any coding for php?

Comments

  • SouldrinkerSouldrinker Posts: 143Member
    : is there any way to copy data from ms excel into mysql?any coding for php?
    :

    No coding, but a way of solving:

    Convert your MS Excel Document into csv data, and up it to your webserver. Now read out the file, and split each line at ";". Now you can translate your arrays into a mysql relation.
  • fffsoftfffsoft Posts: 6Member
    am sending u the code, make sure to create a database and a table in it to copy excel data, plz change the database name according to ur databade in file inputExcel.php.
    there r 3 files in this regard.
    here is coding of 3 files and u will run index.html to copy data.

    note that only IE will do bcoz mozilla donot send file full path.
    -------------
    index.html
    -------------



    Welcome to Excel2Html :: Login ::

    function validate(){
    if(form1.inFile.value == ""){
    alert("Please insert filename!");
    form1.inFile.focus();
    return false;
    }
    return true;
    }



    Input the Excel File values











    Input File
    Worksheet 
    Start Cell 
    End Cell 
       





    ---------------------------
    inputExcel.php
    --------------------------
    <?php
    require ("excel2html.php");
    // require ("function.php");
    //$pathInName = $_POST['inFile'];
    $pathInName = isset($_REQUEST['inFile'])? $_REQUEST['inFile']:'';
    echo $pathInName;
    $worksheet = $_POST['worksheet'];
    $startCell = $_POST['startCell'];
    $endCell = $_POST['endCell'];

    $pathInName = realpath($pathInName); //some browser store differently

    echo $pathInName;
    // Instantiate Excel
    $E = new Excel2html;
    $workbook = $E->myfile($pathInName);
    $pathin = $E->mypath($pathInName);



    // Default value for Worksheet
    if (!$worksheet)$worksheet = 'Sheet1';

    // Open the workbook
    $E->XL($workbook, $pathin, $sheet);

    // Default value for Starting Cell
    if (!$startCell)$startCell = 'A1';

    // Default value for End Cell
    if (!$endCell)$endCell = 'd5';
    $sheet = $worksheet;
    $startStr = $E->getCellString($startCell);
    $startNum = $E->getCellNum($startCell);
    $endStr = $E->getCellString($endCell);
    $endNum = $E->getCellNum($endCell);

    // Read the content of range of cells and output to html file
    $cellRange = $startCell . ":" . $endCell;
    $content = $E->readrange($sheet, $cellRange);
    echo "Excel to HTML";
    //echo "";
    echo "
    ";
    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /* echo "";
    echo strtoupper($startStr++); // COLUMNS
    echo "";*/


    }
    echo "";
    }
    echo "";



    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /*if ($num == 0) {
    if ($numArray == 0)
    print "";
    echo "";
    } else {
    if ($numArray == 0)*/
    $startNum++;

    //echo "num = " . $num . "--numArray = " . $numArray;


    //echo $content[$num][$numArray];
    $a = $content[$num][$numArray]; echo $a; $numArray++;
    $b = $content[$num][$numArray]; echo $b; $numArray++;
    $c = $content[$num][$numArray]; echo $c;
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable(d_code, destination, rate) VALUES('$a', '$b', '$c')");



    //}
    }
    echo "";
    }
    }
    echo "
    " . $startNum++ . "";
    echo $content[$num][$numArray]; echo "row1";
    echo "


    ";
    echo "Selected data has been copied to MySql

    PLEASE CLOSE THIS WINDOW";

    /*
    echo $content[0][0] . $content[0][1] . $content[0][2];
    $a = $content[0][0];
    $b = $content[0][1];
    $c = $content[0][2];
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable
    (name, extension, email) VALUES('$a', '$b', '$c')");
    */
    echo "";

    // Close the file
    $E->closexl();
    unset ($E);
    exit();
    ?>
    -------------------------
    inputExcel2.php
    -----------------------
    <?php
    require ("excel2html.php");
    // require ("function.php");
    $pathInName = $_POST['inFile'];
    $worksheet = $_POST['worksheet'];
    $startCell = $_POST['startCell'];
    $endCell = $_POST['endCell'];
    $pathInName = realpath($pathInName); //some browser store differently

    // Instantiate Excel
    $E = new Excel2html;
    $workbook = $E->myfile($pathInName);
    $pathin = $E->mypath($pathInName);



    // Default value for Worksheet
    if (!$worksheet)$worksheet = 'Sheet1';

    // Open the workbook
    $E->XL($workbook, $pathin, $sheet);

    // Default value for Starting Cell
    if (!$startCell)$startCell = 'A1';

    // Default value for End Cell
    if (!$endCell)$endCell = 'd5';
    $sheet = $worksheet;
    $startStr = $E->getCellString($startCell);
    $startNum = $E->getCellNum($startCell);
    $endStr = $E->getCellString($endCell);
    $endNum = $E->getCellNum($endCell);

    // Read the content of range of cells and output to html file
    $cellRange = $startCell . ":" . $endCell;
    $content = $E->readrange($sheet, $cellRange);
    echo "Excel to HTML";
    //echo "";
    echo "
    ";
    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    echo "";
    echo strtoupper($startStr++); // COLUMNS
    echo "";
    }
    echo "";
    }
    echo "";



    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /*if ($num == 0) {
    if ($numArray == 0)
    print "";
    echo "";
    } else {
    if ($numArray == 0)*/
    $startNum++;

    //echo "num = " . $num . "--numArray = " . $numArray;


    //echo $content[$num][$numArray];
    $a = $content[$num][$numArray]; $numArray++;
    $b = $content[$num][$numArray]; $numArray++;
    $c = $content[$num][$numArray];
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable
    (d_code, destination, rate) VALUES('$a', '$b', '$c')");



    //}
    }
    echo "";
    }
    }
    echo "
    " . $startNum++ . "";
    echo $content[$num][$numArray]; echo "row1";
    echo "


    ";
    /*
    echo $content[0][0] . $content[0][1] . $content[0][2];
    $a = $content[0][0];
    $b = $content[0][1];
    $c = $content[0][2];
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable
    (name, extension, email) VALUES('$a', '$b', '$c')");
    */
    echo "";

    // Close the file
    $E->closexl();
    unset ($E);
    exit();
    ?>
    -------------------------------
    take care
    from Muhammad Farooq
    fffsoft@hotmail.com
    ----------------------------
  • CSK001CSK001 Posts: 7Member
    Hi,
    I would suggest you to navigate about this one, on internet. There is lots of information available on this.

    CSK


  • joshimadan78joshimadan78 Posts: 1Member
    Dear Sir!

    its require excel2html.php file, where is it and how can i create it,

    kindly help,
  • fffsoftfffsoft Posts: 6Member
    o dear unfortune, excel2html.php was to be coded, but a same file was duplicated now i am sending u complete.
    --------------------
    am sending u the code, make sure to create a database and a table in it to copy excel data, plz change the database name according to ur databade in file inputExcel.php.
    there r 3 files in this regard.
    here is coding of 3 files and u will run index.html to copy data.

    note that only IE will do bcoz mozilla donot send file full path.
    -------------
    index.html
    -------------



    Welcome to Excel2Html :: Login ::

    function validate(){
    if(form1.inFile.value == ""){
    alert("Please insert filename!");
    form1.inFile.focus();
    return false;
    }
    return true;
    }



    Input the Excel File values











    Input File
    Worksheet 
    Start Cell 
    End Cell 
       





    ---------------------------
    inputExcel.php
    --------------------------
    <?php
    require ("excel2html.php");
    // require ("function.php");
    //$pathInName = $_POST['inFile'];
    $pathInName = isset($_REQUEST['inFile'])? $_REQUEST['inFile']:'';
    echo $pathInName;
    $worksheet = $_POST['worksheet'];
    $startCell = $_POST['startCell'];
    $endCell = $_POST['endCell'];

    $pathInName = realpath($pathInName); //some browser store differently

    echo $pathInName;
    // Instantiate Excel
    $E = new Excel2html;
    $workbook = $E->myfile($pathInName);
    $pathin = $E->mypath($pathInName);



    // Default value for Worksheet
    if (!$worksheet)$worksheet = 'Sheet1';

    // Open the workbook
    $E->XL($workbook, $pathin, $sheet);

    // Default value for Starting Cell
    if (!$startCell)$startCell = 'A1';

    // Default value for End Cell
    if (!$endCell)$endCell = 'd5';
    $sheet = $worksheet;
    $startStr = $E->getCellString($startCell);
    $startNum = $E->getCellNum($startCell);
    $endStr = $E->getCellString($endCell);
    $endNum = $E->getCellNum($endCell);

    // Read the content of range of cells and output to html file
    $cellRange = $startCell . ":" . $endCell;
    $content = $E->readrange($sheet, $cellRange);
    echo "Excel to HTML";
    //echo "";
    echo "
    ";
    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /* echo "";
    echo strtoupper($startStr++); // COLUMNS
    echo "";*/


    }
    echo "";
    }
    echo "";



    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /*if ($num == 0) {
    if ($numArray == 0)
    print "";
    echo "";
    } else {
    if ($numArray == 0)*/
    $startNum++;

    //echo "num = " . $num . "--numArray = " . $numArray;


    //echo $content[$num][$numArray];
    $a = $content[$num][$numArray]; echo $a; $numArray++;
    $b = $content[$num][$numArray]; echo $b; $numArray++;
    $c = $content[$num][$numArray]; echo $c;
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable(d_code, destination, rate) VALUES('$a', '$b', '$c')");



    //}
    }
    echo "";
    }
    }
    echo "
    " . $startNum++ . "";
    echo $content[$num][$numArray]; echo "row1";
    echo "


    ";
    echo "Selected data has been copied to MySql

    PLEASE CLOSE THIS WINDOW";

    /*
    echo $content[0][0] . $content[0][1] . $content[0][2];
    $a = $content[0][0];
    $b = $content[0][1];
    $c = $content[0][2];
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable
    (name, extension, email) VALUES('$a', '$b', '$c')");
    */
    echo "";

    // Close the file
    $E->closexl();
    unset ($E);
    exit();
    ?>

    -------------------------
    excel2html.php
    ------------------------
    <?php

    class Excel2html {
    /* variables */

    var $pathin;
    var $workbook;
    var $sheet;
    var $range;
    var $celladdress;

    /* Constructor */

    function excel2html()
    {
    // Instantiate Excel
    $this->ex = new COM("Excel.sheet") or Die ("Did not instantiate Excel");

    return 1;
    }

    function XL($workbook, $pathin = "", $sheet = "Sheet1")
    {
    if ($workbook) {
    // Load the workbook
    $wkb = $this->ex->application->Workbooks->Open($pathin . $workbook) or Die ("Did not open $pathin $workbook");
    } else {
    // New workbook
    $wkb = $this->ex->application->Workbooks->Add or Die ("Unable to add a workbook");
    }

    if ($sheet) {
    // Activate the sheet
    $sheets = $wkb->Worksheets($sheet) or Die ("Unable to activate $sheet");
    } else {
    // new sheet
    $sheet = "Sheet1" ;
    }
    // Excel Won't prompt the user when replacing or closing workbooks
    // Comment the line below if you want Excel to prompt
    $this->ex->application->DisplayAlerts = "False";
    return 1;
    }

    function readrange($sheet = "Sheet1", $range)
    {
    // Read all the cells in the range to $result and return it
    unset ($result);

    $range = trim($range);
    // Determine start and end of range
    $tokstart = strtok($range, ":");
    $tokend = strtok(":");
    if ($tokend == "") {
    // Read one single cell
    $sheets = $this->ex->Application->Worksheets($sheet);
    $sheets->activate;
    // Select the cell
    $selcell = $sheets->Range($range);
    $selcell->activate;
    return $selcell->value;
    }
    // Read a range of cells
    // determine column and row numbers
    $sheets = $this->ex->Application->Worksheets($sheet);
    $sheets->activate;
    $rgstart = $sheets->range($tokstart);
    $colstart = $rgstart->column;
    $rowstart = $rgstart->row;
    $rgend = $sheets->range($tokend);
    $colend = $rgend->column;
    $rowend = $rgend->row;
    if ($colstart > $colend or $rowstart > $rowend) {
    Print ("Notation Error! Cell Column/Row should be increasing.");
    return;
    }
    // Now read each cell
    if ($colstart == $colend) {
    // Read Vertically
    $j = 0;
    For ($i = $rowstart; $i <= $rowend; $i++) {
    $selcell = $sheets->cells($i, $colstart);
    $selcell->activate;
    $result[$j] = $selcell->value;
    $j++;
    }
    } else {
    // Read vertically
    $k = 0;
    For ($l = $rowstart; $l <= $rowend; $l++) {
    // Read horizontally
    $j = 0;
    For ($i = $colstart; $i <= $colend; $i++) {
    $selcell = $sheets->cells($rowstart, $i);
    $selcell->activate;
    $result[$j] = $selcell->value;
    // echo "result:$result[$j]
    ";
    $j++;
    }
    $resultArray[$k] = $result;
    $rowstart++;
    $k++;
    }
    }

    return $resultArray;
    }
    // Returns the file name
    function myfile($pathin)
    {
    $pathComponent = explode("\", $pathin);
    for($i = 0;$i < count($pathComponent)-1;$i++)
    if (!$path)
    $path = $pathComponent[$i];
    else
    $path = $path . "\" . $pathComponent[$i];

    return $workbook = $pathComponent[count($pathComponent)-1];
    }
    // Returns the file path
    function mypath($pathin)
    {
    $pathComponent = explode("\", $pathin);
    for($i = 0;$i < count($pathComponent)-1;$i++)
    if (!$path)
    $path = $pathComponent[$i];
    else
    $path = $path . "\" . $pathComponent[$i];

    return $pathin = $path . "\";
    }
    // Returns the Starting Cell String value
    function getCellString($celladdress)
    {
    for($i = 1;$i <= strlen($celladdress);$i++) {
    if (is_numeric(substr($celladdress, - ($i)))) {
    $cellStr = substr($celladdress, 0, strlen($celladdress) - $i);
    }
    }
    return $cellStr;
    }
    // Returns the starting cell Numeric value
    function getCellNum($celladdress)
    {
    for($i = 1;$i <= strlen($celladdress);$i++) {
    if (is_numeric(substr($celladdress, - ($i))))
    $cellnum = substr($celladdress, - $i) ;
    }
    return $cellnum;
    }

    function closexl()
    {
    // Close active workbook without prompt from Excel
    $this->ex->application->ActiveWorkbook->Close("False");
    return 1;
    }
    }
    /* end of Excel class */

    ?>

    -------------------------------
    take care
    from Muhammad Farooq
    fffsoft@hotmail.com
    ----------------------------


  • fffsoftfffsoft Posts: 6Member
    o dear unfortune, excel2html.php was to be coded, but a same file was duplicated now i am sending u complete.
    --------------------
    am sending u the code, make sure to create a database and a table in it to copy excel data, plz change the database name according to ur databade in file inputExcel.php.
    there r 3 files in this regard.
    here is coding of 3 files and u will run index.html to copy data.

    note that only IE will do bcoz mozilla donot send file full path.
    -------------
    index.html
    -------------



    Welcome to Excel2Html :: Login ::

    function validate(){
    if(form1.inFile.value == ""){
    alert("Please insert filename!");
    form1.inFile.focus();
    return false;
    }
    return true;
    }



    Input the Excel File values











    Input File
    Worksheet 
    Start Cell 
    End Cell 
       





    ---------------------------
    inputExcel.php
    --------------------------
    <?php
    require ("excel2html.php");
    // require ("function.php");
    //$pathInName = $_POST['inFile'];
    $pathInName = isset($_REQUEST['inFile'])? $_REQUEST['inFile']:'';
    echo $pathInName;
    $worksheet = $_POST['worksheet'];
    $startCell = $_POST['startCell'];
    $endCell = $_POST['endCell'];

    $pathInName = realpath($pathInName); //some browser store differently

    echo $pathInName;
    // Instantiate Excel
    $E = new Excel2html;
    $workbook = $E->myfile($pathInName);
    $pathin = $E->mypath($pathInName);



    // Default value for Worksheet
    if (!$worksheet)$worksheet = 'Sheet1';

    // Open the workbook
    $E->XL($workbook, $pathin, $sheet);

    // Default value for Starting Cell
    if (!$startCell)$startCell = 'A1';

    // Default value for End Cell
    if (!$endCell)$endCell = 'd5';
    $sheet = $worksheet;
    $startStr = $E->getCellString($startCell);
    $startNum = $E->getCellNum($startCell);
    $endStr = $E->getCellString($endCell);
    $endNum = $E->getCellNum($endCell);

    // Read the content of range of cells and output to html file
    $cellRange = $startCell . ":" . $endCell;
    $content = $E->readrange($sheet, $cellRange);
    echo "Excel to HTML";
    //echo "";
    echo "
    ";
    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /* echo "";
    echo strtoupper($startStr++); // COLUMNS
    echo "";*/


    }
    echo "";
    }
    echo "";



    for ($numArray = 0; $numArray < $countArray; $numArray++) {
    /*if ($num == 0) {
    if ($numArray == 0)
    print "";
    echo "";
    } else {
    if ($numArray == 0)*/
    $startNum++;

    //echo "num = " . $num . "--numArray = " . $numArray;


    //echo $content[$num][$numArray];
    $a = $content[$num][$numArray]; echo $a; $numArray++;
    $b = $content[$num][$numArray]; echo $b; $numArray++;
    $c = $content[$num][$numArray]; echo $c;
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable(d_code, destination, rate) VALUES('$a', '$b', '$c')");



    //}
    }
    echo "";
    }
    }
    echo "
    " . $startNum++ . "";
    echo $content[$num][$numArray]; echo "row1";
    echo "


    ";
    echo "Selected data has been copied to MySql

    PLEASE CLOSE THIS WINDOW";

    /*
    echo $content[0][0] . $content[0][1] . $content[0][2];
    $a = $content[0][0];
    $b = $content[0][1];
    $c = $content[0][2];
    mysql_connect("localhost", "root", "") or die(mysql_error());
    mysql_select_db("database") or die(mysql_error());
    mysql_query("INSERT INTO mytable
    (name, extension, email) VALUES('$a', '$b', '$c')");
    */
    echo "";

    // Close the file
    $E->closexl();
    unset ($E);
    exit();
    ?>

    -------------------------
    excel2html.php
    ------------------------
    <?php

    class Excel2html {
    /* variables */

    var $pathin;
    var $workbook;
    var $sheet;
    var $range;
    var $celladdress;

    /* Constructor */

    function excel2html()
    {
    // Instantiate Excel
    $this->ex = new COM("Excel.sheet") or Die ("Did not instantiate Excel");

    return 1;
    }

    function XL($workbook, $pathin = "", $sheet = "Sheet1")
    {
    if ($workbook) {
    // Load the workbook
    $wkb = $this->ex->application->Workbooks->Open($pathin . $workbook) or Die ("Did not open $pathin $workbook");
    } else {
    // New workbook
    $wkb = $this->ex->application->Workbooks->Add or Die ("Unable to add a workbook");
    }

    if ($sheet) {
    // Activate the sheet
    $sheets = $wkb->Worksheets($sheet) or Die ("Unable to activate $sheet");
    } else {
    // new sheet
    $sheet = "Sheet1" ;
    }
    // Excel Won't prompt the user when replacing or closing workbooks
    // Comment the line below if you want Excel to prompt
    $this->ex->application->DisplayAlerts = "False";
    return 1;
    }

    function readrange($sheet = "Sheet1", $range)
    {
    // Read all the cells in the range to $result and return it
    unset ($result);

    $range = trim($range);
    // Determine start and end of range
    $tokstart = strtok($range, ":");
    $tokend = strtok(":");
    if ($tokend == "") {
    // Read one single cell
    $sheets = $this->ex->Application->Worksheets($sheet);
    $sheets->activate;
    // Select the cell
    $selcell = $sheets->Range($range);
    $selcell->activate;
    return $selcell->value;
    }
    // Read a range of cells
    // determine column and row numbers
    $sheets = $this->ex->Application->Worksheets($sheet);
    $sheets->activate;
    $rgstart = $sheets->range($tokstart);
    $colstart = $rgstart->column;
    $rowstart = $rgstart->row;
    $rgend = $sheets->range($tokend);
    $colend = $rgend->column;
    $rowend = $rgend->row;
    if ($colstart > $colend or $rowstart > $rowend) {
    Print ("Notation Error! Cell Column/Row should be increasing.");
    return;
    }
    // Now read each cell
    if ($colstart == $colend) {
    // Read Vertically
    $j = 0;
    For ($i = $rowstart; $i <= $rowend; $i++) {
    $selcell = $sheets->cells($i, $colstart);
    $selcell->activate;
    $result[$j] = $selcell->value;
    $j++;
    }
    } else {
    // Read vertically
    $k = 0;
    For ($l = $rowstart; $l <= $rowend; $l++) {
    // Read horizontally
    $j = 0;
    For ($i = $colstart; $i <= $colend; $i++) {
    $selcell = $sheets->cells($rowstart, $i);
    $selcell->activate;
    $result[$j] = $selcell->value;
    // echo "result:$result[$j]
    ";
    $j++;
    }
    $resultArray[$k] = $result;
    $rowstart++;
    $k++;
    }
    }

    return $resultArray;
    }
    // Returns the file name
    function myfile($pathin)
    {
    $pathComponent = explode("\", $pathin);
    for($i = 0;$i < count($pathComponent)-1;$i++)
    if (!$path)
    $path = $pathComponent[$i];
    else
    $path = $path . "\" . $pathComponent[$i];

    return $workbook = $pathComponent[count($pathComponent)-1];
    }
    // Returns the file path
    function mypath($pathin)
    {
    $pathComponent = explode("\", $pathin);
    for($i = 0;$i < count($pathComponent)-1;$i++)
    if (!$path)
    $path = $pathComponent[$i];
    else
    $path = $path . "\" . $pathComponent[$i];

    return $pathin = $path . "\";
    }
    // Returns the Starting Cell String value
    function getCellString($celladdress)
    {
    for($i = 1;$i <= strlen($celladdress);$i++) {
    if (is_numeric(substr($celladdress, - ($i)))) {
    $cellStr = substr($celladdress, 0, strlen($celladdress) - $i);
    }
    }
    return $cellStr;
    }
    // Returns the starting cell Numeric value
    function getCellNum($celladdress)
    {
    for($i = 1;$i <= strlen($celladdress);$i++) {
    if (is_numeric(substr($celladdress, - ($i))))
    $cellnum = substr($celladdress, - $i) ;
    }
    return $cellnum;
    }

    function closexl()
    {
    // Close active workbook without prompt from Excel
    $this->ex->application->ActiveWorkbook->Close("False");
    return 1;
    }
    }
    /* end of Excel class */

    ?>

    -------------------------------
    take care
    from Muhammad Farooq
    fffsoft@hotmail.com
    ----------------------------


Sign In or Register to comment.