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.

DOK · Accepted Answer · 2012-04-21 11:55:03Z

6

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.

answered Apr 21, 2012 at 11:55

DOK

32.8k8 gold badges64 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LITguy Over a year ago

You absolutely set me in the right direction and solved it! The only thing I had to do differently is figure out where to locate the feature called "multiple consolidation range" in Excel 2010. The demo is for an older verion. I went to Insert>Pivot Table and it didn't have that option. The solution for Excel 2010 is to Press Alt+D, P in order to find that hard to locate pivot table wizard. I'm sure it is easier for people that use excel often. BTW, for those less experienced, you have to press Alt+D... THEN press P separately. Don't hold all 3 down at the same time! THANK YOU DOK!!!

DOK Over a year ago

I'm happy to help you, as others have helped me. And you have definitely helped others by including in your comment the details of how to do this in the current version of Excel.

Collectives™ on Stack Overflow

Import Excel data matrix into MySQL database

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related