SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Converting an xls spreadsheet to MySQL

    I was just curious if there is any fast method of converting an xls spreadsheet to either SQL command or preferably right into a MySQL table. Any suggestions on help on this issue would be greatly appreciated as it will save me many hours of manual conversion.

  2. #2
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Save the XLS as CSV, and then use MySQL's load data in file to get it into MySQL.
    E.g.
    load data infile 'filename.csv' into table table_name fields terminated by ',' (field_one, field_two);
    http://www.mysql.com/doc/en/LOAD_DATA.html
    Oh no! the coots are eating my nodes!

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Would that work for multiple sheets, or would I have to individually save each sheet as a seperate CSV file. Also I assume it would be wise to remove the titles from the columns in the excel spreadsheet or else it to would be placed as a row in MySQL

  4. #4
    SitePoint Enthusiast
    Join Date
    Apr 2003
    Location
    Ontario, Canada
    Posts
    45
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for the help with regards to converting the file to .csv, I am using PHPMyAdmin, so I could load the data via that, which saves me doing it via a sql query, which I am not overly good at yet (I am still new to all of this).

  5. #5
    Node mutilating coot timnz's Avatar
    Join Date
    Feb 2001
    Location
    New Zealand
    Posts
    516
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There's probably a cunning way to get the data easily from individual sheets, which I'm obviously not familiar with.

    And yes, you'll have to manually remove the column titles from the resulting CSV files, unless of course there is another cunning way to do that.

    Lastly, be a tad careful importing (and exporting for that matter) with PHPMyAdmin, it does/used to have a tendancy to.... stop... mid import/export.
    Oh no! the coots are eating my nodes!


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
  •