#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0

    Very Complicated Situation


    Here is an Excel table which i want to convert to mysql database for the new website to access it.

    Unfortunately as a new user i am not allowed to insert images or links so please copy and paste the link in to your web browser to see the screenshot.

    i47.tinypic.com/sfdr1k.png

    You can see the "Table 1". it is showing 2011 data only but i have data for 3 years.

    And "Table 2" shows data for the month of january only while i have data for each months for the 3 years.
    For the "Table 1" i created a table with the columns same as Excel. But my problem is with the "Table 2". I can't figure out how to convert it to mysql table, i mean what columns and fields should i create?

    I hope you understand what i mean
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Oct 2009
    Location
    Nebraska, USA
    Posts
    876
    Rep Power
    275
    THIS looks like a decent tutorial.

    Otherwise, give THIS a try.
    [2nd link is not meant to be sarcastic or condescending, just looked like there were alot of results to help you with your problem]
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    None of them works for me. i think i have to elaborate a little bit and reframe my question.

    If you see the Table 2 it is very different then typical sql tables. it not only has a top row of headings but also a vertical column of headings too, that is what makes it complicated.

    I mean 2 horizontal and vertical headers like this.

    jan feb mar apr may jun jul aug sep oct nov dec
    bularia
    estonia
    germany
    britain
    sweden
    norway
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by milaz
    If you see the Table 2 it is very different then typical sql tables.
    so is table 1 -- both your tables have exact same problem design

    to convert them to mysql "as is" is actually not that hard... but to convert them into a proper relational design, that will take some extra work

    i might be able to do it for you, if you would answer some questions...

    how often are you gonna do this? once a year? where's the data coming from? are there other excel sheets that we can't see that will have to be accommodated? what are you going to do with the data? display it exactly like this? or do calculations and searches on it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2013
    Posts
    3
    Rep Power
    0
    Originally Posted by r937
    so is table 1 -- both your tables have exact same problem design

    to convert them to mysql "as is" is actually not that hard... but to convert them into a proper relational design, that will take some extra work

    i might be able to do it for you, if you would answer some questions...

    how often are you gonna do this? once a year? where's the data coming from? are there other excel sheets that we can't see that will have to be accommodated? what are you going to do with the data? display it exactly like this? or do calculations and searches on it?
    how often are you gonna do this?
    3-4 times a year.

    where's the data coming from?
    privacy.

    are there other excel sheets that we can't see that will have to be accommodated?
    No

    what are you going to do with the data? display it exactly like this? or do calculations and searches on it?
    do calculations and searches on it.

    Please if you can solve this problem i will be very thanxful to you.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,205
    Rep Power
    4279
    Originally Posted by milaz
    how often are you gonna do this?
    3-4 times a year.
    then i'm sorry, i cannot do it for you

    my best advice is, generate a csv from excel, and then use the mysql LOAD DATA INFILE command to import it into a table that exactly matches the excel columns

    it won't be very elegant, but at least the data will be loaded
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo