Converting M$ Access to MySQL

Ye' old general discussion board. Basically, for everything that isn't covered elsewhere. Come here to shoot the breeze, shoot your mouth off, or whatever suits your fancy.
This forum is not for asking programming related questions.

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Converting M$ Access to MySQL

Post by impulse() »

Are there are free programs out there to do this? I've found a couple but all the ones that connect direct to the MySQL server are unable to connect and the ones that generate a .sql file are shareware and only insert the first 5 entries.

Any ideas appreicated, Stephen.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

How much data are you trying to convert? You could always export your Access tables to CSV files and import them into MySQL with phpMyAdmin.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

I've tried that already but I keep getting
Invalid field count in CSV input on line 1.
Although I've counted the fields in the CSV file and those in the DB and they match exactly.

User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Are there any values in the CSV file that have comma's in them (like addresses, etc)?
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

Yes, there are a few empty entries.

Some of the CSV file looks like:
,,,,,,,,,,,,,,,,,,,,,,
Are these causing the error?
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

He means like this:

Code: Select all

name,address,zip

justin,404 error st, berwick, me, 03901
steve,500 request ln, new york, ny, 94943
Notice the commas IN the values themselves. So it reads it as;
name = justin
address = 404 error st
zip = berwick

and then the rest throws the error.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post by impulse() »

There's nothing like that, that I can see. I've managed to create a connection between this PC and my server now so I can use a program that does ALL the work for me. I'm going to have to pay for it though :( Not really my prefered path but it looks like the only way that isn't time consuming or error prone.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

That just seems so unnecessary. I did what you are looking to do about two years ago, and the only hitch I had was that I had a field that had commas in it that was throwing off the field count to the parser. I think I ended up wrapping all fields in double quotes to make it work. But I never considered using a tool that costs money.

You're sure all the fields are the same, all the field types are set up properly and all primary keys are unique? Another thing you might want to try is the free version of SQLYog (at http://www.webyog.com). I think it has an import utility that might be able to skip the manual entire export/import routine.
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Did you know you can do it directly in the query like this

Code: Select all

LOAD DATA LOCAL INFILE '/importfile.csv'
INTO TABLE `test_table`
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
(field1, filed2, field3);
Post Reply