SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2008
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Excel to MySQL Possible?

    Hey guys,

    I have tons of Excel data to add to a MySQL database and I'm looking for a quick way around doing the job manually due to time constraint. Is there a way to upload Excel data into MySQL? Any programs, plugins?

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by onedee View Post
    Is there a way to upload Excel data into MySQL?
    yes, just export from excel to a csv file, and import to mysql using the LOAD DATA INFILE command
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Enthusiast
    Join Date
    Mar 2008
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    yes, just export from excel to a csv file, and import to mysql using the LOAD DATA INFILE command
    Hello r937, thanks for dropping by, I will have to give that a try. I hope importing a CSV file will also take care of the ID's, etc?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    take care of the ids?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Mar 2008
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    take care of the ids?
    For some reason the ID's in some of the tables are not in order and I'd assumed it's a randomly assigned number. But I see now I can actually add an ID to each row and that just puts it in order within the table.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    clarification: the rows in a table are in undefined order

    the only order you can impose is the order of presentation when you use the ORDER BY clause of a SELECT statement
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Enthusiast Angrypoonani's Avatar
    Join Date
    Mar 2011
    Posts
    35
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by onedee View Post
    For some reason the ID's in some of the tables are not in order and I'd assumed it's a randomly assigned number. But I see now I can actually add an ID to each row and that just puts it in order within the table.
    I may be misunderstanding the question... But you can organize all of your data beforehand if you're importing from an excel file. Organize all your data first. I usually save the data as a CSV then import it with something along the lines of:
    IMPORT DATA INFILE 'data.csv' INTO TABLE my_table​ FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n' IGNORE LINES 1 ( colA, colB, ... , colX );

    Of course... your syntax may be different depending on your file type and what OS it is saved in. In other words, it's not a copy paste situation

  8. #8
    SitePoint Enthusiast
    Join Date
    Mar 2008
    Posts
    58
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys, here is what I will be doing.

    1. Arrange everything in Excel, give columns and rows the same title as the columns & rows in the database
    2. From PhpMyAdmin - navigate into the respective DB table
    3. Click import and from the import screen set these options:

    a. Format: CSV
    b. Format-Specific Options: NONE
    c. Select (Do not use AUTO_INCREMENT for zero values)


Tags for this Thread

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
  •