Best way to dump excel file to database

I have a excel file sent daily with updated product list and prices… Im looking for the best way to dump it into database each day to update… So it can be called out and displayed so its grouped by

CPUs :: Intel

CPUs and listed by Intel then AMD etc etc?

Hope that makes sence…

Thanks

Well, if you have the option, it would be best to have the Excel file saved as a CSV. Then you can simply read the CSV into PHP and explode each line on commas.

Without knowing specifically how your Excel files are structured, it’s hard to say. Nevertheless, it seems like that’s something that could be restructured with PHP with nested arrays. So, as you read in the lines of your CSV, instead of just exploding each line and saving the resulting array, you would process the line into an array structure of your choosing. For example, if you had:

type,company,product#,price,name
CPUs,AMD,processor#1,$10,Super Awesome
CPUs,AMD,processor#2,$20,Lame
CPUs,Intel,processor#3,$30,Half-assed
CPUs,Intel,processor#4,$40,Best processor ever

You would take each exploded line and put the relevant information in the relevant arrays:

<?php
$master_array = null; //The overall array
/*
* What we're going to do is build an array that will look something like this:
* array[type][company][product#][each product attribute]
*/

while(/*however you're reading input*/)
{
    $exploded_line = explode(',', $line);
    if( isset($master_array[$line[0]][$line[1]][$line[3]]) )
    {
        //This product number already exists in the array. Add it again somehow, or ignore it, record an error, whatever you want.
    }else
    {
        $master_array[$line[0]][$line[1]][$line[3]] = array('price'=>$line[4], 'name'=>$line[5]);
    }
}

//Later when you want to read out the data

foreach($master_array as $type=>$companies)
{
    //To group by product type, add output here, for example:
    echo "<div class='product_type'><h2>$type</h2>";

    foreach($companies as $company=>$products)
    {
        //To group by company, add output here, for example:
        echo "<div class='company'><h2>$company</h2>";

        foreach($products as $product_id=>$product_info)
        {
            //Here you have access to all relevant variables:
            $type; //CPU
            $company; //AMD/Intel
            $product_id; //ID of the product
            $product_info['price']; //Price of the product
            $product_info['name']; //Name of the product
        }

        //Remember to close up your divs
        echo "</div>"; //Ends the company div
    }

    echo "</div>"; //Ends the product_type div
}
?>

If, within a cell, you actually have something like, “CPUs :: AMD”, you can explode that:

explode('::',$line);

This will split that into a heading/subheading pair.

If this still doesn’t answer your question, could you give me an example of how your spreadsheet is setup?

Yea I can do that…

But its not just columns, it has headings and sub headings. I want to beable to create a pop-out menu from the attatchment layout (spreadsheet)

CPUs :: AMD
CPUs :: Intel

To be like:

CPUs
------->AMD
------->Intel

Hard Drives
-------> Seagate
-------> Western Digital

So not sure how to dump the excel file without having to spend heaps of time restructuring it… as I want to dump it each day top update my database for the online store…

Thanks