1. Home
  2. MS Office
  3. Import mysql database from phpmyadmin to ms access

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.

clip_image001

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.

clip_image002

You will be prompt for choosing output destination for the file. Download the file and unzip it.

clip_image003

Open the exported file in Excel 2010, to check whether data fields are still intact or not.

clip_image004

On File menu, click Save As to save the file in xlsx format.

clip_image005

Now launch Access 2010, head over to External Data and click Excel to import worksheet.

clip_image006

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.

clip_image007

Since the first row not contain the column headings, so we will leave this step, click Next.

clip_image008

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.

clip_image009

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.

clip_image010

Give the table an appropriate name and click Finish.

wizard 5

The database table from phpMyAdmin through Excel is successfully inserted into Access.

clip_image012

For adding more fields and applying constraints, switch to Design View.

clip_image013

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.

9 Comments

  1. 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

  2. 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.

  3. Thank you, This helped me out a lot … Although i had to export each table individually so i could import a whole 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…

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.