SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Enthusiast mackman's Avatar
    Join Date
    Jan 2002
    Location
    Metro Detroit, USA
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Spreadsheet into multiple tables

    Hey all -

    I'm stumped with how to efficiently go about breaking data that's currently in one spreasheet, into multiple tables in MySQL, while maintaining data integrity.

    I am familiar with database design, so...

    The spreadsheet has columns such as Name, Nickname, etc., but also columns like HighSchool, Colleges, and ProTeams. Those last three columns have multiple values for each row.

    So immediately I see a many-to-many relationship between a person and, say, ProTeams, which would represented in MySQL with a lookup table.

    I can break out the values easily for the Colleges, ProTeams, etc, for the support table, but how to add the values to the lookup table without having to type them in row after row after row... ??

    Anyone follow? Confused?

    Thanks in advance!

  2. #2
    Sultan of Ping jofa's Avatar
    Join Date
    Mar 2002
    Location
    SvÝ■jˇ­
    Posts
    4,080
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    When you say spreadsheet, you mean Excel, correct?

    Let's say you have this in Sheet1 in the workbook:
    Name, Nephews/Nieces
    "Donald", "Huey, Dewey, Louie"
    "Daisy", "April, May, June"

    Then create these columns in Sheet2:
    Niece/Nephew, Uncle/Aunt

    And run this macro...
    Code:
    Sub GetNephewsAndNiecesInfo()
        intSheet2Row = 2
        For intSheet1Row = 2 To 3 ' Change 3 to the number of rows in your sheet...
            strDuck = Sheet1.Cells(intSheet1Row, 1).Value
            strNephewsOrNieces = Sheet1.Cells(intSheet1Row, 2).Value
            arrNephewsOrNieces = Split(strNephewsOrNieces, ",")
            For i = LBound(arrNephewsOrNieces) To UBound(arrNephewsOrNieces)
                strNephewOrNiece = Trim(arrNephewsOrNieces(i))
                Sheet2.Cells(intSheet2Row, 1).Value = strNephewOrNiece
                Sheet2.Cells(intSheet2Row, 2).Value = strDuck
                intSheet2Row = intSheet2Row + 1
            Next
        Next
    End Sub
    Sheet2 should now look like this:
    Niece/Nephew, Uncle/Aunt
    Huey, Donald
    Dewey, Donald
    Louie, Donald
    April, Daisy
    May, Daisy
    June, Daisy

    Finally; load the values from sheet 2 into your db table

    Of course you can insert all columns from sheet 1 to the db, and break out the values there, but I think this way is better

    NB! In your lookup table case; first import values to the table used for the M:M relation, then insert distinct values from this table into the 3rd table with lookup values

  3. #3
    SitePoint Enthusiast mackman's Avatar
    Join Date
    Jan 2002
    Location
    Metro Detroit, USA
    Posts
    31
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did indeed mean Excel, and THANK YOU very much! Worked like a charm. Now to dump values into the DB.

    Thanks again - my knowledge of what Excel has to offer in the way of macros is few and far between.


Bookmarks

Posting Permissions

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