Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2012
    Posts
    6

    Unanswered: simple database question

    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.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    where do you want the final data to reside? in excel or in mysql?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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 Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ronan, that's pretty slick

    wouldn't importing the csv be easier, though?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  6. #6
    Join Date
    Jan 2012
    Posts
    6
    Quote Originally Posted by it-iss.com View Post
    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.
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •