Jimmy Harrell’s Blog

Okay, the past two days have been so hectic for me I decided to Blog about it. So just how do you Migrate a 40,000 record Table from Microsoft Access into MYSQL database? Well just to tell you up front my experience is one of having to do it using Godaddy’s Shared Hosting Environment. But I don’t think that really matters that much so I”ll just keep on with my blog here. My environment:

Windows XP OS
Microsoft Access2007
Godaddy.com for the Shared Hosting running on a Linux Platform and being forced to use the ir phpMyAdmin version 2.9.1.1

Okay so here was my dilemma. I had a songlist.mdb that was 12MB in size and contained 40,000 records in one table. I needed this data loaded into MySQL and I wasn’t going to do it manually 1 record at a time.

Solution:

First in Microsoft Access you can export your table out as a .xls file which you can then save as a .csv file. Access is nice as it gives you many other file formats to export your data as like xml and a few others… But for this purpose we needed it to be .csv as required by the ol phpMyadmin (which by the way will only let you upload a .csv as long as it’s under 2MB(which mine wasn’t) but we’ll save that discussion for later on). So in Access:

1. Select your Table
2. Right Click it and choose Export
3.Choose Excel
4. Browse to the directory where you want to save your file
5. Save your filename to whatever you want

Now open up your .xls file that you just saved and then save it out as a .csv. Simply choose Save As option and in the “Save as Type” dialogue box slect CSV (comma delimited).

If your hosting provider doesn’t set limits on the file size of your csv then simply make sure that you have your table created in MySQL just as it is in Microsoft Access. (If you don’t know how to do that just email me at jimmyharrell@jimmyharrell.com and I’ll call you up and walk you through it. Pretty easy.

Now if you’re limited to file size as I am then you’ll need to break up your csv file into chunks. So for me that meant 10,000 records at a time to keep the file size under 2MB. To do this is pretty easy

1. Open up your csv file and simply go down to the 10,000th record. Any row after that, you’ll delete. Just make sure you have a copy of the entire csv as a backup somewhere b/c you’ll have to do this four more times to get the other 30,000 reocrds. Once you’ve done this then go to the phpmyAdmin and select your table, then select Import (I’ve attatched a screenshot) .

Now I don’t know about you but for me, in the first field of my KaraokeTracks Table, I had a field named AritstName.

This field had , <-commas in the name like this: McGraw, Tim. So in the phpadmin screenshot you’ll see something that says “Fields terminated by” and to the right you’ll see a ; <- semicolon.

At first when I would load my .csv file I kept getting an “Invalid Field Count” error message which frustrated me to death b/c my csv file has 7 fields ans so did my Table in my MySQL table. So when I changed the value from a ; to a , (semicolon to a comma) and then uploaded the csv file it was like magic. Abracadabra I know had my first 10,000 records loaded into My Table all in a matter of about 10 seconds.