Import MySQL Database From phpMyAdmin To MS Access
phpMyAdmin is used for storing databases, with simple and dead easy interface, you can handle MySQL databases in a convenient way. But when it comes to exporting data to novice-user oriented applications, it doesn’t support direct data migration. If you are looking for way for data migration/transfer from phpMyAdmin to Microsoft Excel or Access, then this post may help.
Open phpMyAdmin, Select database you want to export, and click Export.
You will reach Export window, choose the format in which you want to export the database. We will choose CSV for Excel, enable zipped radio button from save as file options, and hit GO button at the bottom-right of the window to start exporting data.
You will be prompt for choosing output destination for the file. Download the file and unzip it.
Open the exported file in Excel 2010, to check whether data fields are still intact or not.
On File menu, click Save As to save the file in xlsx format.
Now launch Access 2010, head over to External Data and click Excel to import worksheet.
You will see Get External Data dialog box, it shows three different options of how and to where database will be stored. Go through the options (with details) and select one. We need to create a database in which table will be created by Access automatically.
Since the first row not contain the column headings, so we will leave this step, click Next.
In this step you can specify information about each of the fields you are importing. Select field/column in the area below and specify corresponding Field Options. Click Next to continue.
Here you can define a primary key(unique identifier) for the table, enable Choose my own primary key and select Field1 which seems to contain auto_increment data type.
Give the table an appropriate name and click Finish.
The database table from phpMyAdmin through Excel is successfully inserted into Access.
For adding more fields and applying constraints, switch to Design View.
Note: The above demonstration shows data migration of small database, there is however no guarantee of successful migration if you need to export huge databases.
How abt importing all the table once
How about changing the title to ‘Import MySQL tables to Excel and Access’. This is not useful if you actually want to import a database..sorry it really isn’t
I found it useful, but it would be really tedious to do it for large databases, a couple of table would be ok but more than 10 …
Thanks anyway.
Thank you, This helped me out a lot … Although i had to export each table individually so i could import a whole database.
Seems like you don't understand the difference between a table and a database.
this is the simple demonstration of importing MySQL table to Access, you can import the whole database(WordPress, Joomla,Drupal etc) to Access using the same procedure.
lol sure not!! if you do a whole database (who knows how many data this db would hold?) copy from mysql to msaccess you would for sure not use this technique and go with a DSN Connection to the mysql db with Windows. Then you could just import the mysql content directly into access via DSN… Do you really think that ANYone would take your steps with a db holding 50 tables?
a link to a tutorial on how to do this DSN connection would have been nice, but nooooo, you just had to rant and run, hmmm… ?
Please don’t assume people know the better way, if they did, they wouldn’t have opened this page to look for a solution…