SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Member kathrinee68's Avatar
    Join Date
    Jul 2009
    Location
    Seattle
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to convert Excel to MySql?

    Is anybody know how to convert Excel to MySql?
    I got a large data of excel which I want to insert into MySql table.
    but I could not figure it out how to do that?
    any suggestions
    thanks

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Save it as a CSV file, then use a LOAD DATA INFILE query to import it.

    http://dev.mysql.com/doc/refman/5.0/en/load-data.html

  3. #3
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    816
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Question

    I've been puzzled by how to do this correctly.

    I have 216 records in a CSV. I've created my fields in MySQL to match to fields created in the Excel file.

    After I attempt to import it with CSV using LOAD DATA, it imports 216 records (as it should) but all fields are 'NULL'.

    Attached are two samples. One is the Excel File, and the other is the result after importing into MySQL. I'm not familiar with this method of importing, and would appreciate any assistance.

    Thank you!
    Attached Images Attached Images

  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)
    please do a SHOW CREATE TABLE for the table

    then show the LOAD DATA command that you used
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    816
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Thank you for your prompt response!

    You'll have to forgive me, but I just simply went to phpmyadmin, accessed the table I wanted to Import to (which was all ready created), then I went to 'Import', selected the CSV file, selected 'CSV using LOAD DATA', kept the defaults of:
    (unchecked) Replace table data with file
    (unchecked) Ignore duplicate rows
    Fields terminated by ;
    Fields enclosed by "
    Fields escaped by \
    Lines terminated by auto
    (checked) use LOCAL keyword

    Attached is an image of my table.
    Attached Images Attached Images

  6. #6
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Is the semicolon really the delimiter in your file?

  7. #7
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    816
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    No, the comma.

  8. #8
    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)
    then you should change this --
    Code:
    fields terminated by ;
    to this --
    Code:
    fields terminated by ','
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    Join Date
    Jan 2010
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ms office excel 2007 offers you the connectivity to all database . You have ODBC connections where you can connect to external source and fetch data into excel. All you need is the credential of database.

  10. #10
    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)
    aleserlee, it seems you have overlooked the fact that the data here is being extracted from excel, not fetched into excel
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Guru
    Join Date
    Jul 2003
    Location
    england
    Posts
    816
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Smile

    Quote Originally Posted by r937 View Post
    then you should change this --
    Code:
    fields terminated by ;
    to this --
    Code:
    fields terminated by ','
    That worked absolutely SPLENDID! Thank you! Now I'll know how to do this in the future!

    You guys at Sitepoint are awesome!


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
  •