« « It’s Alive!! Shapeoko Motor Stalling Solution | PHP Script to Download Basecamp Files.. Updated For New Basecamp » »

Make a PHP Database out of an Excel Spreadsheet

Jan 18, 2013 by     11 Comments    Posted under: PHP, Tutorials

I recently ran into a situation where I needed to create a dynamic, data driven website, but I wasn’t allowed to install a MySQL database. Why? I’m not sure. But it became an interesting exercise in generating a database on the fly, and I thought I’d share the code in case anyone else needed to do something similar.

The first step is to create your Excel sheet. Make a separate sheet for what would be each database table. The first row is your field names. The first column is your unique primary key. Your spreadsheet should look something like this:

Save your spreadsheet as a tab delimited text file. You will still be able to open it up in Excel if you need to make any edits. You can change the delimiter if you need to, just change the explode parameters on lines 15 and 25 below to match.

First, we will write a generic database class that will read your spreadsheet and parse it into an array.

class Database {

    public function initializeDB($filename)
    {
        $fp = fopen($filename,'r');

        if (!$fp) {
            return false;
        }

        $titlesArray = array();
        $dbArray = array();

        $line = fgets($fp, 1024);
        $fields = explode ("\t", $line);

        foreach($fields as $item)
        {
            $titlesArray[] = trim($item);
        }

        while (!feof($fp)) {
            $line = fgets($fp, 16024);

            $fields = explode ("\t", $line);
            $dbArray[$fields[0]] = array();
            foreach($fields as $key=>$item)
            {
                $pat = '`^"(.*)"$`';
                $item = preg_replace($pat, '${1}', $item);

                $dbArray[$fields[0]][$titlesArray[$key]] = $item;
            }

            $fp++;
        }

        fclose($fp);

        return $dbArray;
    }

}

This reads your database file, and turns it into an array of key=>value pairs based on your column titles.
Now, let’s extend the class for our product class. This way we can reuse the Database class for each table. Here’s our Product class:

require_once('Database.class.php');

class Products extends Database {

    private $productsArray;

    public function __construct()
    {
        //products.txt is our Excel spreadsheet
        $this->productsArray = $this->initializeDB("data/products.txt");
    }

    //Get a single product by its primary key.
    public function getProductByID($id)
    {
        $product = $this->productsArray[$id];
        //do any additional processing here
        return $product;
    }

    //I have a field named "Collection" in my database. Let's grab all the products belonging to that collection.
    public function getProductsByCollection($collection)
    {
        $returnArray = array();
        foreach($this->productsArray as $key=>$val)
        {
            if($val['Collection'] == $collection) {

                $returnArray[] = $val;

            }
        }
        return $returnArray;
    }

}

With our Products class ready, all we need to do is initialize it in our main PHP script.

require_once('inc/Products.class.php');

$products = new Products();
$productId = $_GET['id'];
$product = $products->getProductByID($productId);

if(!$product) {
    //handle what happens if that product doesn't exist
}

//I include my HTML in a separate file to keep it clean.
include('templates/detail-tpl.php');

Finally, in the template file, you can access the items in your database. WordPress doesn’t like my php opening tag, so you’ll have to substitute that in yourself.


<p><[phptag] echo $product['product_name']; ?></p>
<p><[phptag] echo $product['product_description']; ?></p>
<p><[phptag] echo $product['product_price']; ?></p>

That’s all there is to it! Hope you found this helpful.

11 Comments + Add Comment

  • Great example , I’m just a beginner and this is helpfull to look at it.
    I want first a simple solution to grab some data out of a database (without mysql) to simplify some funvtions on my website. The data comes from a seperate database and the data is only available for me in excel spreadsheet. This data i want to use im my website.

    Thanks for this tutorial.

    Carl.

  • I have a real need for getting this to work but am having some difficulty and hope you might help. I am not running in WordPress but on a hosted server that supports PHP.

    All the php scripts you described are in the same MAIN directory of my website. I have given them the following names maindatabasescript.php, Database.class.php, products.class.php, and detail-tpl.php. The name of the spreadsheet is products.txt.

    I have inserted “echo” statements that tell me each of the PHP files have been accessed but nothing prints at the end.

    I deleted the reference to other directories e.g. inc and data. Is that OK or is that necessary?

    I deleted the and text as that seemed to be associated with WordPress. Is that right to do?

    I included in the maindatabasescript.php the html header and body code. Is that appropriate?

    Even though all the echo text I inserted prints out, the content of the products.txt file does not. Can you suggest what I might be doing wrong?

    • Not sure, are you passing in an id in the URL? maindatabasescript.php?id=1
      You can try adding “var_dump($product);” after line 5 on the main script to see if a product was found.
      There was no WordPress code in there, so you shouldn’t have had to delete anything. Maybe that’s the problem?

      • Thanks Liz for trying to help. I will try the var_dump suggestion.

        So you believe the span class=”skimlinks-unlinked” script is needed…I will reinsert all that and see what happens.

        Do you believe I did the right thing to put the html head and body code in the maindatabase script file

        • Oh, no you don’t need that script. It’s hard to say without seeing your code, but it’s probably okay to put the html code in the script file.

          You can also try doing a var_dump of $this->productsArray in the products class after $this->initializeDB(“data/products.txt”);. If you get a product array you’ll know it worked. If not, either the csv file isn’t named right or the CSV isn’t formatted right, or something else is going wrong.

  • Liz…I inserted the var_dump after line 5 and the result was string(0) “”. What does that mean to you?

    I’m still in the process of reinserting the deleted script.

  • I still haven’t reinserted the deleted items as I followed your suggestion of inserting the var dump of $this.
    Here was the results.

    array(4) { [1]=> string(9) “clothes ” [2]=> string(9) “clothes ” [3]=> string(9) “clothes ” [“”]=> string(0) “” } string(0) “”

    NOTE that I added a column named Collections as that was referenced in the products.class.php script.

    I believe I have created and saved a valid example of a tab delimited TXT file as specified. You referenced a CSV fill, so please indicate if I am wrong in having a TXT Excel file vs the CSV file.

    Lastly, would you be willing for me to send you the 4 files to look over or I can put them on a test website and I can send you the main php file.

  • That’s a very nice offer. I just sent the email

  • can i get this source code please.

  • Can I display the data of any of the sheets or a range of cells from the Excel Workbook on the Webpage?

Leave a comment

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>