Importing/Exporting Data Between MySQL & M/S Excel

Databases
1

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

If you can afford it, this tool might do it for you: http://www.convert-in.com/xls2sql.htm

3

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

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.

5

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

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 :slight_smile:

Let us know how you get on.

7

Thanks, hillsy. I’ll give it a try. Your comments are appreciated.

SeattleDan

8

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 :wink:

9

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

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