MS Access to MySQL Conversion help (Gigantic table)

Question

So I have this gigantic table, containing approx 7 million records, in MS Access (*.mdb), I want to transfer it into a much more workable MySQL format, and store it on my webserver. The file itself weighs 2GB.

The problem is, since the table is so large, it won't let me export it normally (Access says the limit is 65,536 records.)

I've tried some 3rd party software but to no avail.

Can anyone recommend a clean way of doing so, without damaging the data inside?

Thanks in advance for any help.

Answer 1

Install MySQL on your own system and upsize to it rather than trying to use your local server. Then run an append query from your MySQL to the server instance.

Answer 2

Install an ODBC driver for MySQL, if you don't have one already. The latest version is available here: Download Connector/ODBC

Create a DSN (Data Source Name) for your MySQL server from the Windows ODBC Data Source Administrator.

Then from Access 2003, select your table in the Database Window, and choose File->Export from Access' main menu. In the "Export Table 'yourtablename' To ..." dialog, select "ODBC Databases()" from the "Save as type" drop-down list (at the bottom of the dialog). The next dialog allows you to specify the name MySQL will use for the exported table, and it defaults to the Access table name. After you click OK, you will get another dialog, "Select Data Source", where you can select your DSN for MySQL. After you click OK on that dialog, you will probably get one more asking you for user name and password. Supply them, and click OK.

Hopefully your table will then transfer without errors. However, I've never done that operation with MySQL. It has worked for me with ODBC transfers to SQL Server and PostGreSQL. So I don't see why it wouldn't work with MySQL, too.

Also I've never attempted to export 7 million records in one go. If it chokes, we'll have to figure out a work-around.

If you're using Access 2007 instead of 2003, look for a similar option starting with the Export section of the ribbon.

I suggested this approach because my impression is this export will be a one-time deal, so I think the Access UI export method would be easiest. However, you can do essentially the same operation with VBA code using the DoCmd.TransferDatabase Method with your ODBC DSN.

Yet another alternative would be to create a compatible table structure in MySQL, create a link in Access to the MySQL destination table (using your DSN again), then run an "append query" from Access:

INSERT INTO link_to_mysql_table (field1, field2, field3, etc)
SELECT field1, field2, field3, etc
FROM access_table;

The append query approach could be useful in case the export chokes on 7 million records. You could add a WHERE clause to limit the SELECT query's output record set to a manageable chunk size, and then repeat with a different WHERE to specify another chunk.

Answer 3

I would use pyodbc as described in

http://en.wikibooks.org/wiki/Python_Programming/Database_Programming

download python 2.7 from http://python.org/ download http://code.google.com/p/pyodbc/

modify the following coede to set myfile.mdb and MyTable according to your table and file

save the code in a file translate.py

import csv
mycsv = csv.writer(open('result.csv', 'wb'), delimiter=',',
                    quotechar='"', quoting=csv.QUOTE_MINIMAL)
import pyodbc

DBfile = 'myfile.mdb'
conn = pyodbc.connect('DRIVER={Microsoft Access Driver (*.mdb)};DBQ='+DBfile)
cursor = conn.cursor()

SQL = 'SELECT * FROM MyTable;'
for row in cursor.execute(SQL): # cursors are iterable
    mycsv.writerow(row)

cursor.close()
conn.close()

run python translate.py

Answer 4

Is that 7 million value after a compact + repair? I mean, if each record is about 120 chars in length, you can fit 32 million records in 2 gigs.

Also, I not aware of a limit of exporting 65,000 records, but only in regards to Excel.

So, you can/should be able to export the data to a csv, and then use a bulk text import in mySql to pull that data in. So, try exporting the table as csv. That should work.

I mean, you could link a table via odbc if you have a good local connection to the sql server, but if not, then I would export to csv (it is VERY fast). I would then zip the file (they zip fantastic). Upload file to server, and un-zip, and then use bulk text import. So, such a zipped file is VERY small and will save huge amounts of transfer time.

You can also consider using tab delimited as mySql also can import those, but a simple text file should work just fine.