copy data from ms excel into mysql - Programmers Heaven

Howdy, Stranger!

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

Categories

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
    [email protected]
    ----------------------------
  • 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
    [email protected]
    ----------------------------


  • 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
    [email protected]
    ----------------------------


Sign In or Register to comment.