import Ms access data into MySql

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • milas57
    New Member
    • May 2007
    • 14

    import Ms access data into MySql

    hello everyone how can i import Ms Access data into MySql thank you
  • mwasif
    Recognized Expert Contributor
    • Jul 2006
    • 802

    #2
    Checkout MySQL migration toolkit.

    Comment

    • ronverdonk
      Recognized Expert Specialist
      • Jul 2006
      • 4259

      #3
      I have the distinctive feeling that the MySQL migration kit for Access is not free of charge (but I could be wrong).

      Another solution would be to do it yourself.
      Write a server application (like PHP) that reads the tables and data from the Access database and stores it into the MySQL database.
      Using a (free) tool like Adodb makes it relatively easy to do.

      Holler when you need more info.

      Ronald

      Comment

      • CodeMaster123
        New Member
        • Feb 2008
        • 4

        #4
        Originally posted by milas57
        hello everyone how can i import Ms Access data into MySql thank you
        First export the tables from MS-Access into a text file and then use infile command to import that text file into MySQL.
        Here is an example of infile command:

        LOAD DATA INFILE ‘name_of_export ed_file.txt’
        INTO TABLE table_name
        FIELDS TERMINATED BY ‘,’
        LINES TERMINATED BY ‘\r\n’;

        Comment

        • milas57
          New Member
          • May 2007
          • 14

          #5
          thank for all yr help guy really appreciate

          Comment

          • ronverdonk
            Recognized Expert Specialist
            • Jul 2006
            • 4259

            #6
            Originally posted by milas57
            thank for all yr help guy really appreciate
            That why we are here (one of our goals in life).
            Do you want to tell us what solution you g=have chosen?

            Ronald

            Comment

            • docdiesel
              Recognized Expert Contributor
              • Aug 2007
              • 297

              #7
              Hi,

              how about copying the tables contents directly by INSERT INTO SELECT FROM..., without having to export/import them and so on?

              Of course first you've got to create the tables in mySQL with phpMyAdmin or another mySQL tool. Then all you need is a Window$-PC with Access and the mySQL ODBC driver. Open the Access database and create a new table - but make it a linked table, using the OBDC entry of your mySQL database as source. Select the appropriate mySQL table. Then copy the data from within Acce$$ by using SQL:

              Code:
              INSERT INTO
                mysql_connected_table1
              SELECT
                *
              FROM
                access_old_table ;
              Regards,

              Bernd

              Comment

              • ronverdonk
                Recognized Expert Specialist
                • Jul 2006
                • 4259

                #8
                Like MySQL, the MS Access db must have meta data about the tables you want to copy. So read the Access table's metadata, construct a MySQL CREATE TABLE command with that information and issue it.

                Then copy (INSERT as in the previous post) and you're done.

                Ronald

                Comment

                • pedrobl
                  New Member
                  • Mar 2008
                  • 1

                  #9
                  Originally posted by ronverdonk
                  I have the distinctive feeling that the MySQL migration kit for Access is not free of charge (but I could be wrong).
                  ...
                  Ronald
                  Actually, you are :P. Mysql Migration tools are released under the GPL and under commercial license by mysql for any OS.

                  More info:


                  Hope it helps,

                  Pedro.

                  Comment

                  • ronverdonk
                    Recognized Expert Specialist
                    • Jul 2006
                    • 4259

                    #10
                    Originally posted by pedrobl
                    Actually, you are :P. Mysql Migration tools are released under the GPL and under commercial license by mysql for any OS.

                    More info:


                    Hope it helps,

                    Pedro.
                    Pedro, you are absolutely right about this. So it was entirely my mistake. Soory foir that.
                    Ronald

                    Comment

                    • HelenParker
                      New Member
                      • May 2017
                      • 1

                      #11
                      You can use MySQL import wizard in dbForge Studio for MySQL. This MySQL GUI tool has MS Access format as well.

                      Comment

                      Working...