Hello Everyone -

I am new to databases and MySQL and I'd like to see if there is a way SQL can help me set up a database in an easy to work with format.

I have data in excel in the following format.First column is the date, the next 10 columns contain sales data by region, so it looks like this:

Date Region 1 ... Region 10
1/1/12 12 ... 19

What I would like is for there to be a Date Column, a Region Column, and then a Sales column:

Date Region Sales
1/1/12 1 12
1/1/12 2 32

I can do this manually of course in Excel, but its an awful lot of copy paste. I was wondering if there was an elegant way to do this via SQL, I could write a VBA macro, but I was hoping that could be avoided.

where do you want the final data to reside? in excel or in mysql?

Have you tried creating a formula which will generate the INSERT statement for you? This is what I usually do. For example:

="INSERT INTO table(field1, field2, field3) VALUES ('" & a2 & "','" & b2 & "', '" & c2 & "');"

You then copy this field down each row and this generates all your INSERT statements. You simply copy and paste each of these into a text file and you are ready to run this to apply the data into your database.

ronan, that's pretty slick

wouldn't importing the csv be easier, though?

Indeed you could export to CSV formatted file and then LOAD DATA into to a staging table, followed by INSERT statements to move the data correctly into the respective table.

Everyone - thanks for the prompt replies. In terms of where I would like my data to live, really I need to have it in (in the proper format) in Excel and SQL since some of my tools will pull from SQL and some need Excel.

Ronan - this is a perfect workaround. Top notch! I just tweaked it a bit to fit the data structure I have:

="INSERT INTO table(field1, field2, field3) VALUES ('" & $C90 & "','" & D$88 & "', '" & D90 & "');"

Since my data has the date in the first column, and the actual data (by region) across multiple columns; this allows me to make a "Region" field and fill it with the data in D88, E88, etc...

Since I am new to SQL I wasn't sure if there was something I could do with setting the data up in seperate tables and then doing joins etc...