SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Location
    Seattle, WA
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing/Exporting Data Between MySQL & M/S Excel

    I'm developing a rather large MySQL database table containing product information for an online catalog of products. I'm using PHP, phpMyAdmin and MySQL, but would like to build my products table on my local system and then export the data to my MySQL database on my hosted server. I started out building my table online using phpMyAdmin, but have found that too cumbersome.

    Could someone give me some thoughts on how to export my Excel data to my MySQL database via phpMyAdmin? And likewise how to export data from my MySQL database to Excel?

    SeattleDan

  2. #2
    Sports Publisher mjames's Avatar
    Join Date
    Jan 2000
    Location
    Charlotte, NC
    Posts
    5,891
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you can afford it, this tool might do it for you: http://www.convert-in.com/xls2sql.htm

  3. #3
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Location
    Seattle, WA
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Marc,

    Thanks for the tip. That may be exactly what I was looking for. I plan to download the trial version to see if it meets my needs. This could save me a huge amount of time.

    SeattleDan

  4. #4
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    While that tool looks good, I'm not sure you really need it. You can use ODBC drivers with Excel to get data into/out of ODBC databases (so you'd need MyODBC as well). I find this works well.

    Access is also very good for this, as you can make linked tables which update to/from your ODBC datasource in real time.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  5. #5
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Location
    Seattle, WA
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've tried using the software mentioned above, but I'm having difficulty connecting to my hosted MySQL server.

    hillsy, you suggested using ODBC drivers for Excel & MySQL. Could you explain how to do this in a little more detail? (I'm a real novice on this subject.) Where do I find the ODBC drivers and exactly what do I need to do to connect to my hosted MySQL server and import/export my data?

  6. #6
    Yugo full of anvils bronze trophy hillsy's Avatar
    Join Date
    May 2001
    Location
    :noitacoL
    Posts
    1,859
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    First you will need the MyODBC MySQL ODBC driver, which is a free download from www.mysql.com

    You need to install this on your client PC. This will allow you to make ODBC connections to your MySQL database server - hopefully. You can then connect to it with any ODBC-enabled application (basically any MS Office app)

    Personally I find Access more useful than Excel for this kind of thing, but if you want to use Excel, all you have to do is go to Data --> Get External Data --> New Database Query and follow your nose

    Let us know how you get on.
    that's me!
    Now A Pom. And a Plone Nut
    Broccoli Martinez Airpark

  7. #7
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Location
    Seattle, WA
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, hillsy. I'll give it a try. Your comments are appreciated.

    SeattleDan

  8. #8
    SitePoint Enthusiast
    Join Date
    Apr 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Did you try LOAD DATA IN FILE ?

    Take a look at the mySQL manual for that stuff, there's a somewhat hidden entry for LOAD DATA IN FILE.

    All you really need is exporting your XLS file to a comma-delimited (or "tab-delimited") file, e.g. "products.csv".

    Then upload that file, either through an HTML-form in an admin area, or trough phpmyAdmin.

    Here's a basic example:

    $sql = " DELETE FROM $table " ;
    @$result = mysql_query($sql) or die (' Couldn\'t delete existing table contents.');


    $sql2 = " LOAD DATA LOCAL INFILE '$myFile' INTO TABLE $table " ;
    @$result2 = mysql_query($sql2) or die (' Could not insert anything. Sorry.');


    This throws out the old data completely, and inserts data from your textfile.

    You might want to do a backup table first.

    Also make sure of course, that your tables really match (structure, columns) etc.

    It's so superfast and easy, I was yodeling when I discovered that nifty feature ;-)

  9. #9
    SitePoint Member
    Join Date
    Jul 2001
    Location
    Melbourne, Australia
    Posts
    20
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,
    You mentioned that you're using PHPMyAdmin to set up the mysql. This has an "Insert textfiles into tables" link mid page when you select a table from the left nav window.

    Excel exports files in CSV (comma seperated values) which work fine with this feature. You may want to run a trial on a sample table.
    PHPMyAdmin is a great utility for beginers (like me )cause it spits out the mysql at the top of each results page
    Scotty

  10. #10
    SitePoint Enthusiast
    Join Date
    Apr 2001
    Location
    Seattle, WA
    Posts
    96
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    scottg,

    Yes, I've discovered that PHPMyAdmin will do what I want in the way of importing Excel CSV files. It's very simple (for a begineer like me, too) and quick.

    Thanks to all of you for your comments. I'm now exporting from MySQL to Excel and importing to MySQL from Excel with ease. PHPMyAdmin is a great tool!

    SeattleDan


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
  •