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?
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.
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.
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?
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
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
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!