Page 1 of 1
Converting M$ Access to MySQL
Posted: Sun Nov 05, 2006 10:40 am
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.
Posted: Sun Nov 05, 2006 11:19 am
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.
Posted: Sun Nov 05, 2006 12:43 pm
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.
Posted: Sun Nov 05, 2006 12:47 pm
by RobertGonzalez
Are there any values in the CSV file that have comma's in them (like addresses, etc)?
Posted: Sun Nov 05, 2006 12:50 pm
by impulse()
Yes, there are a few empty entries.
Some of the CSV file looks like:
,,,,,,,,,,,,,,,,,,,,,,
Are these causing the error?
Posted: Sun Nov 05, 2006 1:14 pm
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.
Posted: Sun Nov 05, 2006 1:28 pm
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.
Posted: Mon Nov 06, 2006 8:09 am
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.
Posted: Mon Nov 06, 2006 8:43 am
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);