1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Question re Excel to MYSQL to HTML

Discussion in 'General Programming Chat' started by Tony57, Feb 6, 2009.

  1. Tony57

    Tony57 Registered Member

    Joined:
    Jan 8, 2008
    Messages:
    60
    Likes Received:
    138
    I've been looking for a solution and easy way to take data from an Excel file, import it into a MYSQL database and display the information as a formatted table in a webpage (Wordpress).

    Anyone know how to do this easily?

    Tony
     
  2. cyklotrial

    cyklotrial Regular Member

    Joined:
    Oct 13, 2008
    Messages:
    248
    Likes Received:
    82
    Location:
    Wonderland
    Can you show example of this conversion? eg.

    excell
    ID|USERNAME|PASSWORD
    1 |Cyklotrial |Password

    MySql
    ID|USER |PASSWD
    1 |Cyklotrial |Password
     
  3. Tony57

    Tony57 Registered Member

    Joined:
    Jan 8, 2008
    Messages:
    60
    Likes Received:
    138
    I manage a website for a hobbyist who wants to show data from a simple Excel table, 6 columns with constantly added rows as new records are added. Nothing too complex in Excel, no formulas etc just 6 columns with a growing number of rows with new data added on the first row.

    He works in Excel hence it would be easiest to convert from that but if there's another way, whereby he could add data directly to the MYSQL DB that might be an option.

    I really have no idea where to start...

    Tony
     
  4. cyklotrial

    cyklotrial Regular Member

    Joined:
    Oct 13, 2008
    Messages:
    248
    Likes Received:
    82
    Location:
    Wonderland
    Firs save excel file as CSV file separated by coma.

    PHP:
    <?php
    function read_csv($csv_file$index_by 0$separator ';'$rec_len 1024)
    {
       
    $handle fopen($csv_file'r');
       if(
    $handle == null || ($data fgetcsv($handle$rec_len$separator)) === false)
       {
           
    // Couldn't open/read from CSV file.
           
    return -1;
       }

       
    $names = array();
       foreach(
    $data as $field)
       {
           
    $names[] = trim($field);
       }

       if(
    is_int($index_by))
       {
           if(
    $index_by || $index_by count($data))
           {
               
    // Index out of bounds.
               
    fclose($handle);
               return -
    2;
           }
       }
       else
       {
           
    // If the column to index by is given as a name rather than an integer, then
           // determine that named column's integer index in the $names array, because
           // the integer index is used, below.
           
    $get_index array_keys($names$index_by);
           
    $index_by $get_index[0];

           if(
    is_null($index_by))
           {
               
    // A column name was given (as opposed to an integer index), but the
               // name was not found in the first row that was read from the CSV file.
               
    fclose($handle);
               return -
    3;
           }
       }

       
    $retval = array();
       while((
    $data fgetcsv($handle$rec_len$separator)) !== false)
       {
           
    $retval[trim($data[$index_by])] = array_combine($names$data);
       }
       
    fclose($handle);

       return 
    $retval;
    }
    ?>
    to show some data:
    PHP:
    <?php
    $data 
    read_csv('file.csv');
    echo 
    $data['1']['header'];//the first cell from the "header" column 
    ?>
    source: php.net

    regards
     
    • Thanks Thanks x 1