Import Excel data matrix into MySQL database

Question

After searching many forums, I think my problem is how to type the question properly because I can't seem to find an answer remotely close to what I need, yet I think this is excel > mysql 101 by the looks of it..

I have an excel sheet with dozens of types of blinds (for windows). There is a row which is the width.. and a left column that is the height. As you cross reference a width and height (say 24 x 36) it has a price value.

       |  24  |  30  |  32  |  36   (width)
   ----------------------------
    24 | $50     $55    etc
    30 | $60     etc    etc(price)
    32 | $70
(height)

I can't for the life of me figure out where or how I am to import this into mysql when my database looks like this..

itemname_id <<(my primary)  |  width  |  height  |  price
-------------------------------------------------------------------

Am I doomed to manually typed thousands of combinations or is this common? How do I type the correct terms to find a solution? I'm not speaking the right lingo evidently.

Thank you so much for any guidance. I've looked forever and I keep hitting a wall.

Answer 1

It probably would have helped to know that the layout of your Excel data is commonly referred to as a pivot table. It is possible to "unpivot" the data in Excel to get the data in the format that you want to import to your database.

This brief article shows how to create a pivot table and then unpivot it. Basically, that entails creating a "sum of values" pivot table and then double-clicking on the single value that is the result. It's counter-intuitive, but pretty simple to do.