Import CSV, Access to MySQL

Anon

Hello Guys

Anybody knows how to convert ASCII CSV files (comma separated values) to mysql or Access to MySQL?

I want to import a big database to mysql. I have this database in CSV and access.

OOzy

Anon

Download myadmin from www.phpwizard.net and you can do it via your browser in a couple of sec's.

regards,
scott d~

Anon

Thank you Sccott

This is the second time you save my bu**

OOzy

Anon

That's pretty cool. I haven't played with PHPmyadmin.

Anyway, there is the good ol' command line function that is something like READ DATA INFILE. There is syntax for it in the mysql manual.

Anon

Hello Scott

It is me again. I am having hard time importing my CSV file using phpMyAdmin. I clicked on "Insert textfiles into table" Then I selected the file and the field termination, etc. I get no error but still there is not data transfer

OOzy

Anon

Well I'm no expert.....but the only thing that I can think of is that if your data has an apostophe somewhere....like "he's" then it won't return an error.....it will just end the command early....and not do the csv dump.

Have a look and see if I'm right!

regards,
scott d~

Anon

Because I think, there are more like me, who could never figure out, how to use the mysql manual efficiently:
LOAD DATA LOCAL INFILE "file.csv" INTO TABLE tablename

There are some parameters you can use to set the field-enclosing-signs and other stuff
Source: MySQL, Paul DuBois, New Riders 1999

Anon

Another way to do it is to use MyODBC. Establish a connection to the MySql database with MyODBC in the ODBC part of windows. In Access you can choose a table and then tell it to export to your MySql data source.

It works pretty well. You may have to change some data types later in MySql.

Anon

Using MyODBC seems to be the most simple way to import huge csv files into MySQL.
I tried it with a 70 MB Text-File. No Problem.

Many thanks Erick ! This helped me a lot !

Anon

Hi,

I tried to convert my Access database to a MySQL using the MyODBC trick. In some cases, it seems te work, but 90% of the time I get this error when I choose to export a table in MS Access :

ODBC--call failed.

[Microsoft][ODBC Drivers Manager] Information type out of range (#0)

I have a MySQL ODBC driver active, mysql server is running...
Evertyhing seems ok with the content of my Access database. Sometimes it works fine and I can see the correct data in mysql but most of the time I get the same error, so I'm stuck. Could anyone help me out ?

Thx

Anon

Mainly this post is to let people know there are a lot of lurkers out here get answers months later doing genreal webs searches on the archive.
Thanks!

Also I just upload my CSV without delimiter and ' caused no problem. I don't what would happen if I used " delimiters.

Anon

just tried the load date infile described above on a win2k system running mysql 3.23.34 and it worked with tab seperate values but not comma seperated, just in case anyone is struggling...

Anon

Does anyone know if it is possible to automate this? Ideally I need it to run everynight, I guess by setting up a cron job on Linux.

Thanks
Rob

kiran_ravi

Anon wrote:
Hello Guys

Anybody knows how to convert ASCII CSV files (comma separated values) to mysql or Access to MySQL?

I want to import a big database to mysql. I have this database in CSV and access.

OOzy

reply me as quick as possible